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?
