Thread: [BUGS] BUG #14679: Inconsistent/wrong behavior of pg_trigger_depth when usedwith DEFERRED CONSTRAINTS

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

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

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