Thread: [SQL] Inconsistent/wrong behavior of pg_trigger_depth when used withDEFERRED CONSTRAINTS

Hello,

I just run into a behavior that I consider wrong. Test case :

create table test(id serial primary key, name text);

CREATE OR REPLACE FUNCTION public.force_integrity()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$DECLARE

BEGIN
        RAISE NOTICE 'TABLE = %.% , pg_trigger_depth()=%',TG_TABLE_SCHEMA, TG_TABLE_NAME, pg_trigger_depth();
        IF (pg_trigger_depth() = 1) THEN
                UPDATE test SET id=id WHERE id=NEW.id;
        END IF;
        RETURN NEW;
END;
$function$

CREATE CONSTRAINT TRIGGER test_force_integrity_tg
  AFTER INSERT OR UPDATE
  ON test
  DEFERRABLE INITIALLY DEFERRED
  FOR EACH ROW
  EXECUTE PROCEDURE force_integrity();

-- test by forcing immediate constraints and thus expected results

begin;

BEGIN

set CONSTRAINTS ALL IMMEDIATE;

insert into test(name) values ('foo');
NOTICE:  TABLE = public.test , pg_trigger_depth()=1
NOTICE:  TABLE = public.test , pg_trigger_depth()=2
CONTEXT:  SQL statement "UPDATE test SET id=id WHERE id=NEW.id"
PL/pgSQL function force_integrity() line 9 at SQL statement
INSERT 0 1

commit;

COMMIT

-- test with defaults - unexpected results

begin ;
BEGIN
insert into test(name) values ('foo');
INSERT 0 1
commit ;

NOTICE:  TABLE = public.test , pg_trigger_depth()=1
NOTICE:  TABLE = public.test , pg_trigger_depth()=1
NOTICE:  TABLE = public.test , pg_trigger_depth()=1
NOTICE:  TABLE = public.test , pg_trigger_depth()=1
NOTICE:  TABLE = public.test , pg_trigger_depth()=1
NOTICE:  TABLE = public.test , pg_trigger_depth()=1
NOTICE:  TABLE = public.test , pg_trigger_depth()=1
NOTICE:  TABLE = public.test , pg_trigger_depth()=1
NOTICE:  TABLE = public.test , pg_trigger_depth()=1
NOTICE:  TABLE = public.test , pg_trigger_depth()=1
NOTICE:  TABLE = public.test , pg_trigger_depth()=1
NOTICE:  TABLE = public.test , pg_trigger_depth()=1
NOTICE:  TABLE = public.test , pg_trigger_depth()=1

-- Endless loop, pg_trigger_depth() never gets increased

This was reproduced on 9.3.17 and on 9.5.6

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

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

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