Re: BUG #16705: Triggers deferred during commit callback are not executed

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

On Fri, Nov 6, 2020 at 9:49 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> PG Bug reporting form <noreply(at)postgresql(dot)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
_______________________________________

O: 240-380-2231| F: 240-556-0361 <(240)%20556-0361>
The Interface Financial Group <https://interfacefinancial.com/>

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.