I'll let you in on a secret: NOTIFY is actually a before-commit
operation. This is good enough because it never, or hardly ever,
fails. I would argue that anything you want to do in an AFTER COMMIT
trigger could just as well be done in a BEFORE COMMIT trigger; if
that's
not reliable enough then you need to fix your trigger.
So, looping back to pgmemcache. It doesn't seem like it's about the
trigger failing; it's about
the transaction failing. How could I 'fix my trigger' so that if
the transaction fails
the cache wouldn't be updated?
An AFTER COMMIT trigger would have to be in a separate transaction.
Agreed. Each trigger being in its own transaction.
What happens if there's more than one, and one of them fails?
Each AFTER COMMIT trigger would be in its own transaction. So if it
fails, let it fail. It isn't the original transactions fault. If
you want it to bundled
with the original transaction then do a BEFORE COMMIT.
If there is more than one, so be it. One may fail, another may not.
If that is not okay or the intended behavior, then create a new
trigger than
combines them into one transaction so that they are coupled.
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.
Why would it have to do this? Not sure if I follow. In my mind,
the only way this would happen is if the AFTER COMMIT trigger acted
upon the very same table it is triggered off of. Then you could
have a circular reference. However, this is true for the BEFORE COMMIT
situation too. I don't see the difference. What am I missing?
Cheers,
Christian
