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

Bruce Momjian <bruce(at)momjian(dot)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.

The basic problem is that the transaction commit sequence is very
carefully designed to do things in a specific order, and there is a
well-defined atomic point where the transaction is really "committed",
and we cannot go injecting random user-written code into that area
and still expect to have a working system.

These objections could be addressed to some extent by running the
triggers in a separate transaction that's automatically executed after
the "user" transaction commits or aborts. But that hardly seems like
a usable basis for replication, since you're just plain out of luck
if the secondary transaction fails.

Another response I've heard is "but I don't want to make
inside-the-database changes, I want to propagate the state to someplace
external". Of course that's completely broken too, because there is
*absolutely no way* you will ever make such changes atomic with the
inside-the-database transaction commit. We discourage people from
making triggers cause outside-the-database side effects already ---
it's not going to be better to do it in an "on commit" trigger.

regards, tom lane