On Wed, Jan 24, 2018 at 01:10:08PM -0500, Bruce Momjian wrote: > On Wed, Nov 29, 2017 at 07:39:34PM +0000, i...@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 <rhodiumt...@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 <br...@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 +
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.
trigger.sql
Description: application/sql