public inbox for [email protected]
help / color / mirror / Atom feedTrigger behaviour not as stated
13+ messages / 5 participants
[nested] [flat]
* Trigger behaviour not as stated
@ 2017-11-29 19:39 [email protected]
2018-01-24 18:10 ` Re: Trigger behaviour not as stated Bruce Momjian <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: [email protected] @ 2017-11-29 19:39 UTC (permalink / raw)
To: pgsql-docs
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.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Trigger behaviour not as stated
2017-11-29 19:39 Trigger behaviour not as stated [email protected]
@ 2018-01-24 18:10 ` Bruce Momjian <[email protected]>
2018-01-24 22:07 ` Re: Trigger behaviour not as stated Bruce Momjian <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: Bruce Momjian @ 2018-01-24 18:10 UTC (permalink / raw)
To: [email protected]; +Cc: pgsql-docs
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.
--
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 +
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Trigger behaviour not as stated
2017-11-29 19:39 Trigger behaviour not as stated [email protected]
2018-01-24 18:10 ` Re: Trigger behaviour not as stated Bruce Momjian <[email protected]>
@ 2018-01-24 22:07 ` Bruce Momjian <[email protected]>
2018-01-28 10:19 ` Re: Trigger behaviour not as stated Thomas Munro <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: Bruce Momjian @ 2018-01-24 22:07 UTC (permalink / raw)
To: [email protected]; +Cc: pgsql-docs; Andrew Gierth <[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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Trigger behaviour not as stated
2017-11-29 19:39 Trigger behaviour not as stated [email protected]
2018-01-24 18:10 ` Re: Trigger behaviour not as stated Bruce Momjian <[email protected]>
2018-01-24 22:07 ` Re: Trigger behaviour not as stated Bruce Momjian <[email protected]>
@ 2018-01-28 10:19 ` Thomas Munro <[email protected]>
2018-01-28 19:18 ` Re: Trigger behaviour not as stated Tom Lane <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: Thomas Munro @ 2018-01-28 10:19 UTC (permalink / raw)
To: Bruce Momjian <[email protected]>; +Cc: [email protected]; pgsql-docs; Andrew Gierth <[email protected]>
On Thu, Jan 25, 2018 at 11:07 AM, Bruce Momjian <[email protected]> wrote:
> 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.
>
> 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.
+1
I was trying to convey that, but it does seem a little terse and
cryptic. Your addition of "referenced" and "only" make it clearer.
--
Thomas Munro
http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Trigger behaviour not as stated
2017-11-29 19:39 Trigger behaviour not as stated [email protected]
2018-01-24 18:10 ` Re: Trigger behaviour not as stated Bruce Momjian <[email protected]>
2018-01-24 22:07 ` Re: Trigger behaviour not as stated Bruce Momjian <[email protected]>
2018-01-28 10:19 ` Re: Trigger behaviour not as stated Thomas Munro <[email protected]>
@ 2018-01-28 19:18 ` Tom Lane <[email protected]>
2018-01-28 20:34 ` Re: Trigger behaviour not as stated Bruce Momjian <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: Tom Lane @ 2018-01-28 19:18 UTC (permalink / raw)
To: Thomas Munro <[email protected]>; +Cc: Bruce Momjian <[email protected]>; [email protected]; pgsql-docs; Andrew Gierth <[email protected]>
Thomas Munro <[email protected]> writes:
> On Thu, Jan 25, 2018 at 11:07 AM, Bruce Momjian <[email protected]> wrote:
>> ... 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.
> I was trying to convey that, but it does seem a little terse and
> cryptic. Your addition of "referenced" and "only" make it clearer.
Hm, the first part of Bruce's change seems fine, but I think this wording:
... In contrast,
! row-level triggers are fired only on affected partitions or child tables,
! even if they are not referenced in the query.
is still confusing. How about something like
In contrast, row-level triggers are fired for each actual row change,
including changes in partitions or child tables that are not directly
named in the query.
Possibly "row operation" would be better than "row change".
regards, tom lane
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Trigger behaviour not as stated
2017-11-29 19:39 Trigger behaviour not as stated [email protected]
2018-01-24 18:10 ` Re: Trigger behaviour not as stated Bruce Momjian <[email protected]>
2018-01-24 22:07 ` Re: Trigger behaviour not as stated Bruce Momjian <[email protected]>
2018-01-28 10:19 ` Re: Trigger behaviour not as stated Thomas Munro <[email protected]>
2018-01-28 19:18 ` Re: Trigger behaviour not as stated Tom Lane <[email protected]>
@ 2018-01-28 20:34 ` Bruce Momjian <[email protected]>
2018-01-28 20:57 ` Re: Trigger behaviour not as stated Tom Lane <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: Bruce Momjian @ 2018-01-28 20:34 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Thomas Munro <[email protected]>; [email protected]; pgsql-docs; Andrew Gierth <[email protected]>
On Sun, Jan 28, 2018 at 02:18:38PM -0500, Tom Lane wrote:
> Thomas Munro <[email protected]> writes:
> > On Thu, Jan 25, 2018 at 11:07 AM, Bruce Momjian <[email protected]> wrote:
> >> ... 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.
>
> > I was trying to convey that, but it does seem a little terse and
> > cryptic. Your addition of "referenced" and "only" make it clearer.
>
> Hm, the first part of Bruce's change seems fine, but I think this wording:
>
> ... In contrast,
> ! row-level triggers are fired only on affected partitions or child tables,
> ! even if they are not referenced in the query.
>
> is still confusing. How about something like
>
> In contrast, row-level triggers are fired for each actual row change,
> including changes in partitions or child tables that are not directly
> named in the query.
>
> Possibly "row operation" would be better than "row change".
Uh, I don't think we want to highlight the statement vs row difference
here but the fact that statement triggers fire on the referenced object
and not on the effected rows. I have attached an updated patch which I
think is an improvement.
--
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.5K, 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..42649d2
*** 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 on the rows in effected 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.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Trigger behaviour not as stated
2017-11-29 19:39 Trigger behaviour not as stated [email protected]
2018-01-24 18:10 ` Re: Trigger behaviour not as stated Bruce Momjian <[email protected]>
2018-01-24 22:07 ` Re: Trigger behaviour not as stated Bruce Momjian <[email protected]>
2018-01-28 10:19 ` Re: Trigger behaviour not as stated Thomas Munro <[email protected]>
2018-01-28 19:18 ` Re: Trigger behaviour not as stated Tom Lane <[email protected]>
2018-01-28 20:34 ` Re: Trigger behaviour not as stated Bruce Momjian <[email protected]>
@ 2018-01-28 20:57 ` Tom Lane <[email protected]>
2018-01-28 22:58 ` Re: Trigger behaviour not as stated Bruce Momjian <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: Tom Lane @ 2018-01-28 20:57 UTC (permalink / raw)
To: Bruce Momjian <[email protected]>; +Cc: Thomas Munro <[email protected]>; [email protected]; pgsql-docs; Andrew Gierth <[email protected]>
Bruce Momjian <[email protected]> writes:
> Uh, I don't think we want to highlight the statement vs row difference
> here but the fact that statement triggers fire on the referenced object
> and not on the effected rows. I have attached an updated patch which I
> think is an improvement
statement-level triggers for its partitions or child tables. In contrast,
! row-level triggers are fired on the rows in effected partitions or
! child tables, even if they are not referenced in the query.
I still think that that's not well written. A large part of the confusion
here is over what "referenced" means. To my mind, child tables/partitions
are referenced by an inherited query, just not explicitly. So that's why
I'd prefer wording like "directly named in the query" (or "explicitly
named"). If you insist on using "referenced" you could write "explicitly
referenced", but IMO that's longer and no clearer.
A lesser complaint is that this reads like the antecedent of "they" is the
rows, not the tables containing them, making the meaning of "referenced"
even less clear.
Maybe something like
In contrast, row-level triggers are fired for individual row change
events, and the triggers that are fired for an event are those
attached to the specific table containing the changed row, even if
it is a partition or child table not directly named in the query.
regards, tom lane
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Trigger behaviour not as stated
2017-11-29 19:39 Trigger behaviour not as stated [email protected]
2018-01-24 18:10 ` Re: Trigger behaviour not as stated Bruce Momjian <[email protected]>
2018-01-24 22:07 ` Re: Trigger behaviour not as stated Bruce Momjian <[email protected]>
2018-01-28 10:19 ` Re: Trigger behaviour not as stated Thomas Munro <[email protected]>
2018-01-28 19:18 ` Re: Trigger behaviour not as stated Tom Lane <[email protected]>
2018-01-28 20:34 ` Re: Trigger behaviour not as stated Bruce Momjian <[email protected]>
2018-01-28 20:57 ` Re: Trigger behaviour not as stated Tom Lane <[email protected]>
@ 2018-01-28 22:58 ` Bruce Momjian <[email protected]>
2018-01-28 23:12 ` Re: Trigger behaviour not as stated Tom Lane <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: Bruce Momjian @ 2018-01-28 22:58 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Thomas Munro <[email protected]>; [email protected]; pgsql-docs; Andrew Gierth <[email protected]>
On Sun, Jan 28, 2018 at 03:57:07PM -0500, Tom Lane wrote:
> Bruce Momjian <[email protected]> writes:
> > Uh, I don't think we want to highlight the statement vs row difference
> > here but the fact that statement triggers fire on the referenced object
> > and not on the effected rows. I have attached an updated patch which I
> > think is an improvement
>
> statement-level triggers for its partitions or child tables. In contrast,
> ! row-level triggers are fired on the rows in effected partitions or
> ! child tables, even if they are not referenced in the query.
>
> I still think that that's not well written. A large part of the confusion
> here is over what "referenced" means. To my mind, child tables/partitions
> are referenced by an inherited query, just not explicitly. So that's why
> I'd prefer wording like "directly named in the query" (or "explicitly
> named"). If you insist on using "referenced" you could write "explicitly
> referenced", but IMO that's longer and no clearer.
>
> A lesser complaint is that this reads like the antecedent of "they" is the
> rows, not the tables containing them, making the meaning of "referenced"
> even less clear.
>
> Maybe something like
>
> In contrast, row-level triggers are fired for individual row change
> events, and the triggers that are fired for an event are those
> attached to the specific table containing the changed row, even if
> it is a partition or child table not directly named in the query.
Oh, I am sorry. I was focused on the first part of the sentence and
didn't notice your change to the second part. How is this attachment?
--
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.5K, 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..6519757
*** 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 on the rows in effected partitions or
! child tables, even if they are not explicitly named 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.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Trigger behaviour not as stated
2017-11-29 19:39 Trigger behaviour not as stated [email protected]
2018-01-24 18:10 ` Re: Trigger behaviour not as stated Bruce Momjian <[email protected]>
2018-01-24 22:07 ` Re: Trigger behaviour not as stated Bruce Momjian <[email protected]>
2018-01-28 10:19 ` Re: Trigger behaviour not as stated Thomas Munro <[email protected]>
2018-01-28 19:18 ` Re: Trigger behaviour not as stated Tom Lane <[email protected]>
2018-01-28 20:34 ` Re: Trigger behaviour not as stated Bruce Momjian <[email protected]>
2018-01-28 20:57 ` Re: Trigger behaviour not as stated Tom Lane <[email protected]>
2018-01-28 22:58 ` Re: Trigger behaviour not as stated Bruce Momjian <[email protected]>
@ 2018-01-28 23:12 ` Tom Lane <[email protected]>
2018-01-28 23:17 ` Re: Trigger behaviour not as stated Bruce Momjian <[email protected]>
0 siblings, 1 reply; 13+ messages in thread
From: Tom Lane @ 2018-01-28 23:12 UTC (permalink / raw)
To: Bruce Momjian <[email protected]>; +Cc: Thomas Munro <[email protected]>; [email protected]; pgsql-docs; Andrew Gierth <[email protected]>
Bruce Momjian <[email protected]> writes:
> Oh, I am sorry. I was focused on the first part of the sentence and
> didn't notice your change to the second part. How is this attachment?
Seems same as your previous version?
regards, tom lane
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Trigger behaviour not as stated
2017-11-29 19:39 Trigger behaviour not as stated [email protected]
2018-01-24 18:10 ` Re: Trigger behaviour not as stated Bruce Momjian <[email protected]>
2018-01-24 22:07 ` Re: Trigger behaviour not as stated Bruce Momjian <[email protected]>
2018-01-28 10:19 ` Re: Trigger behaviour not as stated Thomas Munro <[email protected]>
2018-01-28 19:18 ` Re: Trigger behaviour not as stated Tom Lane <[email protected]>
2018-01-28 20:34 ` Re: Trigger behaviour not as stated Bruce Momjian <[email protected]>
2018-01-28 20:57 ` Re: Trigger behaviour not as stated Tom Lane <[email protected]>
2018-01-28 22:58 ` Re: Trigger behaviour not as stated Bruce Momjian <[email protected]>
2018-01-28 23:12 ` Re: Trigger behaviour not as stated Tom Lane <[email protected]>
@ 2018-01-28 23:17 ` Bruce Momjian <[email protected]>
2018-01-29 11:32 ` Re: Trigger behaviour not as stated Ian R. Campbell <[email protected]>
2018-01-31 22:00 ` Re: Trigger behaviour not as stated Bruce Momjian <[email protected]>
0 siblings, 2 replies; 13+ messages in thread
From: Bruce Momjian @ 2018-01-28 23:17 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Thomas Munro <[email protected]>; [email protected]; pgsql-docs; Andrew Gierth <[email protected]>
On Sun, Jan 28, 2018 at 06:12:01PM -0500, Tom Lane wrote:
> Bruce Momjian <[email protected]> writes:
> > Oh, I am sorry. I was focused on the first part of the sentence and
> > didn't notice your change to the second part. How is this attachment?
>
> Seems same as your previous version?
OK, new vesion that uses "explicitly named" in both modified doc lines.
--
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.5K, 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..dab1041
*** 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 attached to the explicitly named table, but not
statement-level triggers for its partitions or child tables. In contrast,
! row-level triggers are fired on the rows in effected partitions or
! child tables, even if they are not explicitly named 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.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Trigger behaviour not as stated
2017-11-29 19:39 Trigger behaviour not as stated [email protected]
2018-01-24 18:10 ` Re: Trigger behaviour not as stated Bruce Momjian <[email protected]>
2018-01-24 22:07 ` Re: Trigger behaviour not as stated Bruce Momjian <[email protected]>
2018-01-28 10:19 ` Re: Trigger behaviour not as stated Thomas Munro <[email protected]>
2018-01-28 19:18 ` Re: Trigger behaviour not as stated Tom Lane <[email protected]>
2018-01-28 20:34 ` Re: Trigger behaviour not as stated Bruce Momjian <[email protected]>
2018-01-28 20:57 ` Re: Trigger behaviour not as stated Tom Lane <[email protected]>
2018-01-28 22:58 ` Re: Trigger behaviour not as stated Bruce Momjian <[email protected]>
2018-01-28 23:12 ` Re: Trigger behaviour not as stated Tom Lane <[email protected]>
2018-01-28 23:17 ` Re: Trigger behaviour not as stated Bruce Momjian <[email protected]>
@ 2018-01-29 11:32 ` Ian R. Campbell <[email protected]>
2018-01-29 15:28 ` Re: Trigger behaviour not as stated Bruce Momjian <[email protected]>
1 sibling, 1 reply; 13+ messages in thread
From: Ian R. Campbell @ 2018-01-29 11:32 UTC (permalink / raw)
To: Bruce Momjian <[email protected]>; +Cc: Tom Lane <[email protected]>; Thomas Munro <[email protected]>; [email protected]; pgsql-docs; Andrew Gierth <[email protected]>
The original confusion on this is answered in part by the following
statement (taken from the answer to my SO question):
"UPDATEs and DELETEs on the parent table will affect rows in the child
tables (if you don't specify ONLY), but triggers will only be fired by data
modifications directed directly against the table with the trigger on it "
This clearly explains that a trigger attached to a parent table will not
fire if the data being modified is in a child table.
The second part of the confusion is that INSERT is not considered to be a
row modification and will fire a BEFORE INSERT trigger on the parent table
even when the data goes into a child (whereas UPDATE and DELETE will not
fire a parent trigger).
My proposal (adapted from Tom's):
In contrast, row-level UPDATE and DELETE triggers are fired for individual
row change
events only on the table to which the trigger is attached. Therefore,
UPDATE and DELETE triggers on
a parent table will only fire when rows in the parent table are being
modified. Likewise,
UPDATE and DELETE triggers on a child table will only fire when rows in
the child table are being modified.
Note that INSERT statements do not follow these update rules, so
statements run on parent tables will
insert rows in child tables if the trigger function so directs.
The last line may need a bit of work, but I feel the text above it is clear.
Ian
On Mon, 29 Jan 2018 at 07:17 Bruce Momjian <[email protected]> wrote:
> On Sun, Jan 28, 2018 at 06:12:01PM -0500, Tom Lane wrote:
> > Bruce Momjian <[email protected]> writes:
> > > Oh, I am sorry. I was focused on the first part of the sentence and
> > > didn't notice your change to the second part. How is this attachment?
> >
> > Seems same as your previous version?
>
> OK, new vesion that uses "explicitly named" in both modified doc lines.
>
> --
> 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 +
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Trigger behaviour not as stated
2017-11-29 19:39 Trigger behaviour not as stated [email protected]
2018-01-24 18:10 ` Re: Trigger behaviour not as stated Bruce Momjian <[email protected]>
2018-01-24 22:07 ` Re: Trigger behaviour not as stated Bruce Momjian <[email protected]>
2018-01-28 10:19 ` Re: Trigger behaviour not as stated Thomas Munro <[email protected]>
2018-01-28 19:18 ` Re: Trigger behaviour not as stated Tom Lane <[email protected]>
2018-01-28 20:34 ` Re: Trigger behaviour not as stated Bruce Momjian <[email protected]>
2018-01-28 20:57 ` Re: Trigger behaviour not as stated Tom Lane <[email protected]>
2018-01-28 22:58 ` Re: Trigger behaviour not as stated Bruce Momjian <[email protected]>
2018-01-28 23:12 ` Re: Trigger behaviour not as stated Tom Lane <[email protected]>
2018-01-28 23:17 ` Re: Trigger behaviour not as stated Bruce Momjian <[email protected]>
2018-01-29 11:32 ` Re: Trigger behaviour not as stated Ian R. Campbell <[email protected]>
@ 2018-01-29 15:28 ` Bruce Momjian <[email protected]>
0 siblings, 0 replies; 13+ messages in thread
From: Bruce Momjian @ 2018-01-29 15:28 UTC (permalink / raw)
To: Ian R. Campbell <[email protected]>; +Cc: Tom Lane <[email protected]>; Thomas Munro <[email protected]>; [email protected]; pgsql-docs; Andrew Gierth <[email protected]>
On Mon, Jan 29, 2018 at 11:32:34AM +0000, Ian R. Campbell wrote:
> The second part of the confusion is that INSERT is not considered to be a row
> modification and will fire a BEFORE INSERT trigger on the parent table even
> when the data goes into a child (whereas UPDATE and DELETE will not fire a
> parent trigger).
Ian, that is not true based on my testing. Running that attached script
that I already posted shows:
test=> INSERT INTO parent VALUES (1, 'one');
NOTICE: Called by parent INSERT
INSERT 0 1
test=> INSERT INTO child VALUES (2, 'two');
--> NOTICE: Called by child INSERT
INSERT 0 1
--
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:
[application/x-sql] trigger.sql (970B, 2-trigger.sql)
download
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Trigger behaviour not as stated
2017-11-29 19:39 Trigger behaviour not as stated [email protected]
2018-01-24 18:10 ` Re: Trigger behaviour not as stated Bruce Momjian <[email protected]>
2018-01-24 22:07 ` Re: Trigger behaviour not as stated Bruce Momjian <[email protected]>
2018-01-28 10:19 ` Re: Trigger behaviour not as stated Thomas Munro <[email protected]>
2018-01-28 19:18 ` Re: Trigger behaviour not as stated Tom Lane <[email protected]>
2018-01-28 20:34 ` Re: Trigger behaviour not as stated Bruce Momjian <[email protected]>
2018-01-28 20:57 ` Re: Trigger behaviour not as stated Tom Lane <[email protected]>
2018-01-28 22:58 ` Re: Trigger behaviour not as stated Bruce Momjian <[email protected]>
2018-01-28 23:12 ` Re: Trigger behaviour not as stated Tom Lane <[email protected]>
2018-01-28 23:17 ` Re: Trigger behaviour not as stated Bruce Momjian <[email protected]>
@ 2018-01-31 22:00 ` Bruce Momjian <[email protected]>
1 sibling, 0 replies; 13+ messages in thread
From: Bruce Momjian @ 2018-01-31 22:00 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Thomas Munro <[email protected]>; [email protected]; pgsql-docs; Andrew Gierth <[email protected]>
On Sun, Jan 28, 2018 at 06:17:19PM -0500, Bruce Momjian wrote:
> On Sun, Jan 28, 2018 at 06:12:01PM -0500, Tom Lane wrote:
> > Bruce Momjian <[email protected]> writes:
> > > Oh, I am sorry. I was focused on the first part of the sentence and
> > > didn't notice your change to the second part. How is this attachment?
> >
> > Seems same as your previous version?
>
> OK, new vesion that uses "explicitly named" in both modified doc lines.
Applied and backpatched through 10, where the text was added.
--
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 +
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2018-01-31 22:00 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-11-29 19:39 Trigger behaviour not as stated [email protected]
2018-01-24 18:10 ` Bruce Momjian <[email protected]>
2018-01-24 22:07 ` Bruce Momjian <[email protected]>
2018-01-28 10:19 ` Thomas Munro <[email protected]>
2018-01-28 19:18 ` Tom Lane <[email protected]>
2018-01-28 20:34 ` Bruce Momjian <[email protected]>
2018-01-28 20:57 ` Tom Lane <[email protected]>
2018-01-28 22:58 ` Bruce Momjian <[email protected]>
2018-01-28 23:12 ` Tom Lane <[email protected]>
2018-01-28 23:17 ` Bruce Momjian <[email protected]>
2018-01-29 11:32 ` Ian R. Campbell <[email protected]>
2018-01-29 15:28 ` Bruce Momjian <[email protected]>
2018-01-31 22:00 ` Bruce Momjian <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox