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?
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
