agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: Achilleas Mantzios <achill@matrix.gatewaynet.com>
To: pgsql-sql@postgresql.org
Subject: Re: Inconsistent/wrong behavior of pg_trigger_depth when used with DEFERRED CONSTRAINTS
Date: Thu, 1 Jun 2017 11:38:47 +0300
Message-ID: <ef27dd39-432e-fd7f-c14f-05a8d9b49b7e@matrix.gatewaynet.com> (raw)
In-Reply-To: <12406.1496242511@sss.pgh.pa.us>
References: <3d1a5143-389c-256a-cda9-44ca002fc606@matrix.gatewaynet.com>
	<12406.1496242511@sss.pgh.pa.us>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>

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

view thread (3+ messages)

Message-ID: <ef27dd39-432e-fd7f-c14f-05a8d9b49b7e@matrix.gatewaynet.com>
Permalink:  ../ef27dd39-432e-fd7f-c14f-05a8d9b49b7e@matrix.gatewaynet.com/
Also on:    postgresql.org/message-id/ef27dd39-432e-fd7f-c14f-05a8d9b49b7e@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: <ef27dd39-432e-fd7f-c14f-05a8d9b49b7e@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