Date: Wed, 24 Jan 2018 17:07:01 -0500
From: Bruce Momjian <bruce@momjian.us>
To: ian@thepathcentral.com
Cc: pgsql-docs@postgresql.org, Andrew Gierth <andrew@tao11.riddles.org.uk>
Subject: Re: Trigger behaviour not as stated
Message-ID: <20180124220701.GP17109@momjian.us>
References: <20171129193934.27108.30796@wrigleys.postgresql.org>
 <20180124181008.GK17109@momjian.us>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="qcHopEYAB45HaUaB"
Content-Disposition: inline
In-Reply-To: <20180124181008.GK17109@momjian.us>
User-Agent: Mutt/1.5.23 (2014-03-12)
Precedence: bulk


--qcHopEYAB45HaUaB
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

On Wed, Jan 24, 2018 at 01:10:08PM -0500, Bruce Momjian wrote:
> On Wed, Nov 29, 2017 at 07:39:34PM +0000, ian@thepathcentral.com wrote:
> > The following documentation comment has been logged on the website:
> > 
> > Page: https://www.postgresql.org/docs/10/static/sql-createtrigger.html
> > Description:
> > 
> > URL: https://www.postgresql.org/docs/current/static/sql-createtrigger.html
> > 
> > Statement: &quot;In contrast, row-level triggers are fired for all affected
> > partitions or child tables.&quot;
> > 
> > Row-level triggers are not fired on child tables where the trigger ON BEFORE
> > UPDATE | DELETE is on the parent table. Only works on BEFORE INSERT.
> 
> Uh, can you email us an example of the failure so we can research it? 
> Thanks.

OK, I have some more details on this.  First there is the Stackoverflow
report:

  https://stackoverflow.com/questions/47557665/postgresql-on-before-delete-trigger-not-firing-on-a-parent-table-in-an-inheritan

The report confirms that row-level triggers are fired _only_ on affected
tables (meaning the table that had a row change), not on any table
mentioned _or_ affected.  The current wording, added in this commit:

	commit 501ed02cf6f4f60c3357775eb07578aebc912d3a
	Author: Andrew Gierth <rhodiumtoad@postgresql.org>
	Date:   Wed Jun 28 18:55:03 2017 +0100
	
	    Fix transition tables for partition/inheritance.
	
	    We disallow row-level triggers with transition tables on child tables.
	    Transition tables for triggers on the parent table contain only those
	    columns present in the parent.  (We can't mix tuple formats in a
	    single transition table.)
	
	    Patch by Thomas Munro
	
	    Discussion: https://postgr.es/m/CA%2BTgmoZzTBBAsEUh4MazAN7ga%3D8SsMC-Knp-6cetts9yNZUCcg%40mail.gmail.com

should be improved.  The attached patch updates the docs to say
statement-level triggers fire on the "referenced" table, while row-level
triggers fire only on the "affected" table, (vs. all affected tables)
even if they are not referenced in the query.  I would backpatch this to
PG 10.

The second attachment is an SQL query script that illustrates the
behavior.

-- 
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

--qcHopEYAB45HaUaB
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment; filename="trigger.diff"

diff --git a/doc/src/sgml/ref/create_trigger.sgml b/doc/src/sgml/ref/create_trigger.sgml
new file mode 100644
index a8c0b57..9cd369d
*** a/doc/src/sgml/ref/create_trigger.sgml
--- b/doc/src/sgml/ref/create_trigger.sgml
*************** UPDATE OF <replaceable>column_name1</rep
*** 501,509 ****
  
    <para>
     Modifying a partitioned table or a table with inheritance children fires
!    statement-level triggers directly attached to that table, but not
     statement-level triggers for its partitions or child tables.  In contrast,
!    row-level triggers are fired for all affected partitions or child tables.
     If a statement-level trigger has been defined with transition relations
     named by a <literal>REFERENCING</literal> clause, then before and after
     images of rows are visible from all affected partitions or child tables.
--- 501,510 ----
  
    <para>
     Modifying a partitioned table or a table with inheritance children fires
!    statement-level triggers directly attached to the referenced table, but not
     statement-level triggers for its partitions or child tables.  In contrast,
!    row-level triggers are fired only on affected partitions or child tables,
!    even if they are not referenced in the query.
     If a statement-level trigger has been defined with transition relations
     named by a <literal>REFERENCING</literal> clause, then before and after
     images of rows are visible from all affected partitions or child tables.

--qcHopEYAB45HaUaB
Content-Type: application/x-sql
Content-Disposition: attachment; filename="trigger.sql"
Content-Transfer-Encoding: quoted-printable

CREATE TABLE parent(id integer, val text);=0ACREATE TABLE child() INHERITS =
(parent);=0A=0ACREATE OR REPLACE FUNCTION inh_trigger() RETURNS trigger=0A =
  LANGUAGE plpgsql AS=0A$$BEGIN=0A   RAISE NOTICE 'Called by parent %', TG_=
OP;=0A   RETURN CASE WHEN TG_OP =3D 'DELETE' THEN OLD ELSE NEW END;=0AEND;$=
$;=0A=0ACREATE TRIGGER inh_trigger BEFORE INSERT OR UPDATE OR DELETE ON par=
ent=0A   FOR EACH ROW EXECUTE PROCEDURE inh_trigger();=0A=0ACREATE OR REPLA=
CE FUNCTION inh_trigger2() RETURNS trigger=0A   LANGUAGE plpgsql AS=0A$$BEG=
IN=0A   RAISE NOTICE 'Called by child %', TG_OP;=0A   RETURN CASE WHEN TG_O=
P =3D 'DELETE' THEN OLD ELSE NEW END;=0AEND;$$;=0A=0ACREATE TRIGGER inh_tri=
gger2 BEFORE INSERT OR UPDATE OR DELETE ON child=0A   FOR EACH ROW EXECUTE =
PROCEDURE inh_trigger2();=0A=0AINSERT INTO parent VALUES (1, 'one');=0AINSE=
RT INTO child VALUES (2, 'two');=0A=0AUPDATE parent SET val =3D 'changed' W=
HERE id =3D 1;=0AUPDATE parent SET val =3D 'changed' WHERE id =3D 2;=0A=0AD=
ELETE FROM parent WHERE id =3D 1;=0ADELETE FROM parent WHERE id =3D 2;=0A
--qcHopEYAB45HaUaB--