From: | Bruce Momjian <bruce(at)momjian(dot)us> |
---|---|
To: | ian(at)thepathcentral(dot)com |
Cc: | pgsql-docs(at)postgresql(dot)org, Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk> |
Subject: | Re: Trigger behaviour not as stated |
Date: | 2018-01-24 22:07:01 |
Message-ID: | 20180124220701.GP17109@momjian.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-docs |
On Wed, Jan 24, 2018 at 01:10:08PM -0500, Bruce Momjian wrote:
> On Wed, Nov 29, 2017 at 07:39:34PM +0000, ian(at)thepathcentral(dot)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:
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(at)postgresql(dot)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(at)momjian(dot)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 +
Attachment | Content-Type | Size |
---|---|---|
trigger.diff | text/x-diff | 1.4 KB |
trigger.sql | application/x-sql | 970 bytes |
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 2018-01-25 17:46:52 | Re: Bug in pg_upgrade standby rsync doc |
Previous Message | Bruce Momjian | 2018-01-24 18:10:08 | Re: Trigger behaviour not as stated |