Re: triggers on prepare, commit, rollback... ?

> Tom Lane wrote:
> > Bruce Momjian <bruce@momjian.us> writes:
> >>>> trigger on "prepare", "commit", "rollback", "savepoint",
> >>> This is a sufficiently frequently asked question that I 
> wish someone 
> >>> would add an entry to the FAQ about it, or add it to the 
> TODO list's 
> >>> "Features we don't want" section.
> > 
> >> OK, remind me why we don't want it again?
> > 
> > I'm sure I've ranted on this several times before, but a 
> quick archive
> > search doesn't find anything.  

I know of this very short "rant":
http://archives.postgresql.org/pgsql-hackers/2008-04/msg01200.php

Florian G. Pflug wrote:
> A possible use-case for that is aggregating some statistics collected 
> during a transaction. One could e.g. maintain a cache of 
> table rowcounts
> by summing up the number of inserted and deleted records per 
> table with 
> some per-row ON INSERT and ON DELETE (presumably C-language) triggers,
> and than update a global cache at transaction end.

This is possible now using deferred constraint triggers (PL/pgSQL is sufficient), though better described IMHO as
"beforeprepare" or "before commit" rather than "on ...".
 

Any FAQ addition should mention deferred constraint triggers.

I would expect problems with "after commit" and "after rollback" triggers.

I think that the documentation of when the existing deferred constraint triggers run in 2PC/non 2PC could be clarified,
andthe effects on the transaction state that are possible within such trigger functions documented.
 

http://www.postgresql.org/docs/current/interactive/sql-createconstraint.html

Says

"They can be fired either at the end of the statement causing the triggering event, or at the end of the containing
transaction;"

It refers to

http://www.postgresql.org/docs/current/interactive/sql-createtable.html

Which says

"Checking of constraints that are deferrable can be postponed until the end of the transaction"

and

"If the constraint is INITIALLY DEFERRED, it is checked only at the end of the transaction."

I'd also contest this statement:

"Only foreign key constraints currently accept this clause. All other constraint types are not deferrable."

Regards,
Stephen Denne.
At the Datamail Group we value teamwork, respect, achievement, client 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 replying immediately, destroy it and do not
copy, disclose or use it in any way.

The Datamail Group, through our GoGreen programme, is committed to environmental sustainability.
Help us in our efforts by not printing this email.
__________________________________________________________________ This email has been scanned by the DMZGlobal
BusinessQuality             Electronic Messaging Suite. 
Please see http://www.dmzglobal.com/dmzmessaging.htm for details.
__________________________________________________________________