BUG #14679: Inconsistent/wrong behavior of pg_trigger_depth when used with DEFERRED CONSTRAINTS

From: achill(at)matrix(dot)gatewaynet(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #14679: Inconsistent/wrong behavior of pg_trigger_depth when used with DEFERRED CONSTRAINTS
Date: 2017-05-30 06:55:58
Message-ID: 20170530065558.4284.28679@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 14679
Logged by: Achilleas Mantzios
Email address: achill(at)matrix(dot)gatewaynet(dot)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 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

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Flo 2017-05-30 12:24:12 Re: Can't restore view with pg_restore
Previous Message David G. Johnston 2017-05-30 01:26:44 Re: Danger