Thread: [BUGS] BUG #14679: Inconsistent/wrong behavior of pg_trigger_depth when usedwith DEFERRED CONSTRAINTS
[BUGS] BUG #14679: Inconsistent/wrong behavior of pg_trigger_depth when usedwith DEFERRED CONSTRAINTS
From
achill@matrix.gatewaynet.com
Date:
The following bug has been logged on the website: Bug reference: 14679 Logged by: Achilleas Mantzios Email address: achill@matrix.gatewaynet.com PostgreSQL version: 9.5.6 Operating system: Ubuntu 16.04.1 LTS , 4.4.0-38-generic Description: 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 triggerLANGUAGE 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 ROWEXECUTE 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 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #14679: Inconsistent/wrong behavior of pg_trigger_depth when used with DEFERRED CONSTRAINTS
From
Tom Lane
Date:
achill@matrix.gatewaynet.com writes: > I just run into a behavior that I consider wrong. Test case : Isn't this the same thing you already filed in <3d1a5143-389c-256a-cda9-44ca002fc606@matrix.gatewaynet.com>? Duplicate bug reports just confuse matters. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #14679: Inconsistent/wrong behavior ofpg_trigger_depth when used with DEFERRED CONSTRAINTS
From
Achilleas Mantzios
Date:
On 30/05/2017 17:03, Tom Lane wrote: > achill@matrix.gatewaynet.com writes: >> I just run into a behavior that I consider wrong. Test case : > Isn't this the same thing you already filed in > <3d1a5143-389c-256a-cda9-44ca002fc606@matrix.gatewaynet.com>? Yes, I just thought that it went unnoticed , so I tried the official method. > > Duplicate bug reports just confuse matters. Won't happen again. Thanks for taking care of this. > > regards, tom lane -- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs