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

On 31/05/2017 17:55, Tom Lane wrote:
> Achilleas Mantzios <achill(at)matrix(dot)gatewaynet(dot)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 ROW WHEN (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