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

On 31/05/2017 17:55, Tom Lane wrote:
> 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.
Redefining the trigger as :
CREATE CONSTRAINT TRIGGER test_force_integrity_tg AFTER INSERT OR UPDATE ON test DEFERRABLE INITIALLY DEFERRED FOR EACH
ROWWHEN (pg_trigger_depth() < 1) EXECUTE PROCEDURE force_integrity();
 

test=# begin ;
BEGIN
test=# insert into test(name) values ('foo');
INSERT 0 1
test=#
test=# commit ;
NOTICE:  TABLE = public.test , pg_trigger_depth()=1
COMMIT
test=#

seems to do the trick. The update's trigger is not even queued in this case.

>
>             regards, tom lane
>
>

-- 
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt