Re: Trigger behaviour not as stated

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: "In contrast, row-level triggers are fired for all affected
> > partitions or child tables."
> > 
> > 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 +