Inconsistent/wrong behavior of pg_trigger_depth when used with DEFERRED CONSTRAINTS

agora inbox for pgsql-sql@postgresql.org  
help / color / mirror / Atom feed

Inconsistent/wrong behavior of pg_trigger_depth when used with DEFERRED CONSTRAINTS
3+ messages / 2 participants
[nested] [flat]

* Inconsistent/wrong behavior of pg_trigger_depth when used with DEFERRED CONSTRAINTS
@ 2017-05-29 11:22  Achilleas Mantzios <achill@matrix.gatewaynet.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Achilleas Mantzios @ 2017-05-29 11:22 UTC (permalink / raw)
  To: pgsql-sql

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

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Inconsistent/wrong behavior of pg_trigger_depth when used with DEFERRED CONSTRAINTS
@ 2017-05-31 14:55  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Achilleas Mantzios <achill@matrix.gatewaynet.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Tom Lane @ 2017-05-31 14:55 UTC (permalink / raw)
  To: Achilleas Mantzios <achill@matrix.gatewaynet.com>; +Cc: pgsql-sql

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

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Inconsistent/wrong behavior of pg_trigger_depth when used with DEFERRED CONSTRAINTS
@ 2017-06-01 08:38  Achilleas Mantzios <achill@matrix.gatewaynet.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 3+ messages in thread

From: Achilleas Mantzios @ 2017-06-01 08:38 UTC (permalink / raw)
  To: pgsql-sql

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 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



-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

end of thread, other threads:[~2017-06-01 08:38 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-05-29 11:22 Inconsistent/wrong behavior of pg_trigger_depth when used with DEFERRED CONSTRAINTS Achilleas Mantzios <achill@matrix.gatewaynet.com>
2017-05-31 14:55 ` Tom Lane <tgl@sss.pgh.pa.us>
2017-06-01 08:38   ` Achilleas Mantzios <achill@matrix.gatewaynet.com>

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox