public inbox for [email protected]help / color / mirror / Atom feed
Clarify how triggers relate to transactions 10+ messages / 5 participants [nested] [flat]
* Clarify how triggers relate to transactions @ 2021-04-27 14:26 PG Doc comments form <[email protected]> 0 siblings, 1 reply; 10+ messages in thread From: PG Doc comments form @ 2021-04-27 14:26 UTC (permalink / raw) To: [email protected]; +Cc: [email protected] The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/13/sql-createtrigger.html Description: https://www.postgresql.org/docs/current/sql-createtrigger.html mentions the word "transaction" only once, in reference specifically to constraint triggers: "They can be fired either at the end of the statement causing the triggering event, or at the end of the containing transaction; in the latter case they are said to be deferred." If I understand correctly, it would be helpful to add this sentence or a corrected version of it: "Triggers always execute in the same transaction as the triggering event, and if a trigger fails, the transaction is rolled back." ^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Clarify how triggers relate to transactions @ 2021-04-28 11:24 Laurenz Albe <[email protected]> parent: PG Doc comments form <[email protected]> 0 siblings, 2 replies; 10+ messages in thread From: Laurenz Albe @ 2021-04-28 11:24 UTC (permalink / raw) To: [email protected]; [email protected] On Tue, 2021-04-27 at 14:26 +0000, PG Doc comments form wrote: > The following documentation comment has been logged on the website: > > Page: https://www.postgresql.org/docs/13/sql-createtrigger.html > Description: > > https://www.postgresql.org/docs/current/sql-createtrigger.html mentions the > word "transaction" only once, in reference specifically to constraint > triggers: "They can be fired either at the end of the statement causing the > triggering event, or at the end of the containing transaction; in the latter > case they are said to be deferred." > > If I understand correctly, it would be helpful to add this sentence or a > corrected version of it: "Triggers always execute in the same transaction as > the triggering event, and if a trigger fails, the transaction is rolled > back." Good idea in principle, but I'd put that information on https://www.postgresql.org/docs/current/trigger-definition.html Yours, Laurenz Albe ^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Clarify how triggers relate to transactions @ 2021-04-28 17:18 Nathan Long <[email protected]> parent: Laurenz Albe <[email protected]> 1 sibling, 2 replies; 10+ messages in thread From: Nathan Long @ 2021-04-28 17:18 UTC (permalink / raw) To: Laurenz Albe <[email protected]>; +Cc: [email protected] Gotcha. Where would I go to make the PR? On Wed, Apr 28, 2021, 7:24 AM Laurenz Albe <[email protected]> wrote: > On Tue, 2021-04-27 at 14:26 +0000, PG Doc comments form wrote: > > The following documentation comment has been logged on the website: > > > > Page: https://www.postgresql.org/docs/13/sql-createtrigger.html > > Description: > > > > https://www.postgresql.org/docs/current/sql-createtrigger.html mentions > the > > word "transaction" only once, in reference specifically to constraint > > triggers: "They can be fired either at the end of the statement causing > the > > triggering event, or at the end of the containing transaction; in the > latter > > case they are said to be deferred." > > > > If I understand correctly, it would be helpful to add this sentence or a > > corrected version of it: "Triggers always execute in the same > transaction as > > the triggering event, and if a trigger fails, the transaction is rolled > > back." > > Good idea in principle, but I'd put that information on > https://www.postgresql.org/docs/current/trigger-definition.html > > Yours, > Laurenz Albe > > ^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Clarify how triggers relate to transactions @ 2021-04-28 18:17 Laurenz Albe <[email protected]> parent: Nathan Long <[email protected]> 1 sibling, 0 replies; 10+ messages in thread From: Laurenz Albe @ 2021-04-28 18:17 UTC (permalink / raw) To: Nathan Long <[email protected]>; +Cc: [email protected] On Wed, 2021-04-28 at 13:18 -0400, Nathan Long wrote: > Gotcha. Where would I go to make the PR? You'd create a patch against Git master and send it to this mailing list or pgsql-hackers. If you don't want it to fall between the cracks, register in the next commitfest where it can undergo peer review. Yours, Laurenz Albe ^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Clarify how triggers relate to transactions @ 2021-05-02 15:45 Euler Taveira <[email protected]> parent: Nathan Long <[email protected]> 1 sibling, 0 replies; 10+ messages in thread From: Euler Taveira @ 2021-05-02 15:45 UTC (permalink / raw) To: Nathan Long <[email protected]>; Laurenz Albe <[email protected]>; +Cc: [email protected] On Wed, Apr 28, 2021, at 2:18 PM, Nathan Long wrote: > Gotcha. Where would I go to make the PR? > There is no such PR feature; we don't use GitHub despite of having a mirror there. As Laurenz said you should create a patch (using your preferred git command) and attach to this thread. If you prefer, you can also send the patch to pgsql-hackers ML (add the link to this thread). The next step is to register your patch to the next commitfest [1] so we don't lose track of it. For a complete reference about submitting a patch, take a look at [2]. [1] https://commitfest.postgresql.org/33/ [2] https://wiki.postgresql.org/wiki/Submitting_a_Patch Regards, -- Euler Taveira EDB https://www.enterprisedb.com/ ^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Clarify how triggers relate to transactions @ 2021-05-05 09:55 Laurenz Albe <[email protected]> parent: Laurenz Albe <[email protected]> 1 sibling, 2 replies; 10+ messages in thread From: Laurenz Albe @ 2021-05-05 09:55 UTC (permalink / raw) To: [email protected]; [email protected] On Wed, 2021-04-28 at 13:24 +0200, Laurenz Albe wrote: > On Tue, 2021-04-27 at 14:26 +0000, PG Doc comments form wrote: > > https://www.postgresql.org/docs/current/sql-createtrigger.html mentions the > > word "transaction" only once, in reference specifically to constraint > > triggers: "They can be fired either at the end of the statement causing the > > triggering event, or at the end of the containing transaction; in the latter > > case they are said to be deferred." > > > > If I understand correctly, it would be helpful to add this sentence or a > > corrected version of it: "Triggers always execute in the same transaction as > > the triggering event, and if a trigger fails, the transaction is rolled > > back." > > Good idea in principle, but I'd put that information on > https://www.postgresql.org/docs/current/trigger-definition.html Here is a proposed patch for this. Yours, Laurenz Albe Attachments: [text/x-patch] 0001-Document-how-triggers-interact-with-transactions.patch (2.5K, 2-0001-Document-how-triggers-interact-with-transactions.patch) download | inline diff: From 393cdf38d812b337b51e3f5ca2af6ca052ed1b17 Mon Sep 17 00:00:00 2001 From: Laurenz Albe <[email protected]> Date: Wed, 5 May 2021 11:53:38 +0200 Subject: [PATCH] Document how triggers interact with transactions Clarify that triggers are always executed as part of the same transaction as the triggering statement, with all consequences. Add an index term for constraint triggers and mention them in the documentation of trigger behavior. --- doc/src/sgml/ref/create_trigger.sgml | 4 ++++ doc/src/sgml/trigger.sgml | 7 ++++++- 2 files changed, 10 insertions(+), 1 deletion(-) diff --git a/doc/src/sgml/ref/create_trigger.sgml b/doc/src/sgml/ref/create_trigger.sgml index 561af989a4..e46b9e79db 100644 --- a/doc/src/sgml/ref/create_trigger.sgml +++ b/doc/src/sgml/ref/create_trigger.sgml @@ -175,6 +175,10 @@ CREATE [ OR REPLACE ] [ CONSTRAINT ] TRIGGER <replaceable class="parameter">name </para> <para> + <indexterm> + <primary>trigger</primary> + <secondary>constraint trigger</secondary> + </indexterm> When the <literal>CONSTRAINT</literal> option is specified, this command creates a <firstterm>constraint trigger</firstterm>. This is the same as a regular trigger except that the timing of the trigger firing can be adjusted using diff --git a/doc/src/sgml/trigger.sgml b/doc/src/sgml/trigger.sgml index 4a0e74652f..0c8cc8d0fe 100644 --- a/doc/src/sgml/trigger.sgml +++ b/doc/src/sgml/trigger.sgml @@ -47,7 +47,9 @@ the <command>UPDATE</command> statement. Triggers can also fire for <command>TRUNCATE</command> statements. If a trigger event occurs, the trigger's function is called at the appropriate time to handle the - event. + event. All triggers are executed as part of the same transaction as + the statement that triggered them, so if either the statement or the + trigger causes an error, the effects of both will be rolled back. </para> <para> @@ -115,6 +117,9 @@ <literal>BEFORE</literal> triggers fire immediately before a particular row is operated on, while row-level <literal>AFTER</literal> triggers fire at the end of the statement (but before any statement-level <literal>AFTER</literal> triggers). + The execution of <literal>AFTER</literal> triggers can also be deferred to the + end of the transaction if they were defined as + <firstterm>constraint triggers</firstterm>. These types of triggers may only be defined on tables and foreign tables, not views. <literal>INSTEAD OF</literal> triggers may only be -- 2.26.3 ^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Clarify how triggers relate to transactions @ 2021-05-20 15:53 Laurenz Albe <[email protected]> parent: Laurenz Albe <[email protected]> 1 sibling, 0 replies; 10+ messages in thread From: Laurenz Albe @ 2021-05-20 15:53 UTC (permalink / raw) To: [email protected]; [email protected] On Wed, 2021-05-05 at 11:55 +0200, Laurenz Albe wrote: > On Wed, 2021-04-28 at 13:24 +0200, Laurenz Albe wrote: > > On Tue, 2021-04-27 at 14:26 +0000, PG Doc comments form wrote: > > > https://www.postgresql.org/docs/current/sql-createtrigger.html mentions the > > > word "transaction" only once, in reference specifically to constraint > > > triggers: "They can be fired either at the end of the statement causing the > > > triggering event, or at the end of the containing transaction; in the latter > > > case they are said to be deferred." > > > > > > If I understand correctly, it would be helpful to add this sentence or a > > > corrected version of it: "Triggers always execute in the same transaction as > > > the triggering event, and if a trigger fails, the transaction is rolled > > > back." > > > > Good idea in principle, but I'd put that information on > > https://www.postgresql.org/docs/current/trigger-definition.html > > Here is a proposed patch for this. Replying to -hackers for the commitfest app. Yours, Laurenz Albe ^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Clarify how triggers relate to transactions @ 2021-07-30 20:20 Tom Lane <[email protected]> parent: Laurenz Albe <[email protected]> 1 sibling, 1 reply; 10+ messages in thread From: Tom Lane @ 2021-07-30 20:20 UTC (permalink / raw) To: Laurenz Albe <[email protected]>; +Cc: [email protected]; [email protected] Laurenz Albe <[email protected]> writes: > On Wed, 2021-04-28 at 13:24 +0200, Laurenz Albe wrote: >> On Tue, 2021-04-27 at 14:26 +0000, PG Doc comments form wrote: >>> If I understand correctly, it would be helpful to add this sentence or a >>> corrected version of it: "Triggers always execute in the same transaction as >>> the triggering event, and if a trigger fails, the transaction is rolled >>> back." >> Good idea in principle, but I'd put that information on >> https://www.postgresql.org/docs/current/trigger-definition.html > Here is a proposed patch for this. I think this is a good idea, but I felt like you'd added the extra sentences in not-terribly-well-chosen places. For instance, your first addition in trigger.sgml is adding to a para that talks about triggers for tables, while the next para talks about triggers for views. So it seems unclear whether the statement is meant to apply to view triggers too. I think it'd work out best to make this a separate para after the one that defines before/after/instead-of triggers. How do you like the attached? regards, tom lane Attachments: [text/x-diff] 0001-Document-how-triggers-interact-with-transactions-2.patch (1.6K, 2-0001-Document-how-triggers-interact-with-transactions-2.patch) download | inline diff: diff --git a/doc/src/sgml/ref/create_trigger.sgml b/doc/src/sgml/ref/create_trigger.sgml index 3f4b5acc7b..e4afa1c01d 100644 --- a/doc/src/sgml/ref/create_trigger.sgml +++ b/doc/src/sgml/ref/create_trigger.sgml @@ -175,6 +175,10 @@ CREATE [ OR REPLACE ] [ CONSTRAINT ] TRIGGER <replaceable class="parameter">name </para> <para> + <indexterm> + <primary>trigger</primary> + <secondary>constraint trigger</secondary> + </indexterm> When the <literal>CONSTRAINT</literal> option is specified, this command creates a <firstterm>constraint trigger</firstterm>. This is the same as a regular trigger except that the timing of the trigger firing can be adjusted using diff --git a/doc/src/sgml/trigger.sgml b/doc/src/sgml/trigger.sgml index f1a845f756..7e2654493b 100644 --- a/doc/src/sgml/trigger.sgml +++ b/doc/src/sgml/trigger.sgml @@ -122,6 +122,15 @@ row in the view is identified as needing to be operated on. </para> + <para> + The execution of an <literal>AFTER</literal> trigger can be deferred + to the end of the transaction, rather than the end of the statement, + if it was defined as a <firstterm>constraint trigger</firstterm>. + In all cases, a trigger is executed as part of the same transaction as + the statement that triggered it, so if either the statement or the + trigger causes an error, the effects of both will be rolled back. + </para> + <para> A statement that targets a parent table in an inheritance or partitioning hierarchy does not cause the statement-level triggers of affected child ^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Clarify how triggers relate to transactions @ 2021-08-18 11:06 Laurenz Albe <[email protected]> parent: Tom Lane <[email protected]> 0 siblings, 1 reply; 10+ messages in thread From: Laurenz Albe @ 2021-08-18 11:06 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: [email protected]; [email protected] On Fri, 2021-07-30 at 16:20 -0400, Tom Lane wrote: > Laurenz Albe <[email protected]> writes: > > On Wed, 2021-04-28 at 13:24 +0200, Laurenz Albe wrote: > > > On Tue, 2021-04-27 at 14:26 +0000, PG Doc comments form wrote: > > > > If I understand correctly, it would be helpful to add this sentence or a > > > > corrected version of it: "Triggers always execute in the same transaction as > > > > the triggering event, and if a trigger fails, the transaction is rolled > > > > back." > > > > Here is a proposed patch for this. > > I think this is a good idea, but I felt like you'd added the extra > sentences in not-terribly-well-chosen places. For instance, your > first addition in trigger.sgml is adding to a para that talks about > triggers for tables, while the next para talks about triggers for > views. So it seems unclear whether the statement is meant to apply > to view triggers too. > > I think it'd work out best to make this a separate para after the > one that defines before/after/instead-of triggers. How do you > like the attached? That is better, and I like your patch. Thanks! Keeping paragraphs short is a good thing. Yours, Laurenz Albe ^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Clarify how triggers relate to transactions @ 2021-09-01 21:25 Tom Lane <[email protected]> parent: Laurenz Albe <[email protected]> 0 siblings, 0 replies; 10+ messages in thread From: Tom Lane @ 2021-09-01 21:25 UTC (permalink / raw) To: Laurenz Albe <[email protected]>; +Cc: [email protected]; [email protected] Laurenz Albe <[email protected]> writes: > On Fri, 2021-07-30 at 16:20 -0400, Tom Lane wrote: >> I think it'd work out best to make this a separate para after the >> one that defines before/after/instead-of triggers. How do you >> like the attached? > That is better, and I like your patch. Thanks! > Keeping paragraphs short is a good thing. Pushed like that, then. regards, tom lane ^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2021-09-01 21:25 UTC | newest] Thread overview: 10+ messages (download: mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2021-04-27 14:26 Clarify how triggers relate to transactions PG Doc comments form <[email protected]> 2021-04-28 11:24 ` Laurenz Albe <[email protected]> 2021-04-28 17:18 ` Nathan Long <[email protected]> 2021-04-28 18:17 ` Laurenz Albe <[email protected]> 2021-05-02 15:45 ` Euler Taveira <[email protected]> 2021-05-05 09:55 ` Laurenz Albe <[email protected]> 2021-05-20 15:53 ` Laurenz Albe <[email protected]> 2021-07-30 20:20 ` Tom Lane <[email protected]> 2021-08-18 11:06 ` Laurenz Albe <[email protected]> 2021-09-01 21:25 ` Tom Lane <[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