From: | Achilleas Mantzios <achill(at)matrix(dot)gatewaynet(dot)com> |
---|---|
To: | pgsql-sql <pgsql-sql(at)postgresql(dot)org> |
Subject: | Inconsistent/wrong behavior of pg_trigger_depth when used with DEFERRED CONSTRAINTS |
Date: | 2017-05-29 11:22:37 |
Message-ID: | 3d1a5143-389c-256a-cda9-44ca002fc606@matrix.gatewaynet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | tel medola | 2017-05-29 14:10:16 | Lost my tablespace |
Previous Message | Vladimir Nicolici | 2017-05-25 18:23:33 | Re: Re: SQL error: function round(double precision, integer) does not exist |