public inbox for [email protected]
help / color / mirror / Atom feedFrom: Bruce Momjian <[email protected]>
To: [email protected]
Cc: [email protected]
Cc: Andrew Gierth <[email protected]>
Subject: Re: Trigger behaviour not as stated
Date: Wed, 24 Jan 2018 17:07:01 -0500
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<[email protected]>
On Wed, Jan 24, 2018 at 01:10:08PM -0500, Bruce Momjian wrote:
> On Wed, Nov 29, 2017 at 07:39:34PM +0000, [email protected] 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-par...
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 <[email protected]>
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 <[email protected]> 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 +
Attachments:
[text/x-diff] trigger.diff (1.4K, 2-trigger.diff)
download | inline 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.
[application/x-sql] trigger.sql (970B, 3-trigger.sql)
download
view thread (13+ messages) latest in thread
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: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: Trigger behaviour not as stated
In-Reply-To: <[email protected]>
* 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