Subject: Re: Inconsistent/wrong behavior of pg_trigger_depth when used
 with DEFERRED CONSTRAINTS
To: pgsql-sql@postgresql.org
References: <3d1a5143-389c-256a-cda9-44ca002fc606@matrix.gatewaynet.com>
 <12406.1496242511@sss.pgh.pa.us>
From: Achilleas Mantzios <achill@matrix.gatewaynet.com>
Message-ID: <ef27dd39-432e-fd7f-c14f-05a8d9b49b7e@matrix.gatewaynet.com>
Date: Thu, 1 Jun 2017 11:38:47 +0300
User-Agent: Mozilla/5.0 (X11; FreeBSD amd64; rv:52.0) Gecko/20100101
 Thunderbird/52.0
MIME-Version: 1.0
In-Reply-To: <12406.1496242511@sss.pgh.pa.us>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
Content-Language: en-US
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

On 31/05/2017 17:55, Tom Lane wrote:
> Achilleas Mantzios <achill@matrix.gatewaynet.com> writes:
>> I just run into a behavior that I consider wrong. Test case :
> Hmm ... after looking at this, I'm not sure why you're surprised.
> In CONSTRAINTS ALL IMMEDIATE mode, when the first invocation of
> the trigger function does an UPDATE, the ensuing trigger firing
> occurs at the end of the UPDATE statement.  So it occurs while
> the outer trigger is still active, pg_trigger_depth() returns 2,
> and all is well.  However, when the trigger firing is deferred,
> that means it's deferred till end of transaction.  So the trigger's
> UPDATE merely queues a trigger firing request to be done later.
> When the request is serviced, we're not inside the original trigger
> anymore, so pg_trigger_depth() returns 1, and the trigger queues
> another request.  Lather rinse repeat.
>
> In other words, pg_trigger_depth() tells you about the dynamic
> state of the control stack; it's not a proxy for detecting whether
> the action that caused the trigger firing was itself done by a
> trigger.  At least not when you're working with deferrable triggers.
>
> You might have better luck by testing to see if the update you are
> thinking of doing would be a no-op.
Redefining the trigger as :
CREATE CONSTRAINT TRIGGER test_force_integrity_tg AFTER INSERT OR UPDATE ON test DEFERRABLE INITIALLY DEFERRED FOR EACH ROW WHEN (pg_trigger_depth() < 1) EXECUTE PROCEDURE force_integrity();

test=# begin ;
BEGIN
test=# insert into test(name) values ('foo');
INSERT 0 1
test=#
test=# commit ;
NOTICE:  TABLE = public.test , pg_trigger_depth()=1
COMMIT
test=#

seems to do the trick. The update's trigger is not even queued in this case.

>
> 			regards, tom lane
>
>

-- 
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql