Re: [SQL] Inconsistent/wrong behavior of pg_trigger_depth when used with DEFERRED CONSTRAINTS

Achilleas Mantzios <achill@matrix.gatewaynet.com> writes:
> I just run into a behavior that I consider wrong. Test case :

Hmm ... after looking at this, I'm not sure why you're surprised.
In CONSTRAINTS ALL IMMEDIATE mode, when the first invocation of
the trigger function does an UPDATE, the ensuing trigger firing
occurs at the end of the UPDATE statement.  So it occurs while
the outer trigger is still active, pg_trigger_depth() returns 2,
and all is well.  However, when the trigger firing is deferred,
that means it's deferred till end of transaction.  So the trigger's
UPDATE merely queues a trigger firing request to be done later.
When the request is serviced, we're not inside the original trigger
anymore, so pg_trigger_depth() returns 1, and the trigger queues
another request.  Lather rinse repeat.

In other words, pg_trigger_depth() tells you about the dynamic
state of the control stack; it's not a proxy for detecting whether
the action that caused the trigger firing was itself done by a
trigger.  At least not when you're working with deferrable triggers.

You might have better luck by testing to see if the update you are
thinking of doing would be a no-op.
        regards, tom lane