Subject: Clarify how triggers relate to transactions

From: PG Doc comments form <[email-masked]>
Date: Tue, 27 Apr 2021 14:26:48 +0000

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."

---

From: Laurenz Albe <[email-masked]>
Date: Wed, 28 Apr 2021 13:24:53 +0200

> the triggering event, and if a trigger fails, the transaction is rolled

Good idea in principle, but I'd put that information on
https://www.postgresql.org/docs/current/trigger-definition.html

Yours,
Laurenz Albe

---

From: Nathan Long <[email-masked]>
Date: Wed, 28 Apr 2021 13:18:40 -0400

Gotcha. Where would I go to make the PR?

---

From: Laurenz Albe <[email-masked]>
Date: Wed, 28 Apr 2021 20:17:49 +0200

> 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

---

From: "Euler Taveira" <[email-masked]>
Date: Sun, 02 May 2021 12:45:45 -0300

> 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/

---

From: Laurenz Albe <[email-masked]>
Date: Wed, 05 May 2021 11:55:20 +0200

> https://www.postgresql.org/docs/current/trigger-definition.html

Here is a proposed patch for this.

Yours,
Laurenz Albe
From 393cdf38d812b337b51e3f5ca2af6ca052ed1b17 Mon Sep 17 00:00:00 2001
From: Laurenz Albe <[email-masked]>
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

---

From: Laurenz Albe <[email-masked]>
Date: Thu, 20 May 2021 17:53:40 +0200

> Here is a proposed patch for this.

Replying to -hackers for the commitfest app.

Yours,
Laurenz Albe

---

From: Tom Lane <[email-masked]>
Date: Fri, 30 Jul 2021 16:20:23 -0400

> 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

index 3f4b5acc7b..e4afa1c01d 100644
 
   <para>
+   <indexterm>
index f1a845f756..7e2654493b 100644
@@ -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

---

From: Laurenz Albe <[email-masked]>
Date: Wed, 18 Aug 2021 13:06:05 +0200

> one that defines before/after/instead-of triggers.  How do you

That is better, and I like your patch.  Thanks!
Keeping paragraphs short is a good thing.

Yours,
Laurenz Albe

---

From: Tom Lane <[email-masked]>
Date: Wed, 01 Sep 2021 17:25:57 -0400

> Keeping paragraphs short is a good thing.

Pushed like that, then.

			regards, tom lane