agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: Achilleas Mantzios <achill@matrix.gatewaynet.com>
To: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Inconsistent/wrong behavior of pg_trigger_depth when used with DEFERRED CONSTRAINTS
Date: Mon, 29 May 2017 14:22:37 +0300
Message-ID: <3d1a5143-389c-256a-cda9-44ca002fc606@matrix.gatewaynet.com> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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

view thread (3+ messages)  latest in thread

Message-ID: <3d1a5143-389c-256a-cda9-44ca002fc606@matrix.gatewaynet.com>
Permalink:  ../3d1a5143-389c-256a-cda9-44ca002fc606@matrix.gatewaynet.com/
Also on:    postgresql.org/message-id/3d1a5143-389c-256a-cda9-44ca002fc606@matrix.gatewaynet.com

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: achill@matrix.gatewaynet.com
  Subject: Re: Inconsistent/wrong behavior of pg_trigger_depth when used with DEFERRED CONSTRAINTS
  In-Reply-To: <3d1a5143-389c-256a-cda9-44ca002fc606@matrix.gatewaynet.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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