The use case is that we have another database grafted onto our postgres database via an FDW. When precommit is called, we commit in the other database, which causes triggers in that database to go off, which causes changes back in the postgres database. To avoid deadlocks, it all has to be done through the same connection. We use deferred triggers for efficiency.
No matter what, we will have to find a work around and I've already got several in mind.
At a minimum it should be documented that deferred triggers and commit callbacks do not get along. However, it doesn't seem like a big change to do the equivalent of SET CONSTRAINTS ALL IMMEDIATE; once you are done processing deferred triggers.
Ross
PG Bug reporting form <noreply@postgresql.org> writes:
> If you register a function with RegisterXactCallback that causes a trigger
> to be deferred, that trigger is never executed.
I'd say that's in the category of "conceptually ridiculous".
The PRE_COMMIT events are supposed to fire after all user-level
actions in the transaction are finished. Running a trigger that
could do arbitrary things would certainly break the assumptions of
whatever processing people might be doing in PRE_COMMIT callbacks.
Perhaps there's a use case for an even earlier callback that runs in or
before the fire-deferred-triggers loop. But you haven't made any argument
for it, so I'm loath to add more complexity and cycles to transaction
commit for this. The whole area is quite tricky --- for instance, the
interaction with cursor closing is not something that would leap to mind.
So it's not obvious that such a callback could do anything useful and
bulletproof.
regards, tom lane
--
Ross Biro | CTO
_______________________________________
CONFIDENTIALITY NOTICE: This email message, including any attachments, is for the sole use of the intended recipient/s and may contain confidential & privileged information. Any unauthorized review, use, disclosure, or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original messages and any attachments.