Document transition table triggers are not allowed on views/foreign tables
Hi,
Another thing I noticed about transition tables is that while we
prohibit transition tables on views/foreign tables, there is no
description about that in the user-facing documentation. So I would
like to propose to do $SUBJECT in create_trigger.sgml. Attached is a
patch for that.
Best regards,
Etsuro Fujita
Attachments:
0002-Update-documentation-about-transition-tables.patchapplication/octet-stream; name=0002-Update-documentation-about-transition-tables.patchDownload
From 56660a767b520273cc2d4670092c464de0e991c3 Mon Sep 17 00:00:00 2001
From: Etsuro Fujita <efujita@postgresql.org>
Date: Sun, 13 Jul 2025 19:09:49 +0900
Subject: [PATCH 2/2] Update documentation about transition tables.
---
doc/src/sgml/ref/create_trigger.sgml | 2 ++
1 file changed, 2 insertions(+)
diff --git a/doc/src/sgml/ref/create_trigger.sgml b/doc/src/sgml/ref/create_trigger.sgml
index 982ab6f3ee4..b8e6f5ad578 100644
--- a/doc/src/sgml/ref/create_trigger.sgml
+++ b/doc/src/sgml/ref/create_trigger.sgml
@@ -208,6 +208,8 @@ CREATE [ OR REPLACE ] [ CONSTRAINT ] TRIGGER <replaceable class="parameter">name
a trigger that can fire on <literal>UPDATE</literal> or <literal>INSERT</literal>;
it creates a transition relation containing the <firstterm>after-images</firstterm>
of all rows updated or inserted by the statement.
+ Currently, this option cannot be specified for <literal>AFTER</literal>
+ triggers on views or foreign tables.
</para>
<para>
--
2.39.5 (Apple Git-154)
On Tue, Jul 15, 2025 at 4:55 PM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
Another thing I noticed about transition tables is that while we
prohibit transition tables on views/foreign tables, there is no
description about that in the user-facing documentation. So I would
like to propose to do $SUBJECT in create_trigger.sgml. Attached is a
patch for that.
If there are no objections, I will push this and back-patch it to all
supported versions.
Best regards,
Etsuro Fujita
On Wed, Jul 23, 2025 at 4:16 PM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
On Tue, Jul 15, 2025 at 4:55 PM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
Another thing I noticed about transition tables is that while we
prohibit transition tables on views/foreign tables, there is no
description about that in the user-facing documentation. So I would
like to propose to do $SUBJECT in create_trigger.sgml. Attached is a
patch for that.
I think the restriction should be specified in a manner similar to how
restriction on CONSTRAINT option for foreign tables is specified i.e.
in " This option is only allowed for an AFTER trigger that is not a
constraint trigger; also, if the trigger is an UPDATE trigger, it must
not specify a column_name list.". But that sentence is already a bit
complex because of ; also, ... part. How about splitting the sentence
into two and mentioning restriction like below?
"This option is only allowed for an AFTER trigger on tables other than
views or foreign tables. The trigger should not be a constraint
trigger. If the trigger is an UPDATE trigger, it must not specify a
column_name list when using this option."
--
Best Wishes,
Ashutosh Bapat
Hi Ashutosh,
On Thu, Jul 24, 2025 at 12:06 AM Ashutosh Bapat
<ashutosh.bapat.oss@gmail.com> wrote:
On Wed, Jul 23, 2025 at 4:16 PM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
On Tue, Jul 15, 2025 at 4:55 PM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
Another thing I noticed about transition tables is that while we
prohibit transition tables on views/foreign tables, there is no
description about that in the user-facing documentation. So I would
like to propose to do $SUBJECT in create_trigger.sgml. Attached is a
patch for that.I think the restriction should be specified in a manner similar to how
restriction on CONSTRAINT option for foreign tables is specified i.e.
in " This option is only allowed for an AFTER trigger that is not a
constraint trigger; also, if the trigger is an UPDATE trigger, it must
not specify a column_name list.". But that sentence is already a bit
complex because of ; also, ... part. How about splitting the sentence
into two and mentioning restriction like below?"This option is only allowed for an AFTER trigger on tables other than
views or foreign tables. The trigger should not be a constraint
trigger. If the trigger is an UPDATE trigger, it must not specify a
column_name list when using this option."
Good idea! This might be nitpicking, but one thing I noticed is this
part of the first sentence: "an AFTER trigger on tables other than
views or foreign tables". Like the CONSTRAINT-restrictions
description above, how about just saying "an AFTER trigger on a plain
table (not a foreign table)"? No need to mention views, so I removed
that. I also changed to singular because that sounds natural. My
first language is not English, though. Other than that the change
looks good to me.
Thanks for the review!
Best regards,
Etsuro Fujita
On Fri, Jul 25, 2025 at 3:22 PM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
Hi Ashutosh,
On Thu, Jul 24, 2025 at 12:06 AM Ashutosh Bapat
<ashutosh.bapat.oss@gmail.com> wrote:On Wed, Jul 23, 2025 at 4:16 PM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
On Tue, Jul 15, 2025 at 4:55 PM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
Another thing I noticed about transition tables is that while we
prohibit transition tables on views/foreign tables, there is no
description about that in the user-facing documentation. So I would
like to propose to do $SUBJECT in create_trigger.sgml. Attached is a
patch for that.I think the restriction should be specified in a manner similar to how
restriction on CONSTRAINT option for foreign tables is specified i.e.
in " This option is only allowed for an AFTER trigger that is not a
constraint trigger; also, if the trigger is an UPDATE trigger, it must
not specify a column_name list.". But that sentence is already a bit
complex because of ; also, ... part. How about splitting the sentence
into two and mentioning restriction like below?"This option is only allowed for an AFTER trigger on tables other than
views or foreign tables. The trigger should not be a constraint
trigger. If the trigger is an UPDATE trigger, it must not specify a
column_name list when using this option."Good idea! This might be nitpicking, but one thing I noticed is this
part of the first sentence: "an AFTER trigger on tables other than
views or foreign tables". Like the CONSTRAINT-restrictions
description above, how about just saying "an AFTER trigger on a plain
table (not a foreign table)"? No need to mention views, so I removed
that.
I was actually going to suggest that, but I wasn't sure why you wanted
to mention "views" explicitly.
I also changed to singular because that sounds natural. My
first language is not English, though. Other than that the change
looks good to me.
+1.
--
Best Wishes,
Ashutosh Bapat
On Fri, Jul 25, 2025 at 7:59 PM Ashutosh Bapat
<ashutosh.bapat.oss@gmail.com> wrote:
On Fri, Jul 25, 2025 at 3:22 PM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
On Thu, Jul 24, 2025 at 12:06 AM Ashutosh Bapat
<ashutosh.bapat.oss@gmail.com> wrote:On Wed, Jul 23, 2025 at 4:16 PM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
On Tue, Jul 15, 2025 at 4:55 PM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
Another thing I noticed about transition tables is that while we
prohibit transition tables on views/foreign tables, there is no
description about that in the user-facing documentation. So I would
like to propose to do $SUBJECT in create_trigger.sgml. Attached is a
patch for that.I think the restriction should be specified in a manner similar to how
restriction on CONSTRAINT option for foreign tables is specified i.e.
in " This option is only allowed for an AFTER trigger that is not a
constraint trigger; also, if the trigger is an UPDATE trigger, it must
not specify a column_name list.". But that sentence is already a bit
complex because of ; also, ... part. How about splitting the sentence
into two and mentioning restriction like below?"This option is only allowed for an AFTER trigger on tables other than
views or foreign tables. The trigger should not be a constraint
trigger. If the trigger is an UPDATE trigger, it must not specify a
column_name list when using this option."Good idea! This might be nitpicking, but one thing I noticed is this
part of the first sentence: "an AFTER trigger on tables other than
views or foreign tables". Like the CONSTRAINT-restrictions
description above, how about just saying "an AFTER trigger on a plain
table (not a foreign table)"? No need to mention views, so I removed
that.I was actually going to suggest that, but I wasn't sure why you wanted
to mention "views" explicitly.I also changed to singular because that sounds natural. My
first language is not English, though. Other than that the change
looks good to me.+1.
Cool! I updated the patch as above. I will push this version.
Best regards,
Etsuro Fujita
Attachments:
Update-documentation-about-transition-tables-v2.patchapplication/octet-stream; name=Update-documentation-about-transition-tables-v2.patchDownload
diff --git a/doc/src/sgml/ref/create_trigger.sgml b/doc/src/sgml/ref/create_trigger.sgml
index ed6d206ae71..25a639a09c0 100644
--- a/doc/src/sgml/ref/create_trigger.sgml
+++ b/doc/src/sgml/ref/create_trigger.sgml
@@ -197,9 +197,11 @@ CREATE [ OR REPLACE ] [ CONSTRAINT ] TRIGGER <replaceable class="parameter">name
of the rows inserted, deleted, or modified by the current SQL statement.
This feature lets the trigger see a global view of what the statement did,
not just one row at a time. This option is only allowed for
- an <literal>AFTER</literal> trigger that is not a constraint trigger; also, if
- the trigger is an <literal>UPDATE</literal> trigger, it must not specify
- a <replaceable class="parameter">column_name</replaceable> list.
+ an <literal>AFTER</literal> trigger on a plain table (not a foreign table).
+ The trigger should not be a constraint trigger. Also, if the trigger is an
+ <literal>UPDATE</literal> trigger, it must not specify
+ a <replaceable class="parameter">column_name</replaceable> list when using
+ this option.
<literal>OLD TABLE</literal> may only be specified once, and only for a trigger
that can fire on <literal>UPDATE</literal> or <literal>DELETE</literal>; it creates a
transition relation containing the <firstterm>before-images</firstterm> of all rows
On Sat, Jul 26, 2025 at 4:59 PM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
On Fri, Jul 25, 2025 at 7:59 PM Ashutosh Bapat
<ashutosh.bapat.oss@gmail.com> wrote:On Fri, Jul 25, 2025 at 3:22 PM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
On Thu, Jul 24, 2025 at 12:06 AM Ashutosh Bapat
<ashutosh.bapat.oss@gmail.com> wrote:On Wed, Jul 23, 2025 at 4:16 PM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
On Tue, Jul 15, 2025 at 4:55 PM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
Another thing I noticed about transition tables is that while we
prohibit transition tables on views/foreign tables, there is no
description about that in the user-facing documentation. So I would
like to propose to do $SUBJECT in create_trigger.sgml. Attached is a
patch for that.I think the restriction should be specified in a manner similar to how
restriction on CONSTRAINT option for foreign tables is specified i.e.
in " This option is only allowed for an AFTER trigger that is not a
constraint trigger; also, if the trigger is an UPDATE trigger, it must
not specify a column_name list.". But that sentence is already a bit
complex because of ; also, ... part. How about splitting the sentence
into two and mentioning restriction like below?"This option is only allowed for an AFTER trigger on tables other than
views or foreign tables. The trigger should not be a constraint
trigger. If the trigger is an UPDATE trigger, it must not specify a
column_name list when using this option."Good idea! This might be nitpicking, but one thing I noticed is this
part of the first sentence: "an AFTER trigger on tables other than
views or foreign tables". Like the CONSTRAINT-restrictions
description above, how about just saying "an AFTER trigger on a plain
table (not a foreign table)"? No need to mention views, so I removed
that.I was actually going to suggest that, but I wasn't sure why you wanted
to mention "views" explicitly.I also changed to singular because that sounds natural. My
first language is not English, though. Other than that the change
looks good to me.+1.
Cool! I updated the patch as above. I will push this version.
LGTM.
--
Best Wishes,
Ashutosh Bapat
On Mon, Jul 28, 2025 at 2:14 PM Ashutosh Bapat
<ashutosh.bapat.oss@gmail.com> wrote:
On Sat, Jul 26, 2025 at 4:59 PM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
I updated the patch as above. I will push this version.
LGTM.
Pushed and back-patched.
Thanks!
Best regards,
Etsuro Fujita