Pavan Deolasee wrote
> On Thu, Mar 20, 2008 at 3:24 AM, Stephen Denne
> <[EMAIL PROTECTED]> wrote:
>
> >
> > Pavan also refers to deferred triggers, which has got me
> thinking about another possible solution:
> >
> > Instead of inserting a delta row, that will be updated a
> lot of times, create an on commit drop temp table named after
> the txid and the grouping value (which is an integer in my
> case, perhaps hash it if you're grouping by something that
> doesn't easily convert to part of a table name),
> > create an after insert initially deferred constraint to
> call a function which will move the 'at commit' values of the
> rows in the temp table to the summary table.
> > The temp table will only have one row inserted, updated
> many times, then on commit the trigger is fired once, and the
> temp table is dropped.
> >
> > Does anyone think this will or won't work for some reason?
>
>
> I think this should work, although you may need to take some
> extra steps
> to manage the summary table. Also, I think a single temp
> table per transaction
> should suffice. The temp table would have one row per "group
> by" or "where"
> condition on which you want to track the count. The
> corresponding row will
> be updated as and when the corresponding count changes. You would need
> INSERT/DELETE/UPDATE triggers to do that. If there are any
> subtransaction
> aborts, that will be taken care by MVCC.
Thanks for that. I had gone ahead and tried out the idea, and it was working
'ok'.
Using one table per transaction has the benefit of less temp tables (but the
same number of triggers waiting to run). It also removes the grouping key from
the table name.
I was using a single table per grouping key, with a single updated row in it.
The benefit was simpler queries, and I could create an ON INSERT trigger that
would be triggered only once when the temp table was created, and a 'zero' row
was inserted, thereby separating the setup of the trigger from the maintenance
of the delta.
I haven't explored the transactional implications of updating vs inserting
delta rows in the summary table at the time of transaction commit. The code
below updates the summary table, which I think could lead to a large delay or
deadlocks if there are other styles of updates on that table (other than
on-commit triggers)?
I also hadn't considered sub-transactions.
Below is a cut-down version of what I tried out.
I was summarizing more than just the number of documents shown below, I was
storing a sum, and two maximums of timestamps (using the 'greatest' function
for aggregating each record). These were extra fields in both the summary table
and the temp tables.
This is able to be made more generic by changing get_temp_table_name() to take
an additional couple of parameters specifying the name of the function to run
at commit, and an identifying key (eg 'doc_count' vs 'doc_size_sum'), or
perhaps including the delta value too, (which looks like it would simplify the
triggers on the tables whose changes we wish to summarize, except that it
doesn't cater for storing greatest or least aggregates.)
I took a wild guess at a way of finding out whether the temp table already
exists:
not exists(select tablename from pg_catalog.pg_tables where
tablename=temp_delta_txid_group)
Is there a better/safer way?
Here's part of the code I've got at the moment (edited here to cut it down to
the core example, so it may contain errors):
CREATE TABLE doc_type_summary (
document_type_id integer NOT NULL,
documents bigint NOT NULL DEFAULT 0,
CONSTRAINT pk_doc_type_summary PRIMARY KEY (document_type_id)
);
CREATE OR REPLACE FUNCTION process_delta() RETURNS TRIGGER AS
$$
BEGIN
EXECUTE 'UPDATE doc_type_summary set
documents=doc_type_summary.documents+d.documents from ' || TG_ARGV[0] || ' as d
where document_type_id = ' || TG_ARGV[1];
RETURN NULL;
END;
$$
LANGUAGE 'plpgsql';
CREATE OR REPLACE FUNCTION get_temp_table_name(document_type_id integer)
RETURNS text AS
$$
DECLARE
temp_delta_txid_group text;
BEGIN
temp_delta_txid_group := 'temp_delta_' || txid_current() || '_' ||
document_type_id;
IF not exists(select tablename from pg_catalog.pg_tables where
tablename=temp_delta_txid_group) THEN
EXECUTE 'CREATE TEMP TABLE ' || temp_delta_txid_group || '(documents
bigint NOT NULL DEFAULT 0) ON COMMIT DROP';
EXECUTE 'CREATE CONSTRAINT TRIGGER ' || temp_delta_txid_group || '_trig
AFTER INSERT ON ' || temp_delta_txid_group || ' DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW EXECUTE PROCEDURE process_delta ("' || temp_delta_txid_group ||
'", ' || document_type_id || ')';
EXECUTE 'INSERT INTO ' || temp_delta_txid_group || ' DEFAULT VALUES';
END IF;
RETURN temp_delta_txid_group;
END;
$$
LANGUAGE 'plpgsql';
CREATE OR REPLACE FUNCTION document_count_trig()
RETURNS TRIGGER AS
$$
DECLARE
temp_delta_txid_group text;
BEGIN
IF TG_OP = 'INSERT' THEN
temp_delta_txid_group := get_temp_table_name(NEW.document_type_id);
EXECUTE 'UPDATE ' || temp_delta_txid_group || ' set
documents=documents+1';
RETURN NEW;
ELSIF TG_OP = 'DELETE' THEN
temp_delta_txid_group := get_temp_table_name(OLD.document_type_id);
EXECUTE 'UPDATE ' || temp_delta_txid_group || ' set
documents=documents-1';
RETURN OLD;
END IF;
END;
$$
LANGUAGE 'plpgsql';
--
Regards,
Stephen Denne.
Disclaimer:
At the Datamail Group we value team commitment, respect, achievement, customer
focus, and courage. This email with any attachments is confidential and may be
subject to legal privilege. If it is not intended for you please advise by
reply immediately, destroy it and do not copy, disclose or use it in any way.
__________________________________________________________________
This email has been scanned by the DMZGlobal Business Quality
Electronic Messaging Suite.
Please see http://www.dmzglobal.com/dmzmessaging.htm for details.
__________________________________________________________________
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
