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.  So, here are a few points to chew on:
> 
> * Trigger on rollback: what's that supposed to do?  The current
> transaction is already aborted, so the trigger has no hope of making any
> database changes that will ever be visible to anyone.
> 
> * Trigger on commit: what do you do if the transaction fails after
> calling the trigger?  The reductio ad absurdum for this is to consider
> having two on-commit triggers, where obviously the second one could
> fail.

I full agree that having triggers on rollback and on commit of 2PC 
transactions is broken by design. Triggers on COMMIT (for non-2PC 
transactions) and PREPARE (for 2PC-Transactions) seem workable though -
I'd expect such a trigger to be executed *before* any actual commit 
handling takes place. Essentially, doing
BEGIN
<some work>
COMMIT
in the presence of an on-commit trigger would be equivalent to doing
BEGIN
<some work>
SELECT my_trigger_function
COMMIT.

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.

regards, Florian Pflug