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. As you said, a single deferred trigger would then merge the temp table with the summary table. Here we need to be extra careful because serializable transactions may fail to update the same row in the summary table. One solution I can think of is (and I haven't checked the archives, so somebody might have already suggested this before): Each summary table will have one summary row per "group by" or "where" condition (same as temp table). In addition to that, it can have zero or more temporary rows for the conditions. - select summary_rows from summary table for update nowait; - if lock is not available, insert our deltas into the summary table as a new row. These rows will be deleted as soon as some other transaction gets lock on the summary rows and merge our deltas with them - if lock is available - merge our deltas with the summary rows - check for other temporary deltas and merge them with the summary rows and delete those temporary rows I guess we can write the generic triggers as contrib module. What needs to done is to let user specify the tables and the conditions on which they want to track count(*) and then apply those conditions in the generic triggers. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers