agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: Tom Lane <tgl@sss.pgh.pa.us>
To: Achilleas Mantzios <achill@matrix.gatewaynet.com>
Cc: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: Inconsistent/wrong behavior of pg_trigger_depth when used with DEFERRED CONSTRAINTS
Date: Wed, 31 May 2017 10:55:11 -0400
Message-ID: <12406.1496242511@sss.pgh.pa.us> (raw)
In-Reply-To: <3d1a5143-389c-256a-cda9-44ca002fc606@matrix.gatewaynet.com>
References: <3d1a5143-389c-256a-cda9-44ca002fc606@matrix.gatewaynet.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>

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.

			regards, tom lane


-- 
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)  latest in thread

Message-ID: <12406.1496242511@sss.pgh.pa.us>
Permalink:  ../12406.1496242511@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/12406.1496242511@sss.pgh.pa.us

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: tgl@sss.pgh.pa.us, achill@matrix.gatewaynet.com
  Subject: Re: Inconsistent/wrong behavior of pg_trigger_depth when used with DEFERRED CONSTRAINTS
  In-Reply-To: <12406.1496242511@sss.pgh.pa.us>

* 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