Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1dFIl6-0002aW-TX for pgsql-sql@arkaria.postgresql.org; Mon, 29 May 2017 11:22:49 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1dFIl6-0007ze-9m for pgsql-sql@arkaria.postgresql.org; Mon, 29 May 2017 11:22:48 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1dFIl4-0007xR-CS for pgsql-sql@postgresql.org; Mon, 29 May 2017 11:22:46 +0000 Received: from host3.dynacom.ondsl.gr ([62.103.35.211] helo=smadev.internal.net) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1dFIl0-0005eu-O2 for pgsql-sql@postgresql.org; Mon, 29 May 2017 11:22:45 +0000 Received: from smadev.internal.net (smadev [10.9.200.131]) by smadev.internal.net (8.15.2/8.15.2) with ESMTP id v4TBMbpf014271; Mon, 29 May 2017 14:22:37 +0300 (EEST) (envelope-from achill@matrix.gatewaynet.com) To: pgsql-sql From: Achilleas Mantzios Subject: Inconsistent/wrong behavior of pg_trigger_depth when used with DEFERRED CONSTRAINTS Message-ID: <3d1a5143-389c-256a-cda9-44ca002fc606@matrix.gatewaynet.com> Date: Mon, 29 May 2017 14:22:37 +0300 User-Agent: Mozilla/5.0 (X11; FreeBSD amd64; rv:52.0) Gecko/20100101 Thunderbird/52.0 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="------------F81E81CC11739AEEAD142A0A" Content-Language: en-US X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org This is a multi-part message in MIME format. --------------F81E81CC11739AEEAD142A0A Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 7bit 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 --------------F81E81CC11739AEEAD142A0A Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit

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