> An AFTER COMMIT trigger would have to be in a separate transaction.
I guess AFTER COMMIT triggers would be like a NOTIFY, but more powerful.
While NOTIFY can't transmit information to another process, this trigger
could, and the other process could then view the results of the commited
transaction.
Also, implementing a long process (something involving network
roundtrips, for instance) in a BEFORE COMMIT trigger would delay the
transaction and any locks it holds with no benefit.
> What happens if there's more than one, and one of them fails?
Each one in its own transaction ?
> Even more to the point, if it's a separate transaction, don't you have
> to fire all these triggers again when you commit that transaction?
> The idea seems circular.
I guess AFTER COMMIT triggers are most useful when coupled to a trigger
on a modification to a table. So, the "before / after commit" could be an
attribute of an AFTER INSERT/UPDATE/DELETE trigger. If the AFTER COMMIT
trigger doesn't do any modifications to the target table, there will be no
infinite loop.
The before/after commit could also be implemented not via triggers, but
via deferred actions, by telling postgres to execute a specific query just
before/after the transaction commits. This could be used to implement the
triggers, but would also be more generic : a trigger on INSERT could then
defer a call to memcache update once the transaction is commited. It gets
lisp-ish, but it would be really cool?
