public inbox for [email protected]
help / color / mirror / Atom feedFrom: jian he <[email protected]>
To: [email protected]
Cc: [email protected]
Cc: [email protected]
Cc: [email protected]
Subject: Re: CREATE TABLE LIKE INCLUDING TRIGGERS
Date: Wed, 8 Jul 2026 13:05:42 +0800
Message-ID: <CACJufxE6RsYTWPwMMaTwfHeVsWHzbfdj-uh-+L2jaciAuB28Wg@mail.gmail.com> (raw)
In-Reply-To: <CAAAe_zA=yA7Odc3vrfp5ywmAuWU6-t7tUS0re-zsDAvgj0Zazg@mail.gmail.com>
References: <CACJufxHJAr2FjbeB6ghg_-N5dxX5JVnjKSLOUxOyt4TeaAWQkg@mail.gmail.com>
<CACJufxHQ8tDG09mXW=8xfEK2_h+By1zLgrFd0EcshHp-TDGiGw@mail.gmail.com>
<CACJufxFu7Y4FhVkaKT2Kaj8ym2T5TcwN93cR_6h4x66iLrSZ-Q@mail.gmail.com>
<CACJufxHMYq2ZqvOhoSKUmx+wZUo=HixjfgGKDJ6L4cdCvwh2VA@mail.gmail.com>
<[email protected]>
<CACJufxH5YDMAQ_KXaPJaC4Zt1i2HNSNaYoskzNDRjSrE_UWjKw@mail.gmail.com>
<CAN4CZFM_8jL7Rf43zLa-9P2BwC+_H-oM9kYxVLGcwu83KC6faA@mail.gmail.com>
<CACJufxGGXnRP+Em521KvPVOmnPtya2zpPqrTuYDmrSY=AFVmfw@mail.gmail.com>
<CAN4CZFNPE_UeTEy5TH9wwc6mBAtX7vnBhLSL47jRiHXA=6yXjQ@mail.gmail.com>
<CACJufxGTxbpbaQjUxc69-Dovx6+KJd=81T5111uizUrdsEkDNQ@mail.gmail.com>
<CAN4CZFOdet_ZZEr+sZhTUiAd-fjOprhMXDhkjdhz48pEoJc9Pg@mail.gmail.com>
<CACJufxEKK44FtxiShTZtPtCehwm47k5wB+RobuRCUJRa_b0owg@mail.gmail.com>
<CACJufxFT-P-QZ-w5tVKOicWayUb1p407mWTDX5CGbf6XP2MGcA@mail.gmail.com>
<CACJufxGhZy9s1ZZhOOu95AMpynk5acVxFVqHqoYcbbscezrbMA@mail.gmail.com>
<CAAAe_zCcq2jGqNU7QMd0XjknG4r=+C4d5jK9nQJCL4Wi2W8L-Q@mail.gmail.com>
<CAAAe_zAHAjTVGE9fAuTZ9df7Y7n7Zctn8WtEkUfytAu+VKQE7w@mail.gmail.com>
<CACJufxFARVrYtOOYqPGfq0S0RtAkuLtQt26=g99fpaNGXM8-Tg@mail.gmail.com>
<CAAAe_zA=yA7Odc3vrfp5ywmAuWU6-t7tUS0re-zsDAvgj0Zazg@mail.gmail.com>
On Fri, Jul 3, 2026 at 2:28 PM Henson Choi <[email protected]> wrote:
>
> I've grouped them by how you might want to act on each.
>
> 1. Docs / typos (all cosmetic)
>
Typo fixed.
> - 0002: create_table.sgml's INCLUDING TRIGGERS entry doesn't mention that a
> trigger's enabled/disabled (tgenabled) state is preserved. Suggested,
> after the source-view sentence ("... are not copied."):
> "The enabled state of each trigger (as set by ALTER TABLE ...
> ENABLE/DISABLE TRIGGER) is preserved on the new table."
>
I changed it to:
+ The enabled state of each trigger (references <link
linkend="catalog-pg-trigger"><structname>pg_trigger</structname></link>.<structfield>tgenabled</structfield>)
+ is preserved on the new table.
> 2. Code -- two fixes to consider
>
> (a) Concurrent trigger add can fail CREATE TABLE via a duplicate (reproduced)
>
> expandTableLikeClause() walks the source trigger array (trigdesc) by index,
> calling generateClonedTriggerStmt(), whose catalog access can process an
> invalidation and rebuild the source trigdesc. The array is sorted by tgname,
> so if another session adds a lower-sorting trigger mid-walk, the indexes shift
> and an already-processed trigger gets cloned again.
>
> I reproduced it by putting a sleep in the loop: with t100/t101/t102 on the
> source, adding t000 from another session right after t100 was processed
> (CREATE TRIGGER is compatible with AccessShareLock, so it isn't blocked) made
> the next iteration see the rebuilt array [t000, t100, t101, t102], with nt=1
> pointing at t100 again -- so it tried to create t100 twice on the new table
> and the whole CREATE TABLE failed with "trigger ... already exists".
>
> The removal side (DROP/ALTER/RENAME TRIGGER) is blocked by AccessExclusiveLock
> against the source's AccessShareLock, so the array can't shrink and nothing is
> missed; the only thing that can run concurrently is CREATE TRIGGER (a
> compatible lock), leaving just this duplicate. It's probably a rare path, but
> it seems worth addressing.
>
In v11, I changed it to first collect the trigger OIDs to be copied to the
target relation, and then call generateClonedTriggerStmt for each trigger OID.
> (b) tgenabled channel unification (CreateTriggerFiringOn)
>
> 0002 adds a tgenabled field to CreateTrigStmt and sets it in several places,
> but the deferred-unique trigger in index.c and the FK triggers in
> tablecmds.c still call CreateTrigger(). CreateTrigger() hardcodes tgenabled
> to TRIGGER_FIRES_ON_ORIGIN internally, so the trigger->tgenabled just
> assigned is a dead store. It's harmless today since the value is always
> ORIGIN, but it's a field set and then ignored. Unifying those sites on
> CreateTriggerFiringOn(..., trigger->tgenabled) (or dropping the assignment)
> would consolidate the two channels.
>
CreateTrigger change to return
```
CreateTriggerFiringOn(stmt, queryString, relOid, refRelOid,
constraintOid, indexOid, funcoid,
parentTriggerOid, whenClause, isInternal,
in_partition, stmt->tgenabled);
```
should be fine.
I also combined two patches (v10-0001, v10-0002) into one (v11-0001).
--
jian
https://www.enterprisedb.com/
Attachments:
[application/x-patch] v11-0001-CREATE-TABLE-LIKE-INCLUDING-TRIGGERS.patch (50.5K, ../CACJufxE6RsYTWPwMMaTwfHeVsWHzbfdj-uh-+L2jaciAuB28Wg@mail.gmail.com/2-v11-0001-CREATE-TABLE-LIKE-INCLUDING-TRIGGERS.patch)
download | inline diff:
From e0757cfe468baefddc45d8d5032f52cf5c7b7de2 Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Wed, 8 Jul 2026 12:59:34 +0800
Subject: [PATCH v11 1/1] CREATE TABLE LIKE INCLUDING TRIGGERS
This will copy all source table's triggers to the new table. This functionality
also extends to CREATE FOREIGN TABLE LIKE statements.
If INCLUDING COMMENTS is specified, trigger comments will be copied to the new
table.
This command will fail if the source relation's triggers contain whole-row
reference.
Incompatible triggers will be skipped. Internal triggers (such as foreign key
associated triggers) won't being copied to new table. Triggers from a source
view will not be copied to a new table. Additionally, constraint triggers and
triggers with transition tables will not be copied to a new foreign table.
Author: jian he <[email protected]>
Reviewed-by: Andrey Borodin <[email protected]>
Reviewed-by: Zsolt Parragi <[email protected]>
Reviewed-by: Robert Haas <[email protected]>
Reviewed-by: Henson Choi <[email protected]>
Tested-by: lakshmi <[email protected]>
discussion: https://postgr.es/m/CACJufxHJAr2FjbeB6ghg_-N5dxX5JVnjKSLOUxOyt4TeaAWQkg@mail.gmail.com
commitfest: https://commitfest.postgresql.org/patch/6087
---
doc/src/sgml/ref/create_foreign_table.sgml | 19 +-
doc/src/sgml/ref/create_table.sgml | 17 +-
src/backend/catalog/index.c | 3 +
src/backend/commands/tablecmds.c | 12 +
src/backend/commands/trigger.c | 231 +++++++++++++++++-
src/backend/parser/gram.y | 11 +-
src/backend/parser/parse_utilcmd.c | 92 ++++++-
src/backend/tcop/utility.c | 12 +-
src/include/commands/trigger.h | 9 +
src/include/nodes/parsenodes.h | 5 +
src/include/parser/kwlist.h | 1 +
.../regress/expected/create_table_like.out | 27 ++
src/test/regress/expected/triggers.out | 129 ++++++++++
src/test/regress/sql/create_table_like.sql | 29 +++
src/test/regress/sql/triggers.sql | 70 ++++++
15 files changed, 649 insertions(+), 18 deletions(-)
diff --git a/doc/src/sgml/ref/create_foreign_table.sgml b/doc/src/sgml/ref/create_foreign_table.sgml
index 083f16772b7..29230f02531 100644
--- a/doc/src/sgml/ref/create_foreign_table.sgml
+++ b/doc/src/sgml/ref/create_foreign_table.sgml
@@ -60,7 +60,7 @@ CREATE FOREIGN TABLE [ IF NOT EXISTS ] <replaceable class="parameter">table_name
<phrase>and <replaceable class="parameter">like_option</replaceable> is:</phrase>
-{ INCLUDING | EXCLUDING } { COMMENTS | CONSTRAINTS | DEFAULTS | GENERATED | STATISTICS | ALL }
+{ INCLUDING | EXCLUDING } { COMMENTS | CONSTRAINTS | DEFAULTS | GENERATED | STATISTICS | TRIGGERS | ALL }
<phrase>and <replaceable class="parameter">partition_bound_spec</replaceable> is:</phrase>
@@ -232,7 +232,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<term><literal>INCLUDING COMMENTS</literal></term>
<listitem>
<para>
- Comments for the copied columns, constraints, and extended statistics
+ Comments for the copied columns, constraints, extended statistics, and triggers
will be copied. The default behavior is to exclude comments,
resulting in the corresponding objects in the new table having no
comments.
@@ -284,6 +284,21 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</listitem>
</varlistentry>
+ <varlistentry id="sql-createforeigntable-parms-like-opt-triggers">
+ <term><literal>INCLUDING TRIGGERS</literal></term>
+ <listitem>
+ <para>
+ All non-internal triggers are copied to the new table.
+ Because triggers on views differ significantly from those on tables, triggers from
+ source view are not be copied.
+ Furthermore constraint triggers and triggers that use transition tables
+ will not be copied because foreign tables do not support these.
+ The enabled state of each trigger (references <link linkend="catalog-pg-trigger"><structname>pg_trigger</structname></link>.<structfield>tgenabled</structfield>)
+ is preserved on the new table.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry id="sql-createforeigntable-parms-like-opt-all">
<term><literal>INCLUDING ALL</literal></term>
<listitem>
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index e342585c7f0..cd1f6a6a121 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -100,7 +100,7 @@ class="parameter">referential_action</replaceable> ] [ ON UPDATE <replaceable cl
<phrase>and <replaceable class="parameter">like_option</replaceable> is:</phrase>
-{ INCLUDING | EXCLUDING } { COMMENTS | COMPRESSION | CONSTRAINTS | DEFAULTS | GENERATED | IDENTITY | INDEXES | STATISTICS | STORAGE | ALL }
+{ INCLUDING | EXCLUDING } { COMMENTS | COMPRESSION | CONSTRAINTS | DEFAULTS | GENERATED | IDENTITY | INDEXES | STATISTICS | STORAGE | TRIGGERS | ALL }
<phrase>and <replaceable class="parameter">partition_bound_spec</replaceable> is:</phrase>
@@ -693,7 +693,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<listitem>
<para>
Comments for the copied columns, check constraints,
- not-null constraints, indexes, and extended statistics will be
+ not-null constraints, indexes, extended statistics, and triggers will be
copied. The default behavior is to exclude comments, resulting in
the corresponding objects in the new table having no
comments.
@@ -797,6 +797,19 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
</listitem>
</varlistentry>
+ <varlistentry id="sql-createtable-parms-like-opt-triggers">
+ <term><literal>INCLUDING TRIGGERS</literal></term>
+ <listitem>
+ <para>
+ All non-internal triggers are copied to the new table.
+ Because triggers on views differ significantly from those on tables, triggers from
+ source view are not copied.
+ The enabled state of each trigger (references <link linkend="catalog-pg-trigger"><structname>pg_trigger</structname></link>.<structfield>tgenabled</structfield>)
+ is preserved on the new table.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry id="sql-createtable-parms-like-opt-all">
<term><literal>INCLUDING ALL</literal></term>
<listitem>
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index 81bba4beac7..f1468b92958 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -2041,6 +2041,7 @@ index_constraint_create(Relation heapRelation,
CreateTrigStmt *trigger = makeNode(CreateTrigStmt);
trigger->replace = false;
+ trigger->tgenabled = TRIGGER_FIRES_ON_ORIGIN;
trigger->isconstraint = true;
trigger->trigname = (constraintType == CONSTRAINT_PRIMARY) ?
"PK_ConstraintTrigger" :
@@ -2057,6 +2058,8 @@ index_constraint_create(Relation heapRelation,
trigger->deferrable = true;
trigger->initdeferred = initdeferred;
trigger->constrrel = NULL;
+ trigger->trigcomment = NULL;
+ trigger->transformed = true;
(void) CreateTrigger(trigger, NULL, RelationGetRelid(heapRelation),
InvalidOid, conOid, indexRelationId, InvalidOid,
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 95abaf4890c..e15140114c7 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -14338,6 +14338,7 @@ CreateFKCheckTrigger(Oid myRelOid, Oid refRelOid, Constraint *fkconstraint,
*/
fk_trigger = makeNode(CreateTrigStmt);
fk_trigger->replace = false;
+ fk_trigger->tgenabled = TRIGGER_FIRES_ON_ORIGIN;
fk_trigger->isconstraint = true;
fk_trigger->trigname = "RI_ConstraintTrigger_c";
fk_trigger->relation = NULL;
@@ -14363,6 +14364,8 @@ CreateFKCheckTrigger(Oid myRelOid, Oid refRelOid, Constraint *fkconstraint,
fk_trigger->deferrable = fkconstraint->deferrable;
fk_trigger->initdeferred = fkconstraint->initdeferred;
fk_trigger->constrrel = NULL;
+ fk_trigger->trigcomment = NULL;
+ fk_trigger->transformed = true;
trigAddress = CreateTrigger(fk_trigger, NULL, myRelOid, refRelOid,
constraintOid, indexOid, InvalidOid,
@@ -14397,6 +14400,7 @@ createForeignKeyActionTriggers(Oid myRelOid, Oid refRelOid, Constraint *fkconstr
*/
fk_trigger = makeNode(CreateTrigStmt);
fk_trigger->replace = false;
+ fk_trigger->tgenabled = TRIGGER_FIRES_ON_ORIGIN;
fk_trigger->isconstraint = true;
fk_trigger->trigname = "RI_ConstraintTrigger_a";
fk_trigger->relation = NULL;
@@ -14408,6 +14412,8 @@ createForeignKeyActionTriggers(Oid myRelOid, Oid refRelOid, Constraint *fkconstr
fk_trigger->whenClause = NULL;
fk_trigger->transitionRels = NIL;
fk_trigger->constrrel = NULL;
+ fk_trigger->trigcomment = NULL;
+ fk_trigger->transformed = true;
switch (fkconstraint->fk_del_action)
{
@@ -14457,6 +14463,7 @@ createForeignKeyActionTriggers(Oid myRelOid, Oid refRelOid, Constraint *fkconstr
*/
fk_trigger = makeNode(CreateTrigStmt);
fk_trigger->replace = false;
+ fk_trigger->tgenabled = TRIGGER_FIRES_ON_ORIGIN;
fk_trigger->isconstraint = true;
fk_trigger->trigname = "RI_ConstraintTrigger_a";
fk_trigger->relation = NULL;
@@ -14468,6 +14475,8 @@ createForeignKeyActionTriggers(Oid myRelOid, Oid refRelOid, Constraint *fkconstr
fk_trigger->whenClause = NULL;
fk_trigger->transitionRels = NIL;
fk_trigger->constrrel = NULL;
+ fk_trigger->trigcomment = NULL;
+ fk_trigger->transformed = true;
switch (fkconstraint->fk_upd_action)
{
@@ -21471,6 +21480,7 @@ CloneRowTriggersToPartition(Relation parent, Relation partition)
trigStmt = makeNode(CreateTrigStmt);
trigStmt->replace = false;
+ trigStmt->tgenabled = trigForm->tgenabled;
trigStmt->isconstraint = OidIsValid(trigForm->tgconstraint);
trigStmt->trigname = NameStr(trigForm->tgname);
trigStmt->relation = NULL;
@@ -21485,6 +21495,8 @@ CloneRowTriggersToPartition(Relation parent, Relation partition)
trigStmt->deferrable = trigForm->tgdeferrable;
trigStmt->initdeferred = trigForm->tginitdeferred;
trigStmt->constrrel = NULL; /* passed separately */
+ trigStmt->trigcomment = NULL;
+ trigStmt->transformed = true;
CreateTriggerFiringOn(trigStmt, NULL, RelationGetRelid(partition),
trigForm->tgconstrrelid, InvalidOid, InvalidOid,
diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c
index 401baddbfc6..ca1abe1c153 100644
--- a/src/backend/commands/trigger.c
+++ b/src/backend/commands/trigger.c
@@ -31,6 +31,7 @@
#include "catalog/pg_proc.h"
#include "catalog/pg_trigger.h"
#include "catalog/pg_type.h"
+#include "commands/comment.h"
#include "commands/trigger.h"
#include "executor/executor.h"
#include "executor/instrument.h"
@@ -168,7 +169,7 @@ CreateTrigger(const CreateTrigStmt *stmt, const char *queryString,
CreateTriggerFiringOn(stmt, queryString, relOid, refRelOid,
constraintOid, indexOid, funcoid,
parentTriggerOid, whenClause, isInternal,
- in_partition, TRIGGER_FIRES_ON_ORIGIN);
+ in_partition, stmt->tgenabled);
}
/*
@@ -603,13 +604,19 @@ CreateTriggerFiringOn(const CreateTrigStmt *stmt, const char *queryString,
false, false);
addNSItemToQuery(pstate, nsitem, false, true, true);
- /* Transform expression. Copy to be sure we don't modify original */
- whenClause = transformWhereClause(pstate,
- copyObject(stmt->whenClause),
- EXPR_KIND_TRIGGER_WHEN,
- "WHEN");
- /* we have to fix its collations too */
- assign_expr_collations(pstate, whenClause);
+ if (stmt->transformed)
+ whenClause = stmt->whenClause;
+ else
+ {
+ /* Transform expression. Copy to be sure we don't modify original */
+ whenClause = transformWhereClause(pstate,
+ copyObject(stmt->whenClause),
+ EXPR_KIND_TRIGGER_WHEN,
+ "WHEN");
+
+ /* we have to fix its collations too */
+ assign_expr_collations(pstate, whenClause);
+ }
/*
* Check for disallowed references to OLD/NEW.
@@ -1218,6 +1225,11 @@ CreateTriggerFiringOn(const CreateTrigStmt *stmt, const char *queryString,
/* Keep lock on target rel until end of xact */
table_close(rel, NoLock);
+ /* Add any requested comment */
+ if (stmt->trigcomment != NULL)
+ CreateComments(trigoid, TriggerRelationId, 0,
+ stmt->trigcomment);
+
return myself;
}
@@ -6929,3 +6941,206 @@ AfterTriggerIsActive(void)
{
return afterTriggers.firing_depth > 0;
}
+
+/*
+ * Duplicate the trigger definition for the new relation: Use the source trigger
+ * (source_trigid from source_rel) to initialize a CreateTrigStmt for the target
+ * relation (heapRel).
+ *
+ * Attribute numbers in expression Vars are adjusted according to attmap.
+ */
+CreateTrigStmt *
+generateClonedTriggerStmt(RangeVar *heapRel, Oid source_trigid,
+ Relation source_rel, const AttrMap *attmap)
+{
+ HeapTuple triggerTuple;
+ HeapTuple proctup;
+ Form_pg_trigger trigForm;
+ Form_pg_proc procform;
+ Relation pg_trigger;
+ RangeVar *constrrel = NULL;
+ SysScanDesc tgscan;
+ ScanKeyData skey[1];
+ Datum value;
+ bool isnull;
+ Node *qual = NULL;
+ List *trigargs = NIL;
+ List *cols = NIL;
+ List *funcname = NIL;
+ List *transitionRels = NIL;
+ char *funcschema;
+ CreateTrigStmt *trigStmt;
+
+ pg_trigger = table_open(TriggerRelationId, AccessShareLock);
+
+ /* Find the trigger to copy */
+ ScanKeyInit(&skey[0],
+ Anum_pg_trigger_oid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(source_trigid));
+
+ tgscan = systable_beginscan(pg_trigger, TriggerOidIndexId, true,
+ NULL, 1, skey);
+
+ triggerTuple = systable_getnext(tgscan);
+ if (!HeapTupleIsValid(triggerTuple))
+ elog(ERROR, "could not find tuple for trigger %u", source_trigid);
+
+ trigForm = (Form_pg_trigger) GETSTRUCT(triggerTuple);
+
+ /* Reconstruct trigger function String list */
+ proctup = SearchSysCache1(PROCOID, ObjectIdGetDatum(trigForm->tgfoid));
+ if (!HeapTupleIsValid(proctup))
+ elog(ERROR, "cache lookup failed for function %u", trigForm->tgfoid);
+ procform = (Form_pg_proc) GETSTRUCT(proctup);
+
+ funcschema = get_namespace_name(procform->pronamespace);
+ funcname = list_make2(makeString(funcschema),
+ makeString(pstrdup(NameStr(procform->proname))));
+ ReleaseSysCache(proctup);
+
+ /*
+ * If there is a column list, transform it to a list of column names. Note
+ * we don't need to map this list in any way ...
+ */
+ if (trigForm->tgattr.dim1 > 0)
+ {
+ for (int i = 0; i < trigForm->tgattr.dim1; i++)
+ {
+ Form_pg_attribute col;
+
+ col = TupleDescAttr(RelationGetDescr(source_rel),
+ trigForm->tgattr.values[i] - 1);
+ cols = lappend(cols,
+ makeString(pstrdup(NameStr(col->attname))));
+ }
+ }
+
+ /* Reconstruct trigger arguments list */
+ if (trigForm->tgnargs > 0)
+ {
+ bytea *val;
+ char *p;
+
+ val = DatumGetByteaPP(fastgetattr(triggerTuple,
+ Anum_pg_trigger_tgargs,
+ RelationGetDescr(pg_trigger),
+ &isnull));
+ if (isnull)
+ elog(ERROR, "tgargs is null in trigger \"%s\" for relation \"%s\"",
+ NameStr(trigForm->tgname),
+ RelationGetRelationName(source_rel));
+
+ p = (char *) VARDATA_ANY(val);
+
+ for (int i = 0; i < trigForm->tgnargs; i++)
+ {
+ trigargs = lappend(trigargs, makeString(pstrdup(p)));
+ p += strlen(p) + 1;
+ }
+ }
+
+ /* If the trigger has a WHEN qualification, add that */
+ value = fastgetattr(triggerTuple, Anum_pg_trigger_tgqual,
+ RelationGetDescr(pg_trigger), &isnull);
+ if (!isnull)
+ {
+ bool found_whole_row;
+
+ qual = stringToNode(TextDatumGetCString(value));
+
+ /* Adjust Vars to match new table's column numbering */
+ qual = map_variable_attnos(qual, PRS2_NEW_VARNO, 0,
+ attmap,
+ InvalidOid,
+ &found_whole_row);
+
+ /* As in expandTableLikeClause, reject whole-row variables */
+ if (found_whole_row)
+ ereport(ERROR,
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot convert whole-row table reference"),
+ errdetail("Trigger \"%s\" contains a whole-row table reference.",
+ NameStr(trigForm->tgname)));
+
+ qual = map_variable_attnos(qual, PRS2_OLD_VARNO, 0,
+ attmap,
+ InvalidOid,
+ &found_whole_row);
+
+ /* As in expandTableLikeClause, reject whole-row variables */
+ if (found_whole_row)
+ ereport(ERROR,
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot convert whole-row table reference"),
+ errdetail("Trigger \"%s\" contains a whole-row table reference.",
+ NameStr(trigForm->tgname)));
+ }
+
+ /* Reconstruct trigger old transition table */
+ value = fastgetattr(triggerTuple, Anum_pg_trigger_tgoldtable,
+ RelationGetDescr(pg_trigger), &isnull);
+ if (!isnull)
+ {
+ TriggerTransition *old = makeNode(TriggerTransition);
+
+ old->isNew = false;
+ old->name = pstrdup(NameStr(*DatumGetName(value)));
+ old->isTable = true;
+ transitionRels = lappend(transitionRels, old);
+ }
+
+ /* Reconstruct trigger new transition table */
+ value = fastgetattr(triggerTuple, Anum_pg_trigger_tgnewtable,
+ RelationGetDescr(pg_trigger),
+ &isnull);
+ if (!isnull)
+ {
+ TriggerTransition *new = makeNode(TriggerTransition);
+
+ new->isNew = true;
+ new->name = pstrdup(NameStr(*DatumGetName(value)));
+ new->isTable = true;
+ transitionRels = lappend(transitionRels, new);
+ }
+
+ /* Reconstruct trigger constraint's FROM table */
+ if (OidIsValid(trigForm->tgconstrrelid))
+ {
+ /*
+ * Acquire the AccessShareLock lock on tgconstrrelid now, as it will
+ * be required later in CreateTriggerFiringOn.
+ */
+ LockRelationOid(trigForm->tgconstrrelid, AccessShareLock);
+
+ constrrel =
+ makeRangeVar(get_namespace_name(get_rel_namespace(trigForm->tgconstrrelid)),
+ get_rel_name(trigForm->tgconstrrelid),
+ -1);
+ }
+
+ trigStmt = makeNode(CreateTrigStmt);
+ trigStmt->replace = false;
+ trigStmt->tgenabled = trigForm->tgenabled;
+ trigStmt->isconstraint = OidIsValid(trigForm->tgconstraint);
+ trigStmt->trigname = pstrdup(NameStr(trigForm->tgname));
+ trigStmt->relation = heapRel;
+ trigStmt->funcname = funcname;
+ trigStmt->args = trigargs;
+ trigStmt->row = TRIGGER_FOR_ROW(trigForm->tgtype);
+ trigStmt->timing = trigForm->tgtype & TRIGGER_TYPE_TIMING_MASK;
+ trigStmt->events = trigForm->tgtype & TRIGGER_TYPE_EVENT_MASK;
+ trigStmt->columns = cols;
+ trigStmt->whenClause = qual;
+ trigStmt->transitionRels = transitionRels;
+ trigStmt->deferrable = trigForm->tgdeferrable;
+ trigStmt->initdeferred = trigForm->tginitdeferred;
+ trigStmt->constrrel = constrrel;
+ trigStmt->trigcomment = NULL;
+ trigStmt->transformed = true;
+
+ systable_endscan(tgscan);
+ table_close(pg_trigger, AccessShareLock);
+
+ return trigStmt;
+}
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index ff4e1388c55..e0dfce69259 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -822,7 +822,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
TABLE TABLES TABLESAMPLE TABLESPACE TARGET TEMP TEMPLATE TEMPORARY TEXT_P THEN
TIES TIME TIMESTAMP TO TRAILING TRANSACTION TRANSFORM
- TREAT TRIGGER TRIM TRUE_P
+ TREAT TRIGGER TRIGGERS TRIM TRUE_P
TRUNCATE TRUSTED TYPE_P TYPES_P
UESCAPE UNBOUNDED UNCONDITIONAL UNCOMMITTED UNENCRYPTED UNION UNIQUE UNKNOWN
@@ -4353,6 +4353,7 @@ TableLikeOption:
| INDEXES { $$ = CREATE_TABLE_LIKE_INDEXES; }
| STATISTICS { $$ = CREATE_TABLE_LIKE_STATISTICS; }
| STORAGE { $$ = CREATE_TABLE_LIKE_STORAGE; }
+ | TRIGGERS { $$ = CREATE_TABLE_LIKE_TRIGGERS; }
| ALL { $$ = CREATE_TABLE_LIKE_ALL; }
;
@@ -6195,6 +6196,7 @@ CreateTrigStmt:
CreateTrigStmt *n = makeNode(CreateTrigStmt);
n->replace = $2;
+ n->tgenabled = TRIGGER_FIRES_ON_ORIGIN;
n->isconstraint = false;
n->trigname = $4;
n->relation = $8;
@@ -6209,6 +6211,8 @@ CreateTrigStmt:
n->deferrable = false;
n->initdeferred = false;
n->constrrel = NULL;
+ n->trigcomment = NULL;
+ n->transformed = false;
$$ = (Node *) n;
}
| CREATE opt_or_replace CONSTRAINT TRIGGER name AFTER TriggerEvents ON
@@ -6244,6 +6248,7 @@ CreateTrigStmt:
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("CREATE OR REPLACE CONSTRAINT TRIGGER is not supported"),
parser_errposition(@1)));
+ n->tgenabled = TRIGGER_FIRES_ON_ORIGIN;
n->isconstraint = true;
n->trigname = $5;
n->relation = $9;
@@ -6259,6 +6264,8 @@ CreateTrigStmt:
&n->deferrable, &n->initdeferred, &dummy,
NULL, NULL, yyscanner);
n->constrrel = $10;
+ n->trigcomment = NULL;
+ n->transformed = false;
$$ = (Node *) n;
}
;
@@ -19130,6 +19137,7 @@ unreserved_keyword:
| TRANSACTION
| TRANSFORM
| TRIGGER
+ | TRIGGERS
| TRUNCATE
| TRUSTED
| TYPE_P
@@ -19795,6 +19803,7 @@ bare_label_keyword:
| TRANSFORM
| TREAT
| TRIGGER
+ | TRIGGERS
| TRIM
| TRUE_P
| TRUNCATE
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index 58ccc7b68f2..4cd554b8f49 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -40,12 +40,14 @@
#include "catalog/pg_opclass.h"
#include "catalog/pg_operator.h"
#include "catalog/pg_statistic_ext.h"
+#include "catalog/pg_trigger.h"
#include "catalog/pg_type.h"
#include "commands/comment.h"
#include "commands/defrem.h"
#include "commands/sequence.h"
#include "commands/tablecmds.h"
#include "commands/tablespace.h"
+#include "commands/trigger.h"
#include "miscadmin.h"
#include "nodes/makefuncs.h"
#include "nodes/nodeFuncs.h"
@@ -1316,7 +1318,8 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla
CREATE_TABLE_LIKE_GENERATED |
CREATE_TABLE_LIKE_CONSTRAINTS |
CREATE_TABLE_LIKE_INDEXES |
- CREATE_TABLE_LIKE_STATISTICS))
+ CREATE_TABLE_LIKE_STATISTICS |
+ CREATE_TABLE_LIKE_TRIGGERS))
{
table_like_clause->relationOid = RelationGetRelid(relation);
cxt->likeclauses = lappend(cxt->likeclauses, table_like_clause);
@@ -1620,6 +1623,93 @@ expandTableLikeClause(RangeVar *heapRel, TableLikeClause *table_like_clause)
list_free(parent_extstats);
}
+ /* Process triggers if required */
+ if ((table_like_clause->options & CREATE_TABLE_LIKE_TRIGGERS) &&
+ (relation->trigdesc != NULL) &&
+ (relation->rd_rel->relkind == RELKIND_RELATION ||
+ relation->rd_rel->relkind == RELKIND_PARTITIONED_TABLE ||
+ relation->rd_rel->relkind == RELKIND_FOREIGN_TABLE))
+ {
+ bool include_comments;
+ List *cloned_trigs = NIL;
+
+ include_comments = (table_like_clause->options & CREATE_TABLE_LIKE_COMMENTS);
+
+ /*
+ * generateClonedTriggerStmt() below does catalog accesses (e.g. a
+ * syscache lookup on the trigger's function), and any such access can
+ * cause AcceptInvalidationMessages() to run, which can rebuild
+ * relation->trigdesc from scratch if a concurrent session has changed
+ * the trigger set in the meantime. CREATE TRIGGER only takes
+ * ShareRowExclusiveLock on the relation, which is not conflict with
+ * the AccessShareLock we're holding here, so this can really happen.
+ * relation->trigdesc->triggers is also kept sorted by trigger name,
+ * so a concurrently-added trigger can shuffle the array positions of
+ * relation->trigdesc->triggers we've already visited.
+ *
+ * Because of that we need first collect the triggers OIDs to be
+ * cloned, and only then clone each one.
+ *
+ * A trigger created by another session after we've taken this
+ * snapshot will simply not be copied to the new table, which is fine.
+ */
+ if (childrel->rd_rel->relkind == RELKIND_FOREIGN_TABLE)
+ {
+ for (int nt = 0; nt < relation->trigdesc->numtriggers; nt++)
+ {
+ Trigger *trig = relation->trigdesc->triggers + nt;
+
+ /* We do not copy internal triggers to the new table */
+ if (trig->tgisinternal)
+ continue;
+
+ /*
+ * Foreign table cannot have constraint triggers or triggers
+ * with transition table
+ */
+ if (OidIsValid(trig->tgconstraint))
+ continue;
+
+ if (trig->tgoldtable != NULL || trig->tgnewtable != NULL)
+ continue;
+
+ cloned_trigs = lappend_oid(cloned_trigs, trig->tgoid);
+ }
+ }
+ else
+ {
+ for (int nt = 0; nt < relation->trigdesc->numtriggers; nt++)
+ {
+ Trigger *trig = relation->trigdesc->triggers + nt;
+
+ /* We do not copy internal triggers to the new table */
+ if (trig->tgisinternal)
+ continue;
+
+ cloned_trigs = lappend_oid(cloned_trigs, trig->tgoid);
+ }
+ }
+
+ foreach_oid(trigoid, cloned_trigs)
+ {
+ CreateTrigStmt *trig_stmt;
+
+ trig_stmt = generateClonedTriggerStmt(heapRel, trigoid,
+ relation, attmap);
+
+ /* Copy comment on trigger, if requested */
+ if (include_comments)
+ {
+ comment = GetComment(trigoid, TriggerRelationId, 0);
+
+ /* We make use of CreateTrigStmt's trigcomment option */
+ trig_stmt->trigcomment = comment;
+ }
+
+ result = lappend(result, trig_stmt);
+ }
+ }
+
/* Done with child rel */
table_close(childrel, NoLock);
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index 73a56f1df1d..f708df57ee3 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -1705,10 +1705,14 @@ ProcessUtilitySlow(ParseState *pstate,
break;
case T_CreateTrigStmt:
- address = CreateTrigger((CreateTrigStmt *) parsetree,
- queryString, InvalidOid, InvalidOid,
- InvalidOid, InvalidOid, InvalidOid,
- InvalidOid, NULL, false, false);
+ {
+ CreateTrigStmt *stmt = (CreateTrigStmt *) parsetree;
+
+ address = CreateTriggerFiringOn(stmt, queryString, InvalidOid, InvalidOid,
+ InvalidOid, InvalidOid, InvalidOid,
+ InvalidOid, NULL, false,
+ false, stmt->tgenabled);
+ }
break;
case T_CreatePLangStmt:
diff --git a/src/include/commands/trigger.h b/src/include/commands/trigger.h
index 0c3d485abf4..655bd54522c 100644
--- a/src/include/commands/trigger.h
+++ b/src/include/commands/trigger.h
@@ -18,6 +18,11 @@
#include "nodes/execnodes.h"
#include "nodes/parsenodes.h"
+/*
+ * forward references in this file
+ */
+typedef struct AttrMap AttrMap;
+
/*
* TriggerData is the node type that is passed as fmgr "context" info
* when a function is called by the trigger manager.
@@ -268,6 +273,10 @@ extern void AfterTriggerBeginSubXact(void);
extern void AfterTriggerEndSubXact(bool isCommit);
extern void AfterTriggerSetState(ConstraintsSetStmt *stmt);
extern bool AfterTriggerPendingOnRel(Oid relid);
+extern CreateTrigStmt *generateClonedTriggerStmt(RangeVar *heapRel,
+ Oid source_trigid,
+ Relation source_rel,
+ const AttrMap *attmap);
/*
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index e03556399ab..d3fd89aa555 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -813,6 +813,7 @@ typedef enum TableLikeOption
CREATE_TABLE_LIKE_INDEXES = 1 << 6,
CREATE_TABLE_LIKE_STATISTICS = 1 << 7,
CREATE_TABLE_LIKE_STORAGE = 1 << 8,
+ CREATE_TABLE_LIKE_TRIGGERS = 1 << 9,
CREATE_TABLE_LIKE_ALL = PG_INT32_MAX
} TableLikeOption;
@@ -3214,6 +3215,7 @@ typedef struct CreateTrigStmt
{
NodeTag type;
bool replace; /* replace trigger if already exists */
+ char tgenabled; /* trigger's firing configuration */
bool isconstraint; /* This is a constraint trigger */
char *trigname; /* TRIGGER's name */
RangeVar *relation; /* relation trigger is on */
@@ -3232,6 +3234,9 @@ typedef struct CreateTrigStmt
bool deferrable; /* [NOT] DEFERRABLE */
bool initdeferred; /* INITIALLY {DEFERRED|IMMEDIATE} */
RangeVar *constrrel; /* opposite relation, if RI trigger */
+ char *trigcomment; /* comment to apply to trigger, or NULL */
+ bool transformed; /* true means no need to do parse analysis for
+ * whenClause */
} CreateTrigStmt;
/* ----------------------
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h
index 51ead54f015..9733d4a796c 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -474,6 +474,7 @@ PG_KEYWORD("transaction", TRANSACTION, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("transform", TRANSFORM, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("treat", TREAT, COL_NAME_KEYWORD, BARE_LABEL)
PG_KEYWORD("trigger", TRIGGER, UNRESERVED_KEYWORD, BARE_LABEL)
+PG_KEYWORD("triggers", TRIGGERS, UNRESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("trim", TRIM, COL_NAME_KEYWORD, BARE_LABEL)
PG_KEYWORD("true", TRUE_P, RESERVED_KEYWORD, BARE_LABEL)
PG_KEYWORD("truncate", TRUNCATE, UNRESERVED_KEYWORD, BARE_LABEL)
diff --git a/src/test/regress/expected/create_table_like.out b/src/test/regress/expected/create_table_like.out
index a23735b5fb4..5bb188aacea 100644
--- a/src/test/regress/expected/create_table_like.out
+++ b/src/test/regress/expected/create_table_like.out
@@ -635,6 +635,28 @@ Not-null constraints:
"ctl_table_a_not_null" NOT NULL "a"
"ctl_table_d_not_null" NOT NULL "d"
+CREATE FUNCTION trigger_func1() RETURNS trigger LANGUAGE plpgsql AS '
+BEGIN
+ RAISE NOTICE ''trigger_func1(%) called: action = %, when = %, level = %'', TG_ARGV[0], TG_OP, TG_WHEN, TG_LEVEL;
+ RETURN NULL;
+END;';
+CREATE TRIGGER trigtest_before_stmt BEFORE DELETE OR UPDATE ON ctl_table
+FOR EACH ROW WHEN (OLD.a > 0)
+EXECUTE PROCEDURE trigger_func1('trigtest_before_stmt');
+CREATE TRIGGER trigtest_after_stmt AFTER UPDATE OF a, b ON ctl_table
+FOR EACH STATEMENT
+EXECUTE PROCEDURE trigger_func1('trigtest_after_stmt');
+ALTER TABLE ctl_table ENABLE REPLICA TRIGGER trigtest_before_stmt;
+ALTER TABLE ctl_table ENABLE ALWAYS TRIGGER trigtest_after_stmt;
+-- CREATE FOREIGN TABLE LIKE should not copy triggers that have transition tables
+CREATE TRIGGER trigtest_after_stmt1 AFTER INSERT ON ctl_table
+REFERENCING NEW TABLE AS new_table
+FOR EACH STATEMENT
+EXECUTE PROCEDURE trigger_func1('trigtest_after_stmt1');
+-- CREATE FOREIGN TABLE LIKE should not copy source table constraint trigger
+CREATE CONSTRAINT TRIGGER constr_trig
+AFTER INSERT ON ctl_table DEFERRABLE INITIALLY DEFERRED enforced FOR EACH ROW
+EXECUTE PROCEDURE trigger_func1();
-- Test EXCLUDING ALL
CREATE FOREIGN TABLE ctl_foreign_table1(LIKE ctl_table EXCLUDING ALL) SERVER ctl_s0;
\d+ ctl_foreign_table1
@@ -683,6 +705,10 @@ Statistics objects:
Not-null constraints:
"ctl_table_a_not_null" NOT NULL "a"
"ctl_table_d_not_null" NOT NULL "d"
+Triggers firing always:
+ trigtest_after_stmt AFTER UPDATE OF a, b ON ctl_foreign_table2 FOR EACH STATEMENT EXECUTE FUNCTION trigger_func1('trigtest_after_stmt')
+Triggers firing on replica only:
+ trigtest_before_stmt BEFORE DELETE OR UPDATE ON ctl_foreign_table2 FOR EACH ROW WHEN (old.a > 0) EXECUTE FUNCTION trigger_func1('trigtest_before_stmt')
Server: ctl_s0
-- \d+ does not report the value of attcompression for a foreign table, so
@@ -734,3 +760,4 @@ DROP FOREIGN TABLE ctl_foreign_table1;
DROP FOREIGN TABLE ctl_foreign_table2;
DROP FOREIGN DATA WRAPPER ctl_dummy CASCADE;
NOTICE: drop cascades to server ctl_s0
+DROP FUNCTION trigger_func1;
diff --git a/src/test/regress/expected/triggers.out b/src/test/regress/expected/triggers.out
index 8fcb33ac81a..953c547cf60 100644
--- a/src/test/regress/expected/triggers.out
+++ b/src/test/regress/expected/triggers.out
@@ -174,6 +174,20 @@ CREATE TRIGGER before_ins_stmt_trig BEFORE INSERT ON main_table
FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func('before_ins_stmt');
CREATE TRIGGER after_ins_stmt_trig AFTER INSERT ON main_table
FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func('after_ins_stmt');
+CREATE TRIGGER wholetrig BEFORE UPDATE ON main_table FOR EACH ROW
+WHEN (OLD IS NOT NULL)
+EXECUTE PROCEDURE trigger_func('modified_a');
+CREATE TABLE main_table1(LIKE main_table INCLUDING TRIGGERS); -- error, wholerow reference not allowed
+ERROR: cannot convert whole-row table reference
+DETAIL: Trigger "wholetrig" contains a whole-row table reference.
+DROP TRIGGER wholetrig ON main_table;
+CREATE TRIGGER wholetrig BEFORE UPDATE ON main_table FOR EACH ROW
+WHEN (NEW IS NOT NULL)
+EXECUTE PROCEDURE trigger_func('modified_a');
+CREATE TABLE main_table1(LIKE main_table INCLUDING TRIGGERS); -- error, wholerow reference not allowed
+ERROR: cannot convert whole-row table reference
+DETAIL: Trigger "wholetrig" contains a whole-row table reference.
+DROP TRIGGER wholetrig ON main_table;
--
-- if neither 'FOR EACH ROW' nor 'FOR EACH STATEMENT' was specified,
-- CREATE TRIGGER should default to 'FOR EACH STATEMENT'
@@ -394,6 +408,46 @@ NOTICE: trigger_func(after_upd_a_b_row) called: action = UPDATE, when = AFTER,
NOTICE: trigger_func(after_upd_b_row) called: action = UPDATE, when = AFTER, level = ROW
NOTICE: trigger_func(after_upd_b_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT
NOTICE: trigger_func(after_upd_stmt) called: action = UPDATE, when = AFTER, level = STATEMENT
+-- CREATE TABLE LIKE INCLUDING TRIGGERS tests
+COMMENT ON TRIGGER before_ins_stmt_trig ON main_table IS 'trigger before_ins_stmt_trig';
+CREATE TABLE main_table1(c int, LIKE main_table INCLUDING TRIGGERS INCLUDING COMMENTS);
+CREATE TABLE main_table2(c int, LIKE main_table EXCLUDING TRIGGERS INCLUDING COMMENTS);
+SELECT pg_get_triggerdef(oid)
+FROM pg_trigger
+WHERE NOT tgisinternal AND tgrelid IN ('main_table'::regclass, 'main_table1'::regclass, 'main_table2'::regclass)
+ORDER BY tgname, tgrelid::regclass::text COLLATE "C";
+ pg_get_triggerdef
+------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE TRIGGER after_ins_stmt_trig AFTER INSERT ON public.main_table FOR EACH STATEMENT EXECUTE FUNCTION trigger_func('after_ins_stmt')
+ CREATE TRIGGER after_ins_stmt_trig AFTER INSERT ON public.main_table1 FOR EACH STATEMENT EXECUTE FUNCTION trigger_func('after_ins_stmt')
+ CREATE TRIGGER after_upd_a_b_row_trig AFTER UPDATE OF a, b ON public.main_table FOR EACH ROW EXECUTE FUNCTION trigger_func('after_upd_a_b_row')
+ CREATE TRIGGER after_upd_a_b_row_trig AFTER UPDATE OF a, b ON public.main_table1 FOR EACH ROW EXECUTE FUNCTION trigger_func('after_upd_a_b_row')
+ CREATE TRIGGER after_upd_b_row_trig AFTER UPDATE OF b ON public.main_table FOR EACH ROW EXECUTE FUNCTION trigger_func('after_upd_b_row')
+ CREATE TRIGGER after_upd_b_row_trig AFTER UPDATE OF b ON public.main_table1 FOR EACH ROW EXECUTE FUNCTION trigger_func('after_upd_b_row')
+ CREATE TRIGGER after_upd_b_stmt_trig AFTER UPDATE OF b ON public.main_table FOR EACH STATEMENT EXECUTE FUNCTION trigger_func('after_upd_b_stmt')
+ CREATE TRIGGER after_upd_b_stmt_trig AFTER UPDATE OF b ON public.main_table1 FOR EACH STATEMENT EXECUTE FUNCTION trigger_func('after_upd_b_stmt')
+ CREATE TRIGGER after_upd_stmt_trig AFTER UPDATE ON public.main_table FOR EACH STATEMENT EXECUTE FUNCTION trigger_func('after_upd_stmt')
+ CREATE TRIGGER after_upd_stmt_trig AFTER UPDATE ON public.main_table1 FOR EACH STATEMENT EXECUTE FUNCTION trigger_func('after_upd_stmt')
+ CREATE TRIGGER before_ins_stmt_trig BEFORE INSERT ON public.main_table FOR EACH STATEMENT EXECUTE FUNCTION trigger_func('before_ins_stmt')
+ CREATE TRIGGER before_ins_stmt_trig BEFORE INSERT ON public.main_table1 FOR EACH STATEMENT EXECUTE FUNCTION trigger_func('before_ins_stmt')
+ CREATE TRIGGER before_upd_a_row_trig BEFORE UPDATE OF a ON public.main_table FOR EACH ROW EXECUTE FUNCTION trigger_func('before_upd_a_row')
+ CREATE TRIGGER before_upd_a_row_trig BEFORE UPDATE OF a ON public.main_table1 FOR EACH ROW EXECUTE FUNCTION trigger_func('before_upd_a_row')
+ CREATE TRIGGER before_upd_a_stmt_trig BEFORE UPDATE OF a ON public.main_table FOR EACH STATEMENT EXECUTE FUNCTION trigger_func('before_upd_a_stmt')
+ CREATE TRIGGER before_upd_a_stmt_trig BEFORE UPDATE OF a ON public.main_table1 FOR EACH STATEMENT EXECUTE FUNCTION trigger_func('before_upd_a_stmt')
+(16 rows)
+
+SELECT pc.relname, pd.tgname, obj_description(pd.oid, 'pg_trigger')
+FROM pg_trigger pd JOIN pg_class pc
+ON pc.oid = pd.tgrelid AND pd.tgname = 'before_ins_stmt_trig'
+ORDER BY 1;
+ relname | tgname | obj_description
+-------------+----------------------+------------------------------
+ main_table | before_ins_stmt_trig | trigger before_ins_stmt_trig
+ main_table1 | before_ins_stmt_trig | trigger before_ins_stmt_trig
+(2 rows)
+
+COMMENT ON TRIGGER before_ins_stmt_trig ON main_table IS NULL;
+DROP TABLE main_table1, main_table2;
--
-- Test case for bug with BEFORE trigger followed by AFTER trigger with WHEN
--
@@ -422,7 +476,27 @@ NOTICE: dummy_update_func(afterb) called: action = UPDATE, old = (t), new = (f)
UPDATE some_t SET some_col = TRUE;
NOTICE: dummy_update_func(before) called: action = UPDATE, old = (f), new = (t)
NOTICE: dummy_update_func(aftera) called: action = UPDATE, old = (f), new = (t)
+ALTER TABLE some_t ENABLE REPLICA TRIGGER some_trig_aftera;
+ALTER TABLE some_t DISABLE TRIGGER some_trig_afterb;
+ALTER TABLE some_t ENABLE ALWAYS TRIGGER some_trig_before;
+CREATE TABLE some_t1 (c INT, LIKE some_t INCLUDING TRIGGERS INCLUDING COMMENTS);
+\d+ some_t1
+ Table "public.some_t1"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+----------+---------+-----------+----------+---------+---------+--------------+-------------
+ c | integer | | | | plain | |
+ some_col | boolean | | not null | | plain | |
+Not-null constraints:
+ "some_t_some_col_not_null" NOT NULL "some_col"
+Disabled user triggers:
+ some_trig_afterb AFTER UPDATE ON some_t1 FOR EACH ROW WHEN (NOT new.some_col) EXECUTE FUNCTION dummy_update_func('afterb')
+Triggers firing always:
+ some_trig_before BEFORE UPDATE ON some_t1 FOR EACH ROW EXECUTE FUNCTION dummy_update_func('before')
+Triggers firing on replica only:
+ some_trig_aftera AFTER UPDATE ON some_t1 FOR EACH ROW WHEN (NOT old.some_col AND new.some_col) EXECUTE FUNCTION dummy_update_func('aftera')
+
DROP TABLE some_t;
+DROP TABLE some_t1;
-- bogus cases
CREATE TRIGGER error_upd_and_col BEFORE UPDATE OR UPDATE OF a ON main_table
FOR EACH ROW EXECUTE PROCEDURE trigger_func('error_upd_and_col');
@@ -861,6 +935,15 @@ CREATE TRIGGER instead_of_update_trig INSTEAD OF UPDATE ON main_view
FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_upd');
CREATE TRIGGER instead_of_delete_trig INSTEAD OF DELETE ON main_view
FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_del');
+CREATE TABLE main_view_table(LIKE main_view INCLUDING TRIGGERS); -- View triggers are not copied
+\d main_view_table
+ Table "public.main_view_table"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | |
+ b | integer | | |
+
+DROP TABLE main_view_table;
-- Valid BEFORE statement VIEW triggers
CREATE TRIGGER before_ins_stmt_trig BEFORE INSERT ON main_view
FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('before_view_ins_stmt');
@@ -2326,6 +2409,20 @@ create constraint trigger parted_trig_two after insert on parted_constr
deferrable initially deferred enforced
for each row when (bark(new.b) AND new.a % 2 = 1)
execute procedure trigger_notice_ab();
+create table parted_constr_copy (c int, like parted_constr including all, d int);
+select pg_get_triggerdef(oid)
+from pg_trigger
+where not tgisinternal and tgrelid in ('parted_constr_copy'::regclass, 'parted_constr'::regclass)
+order by tgname;
+ pg_get_triggerdef
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE CONSTRAINT TRIGGER parted_trig AFTER INSERT ON public.parted_constr DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE FUNCTION trigger_notice_ab()
+ CREATE CONSTRAINT TRIGGER parted_trig AFTER INSERT ON public.parted_constr_copy DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE FUNCTION trigger_notice_ab()
+ CREATE CONSTRAINT TRIGGER parted_trig_two AFTER INSERT ON public.parted_constr DEFERRABLE INITIALLY DEFERRED FOR EACH ROW WHEN ((bark(new.b) AND ((new.a % 2) = 1))) EXECUTE FUNCTION trigger_notice_ab()
+ CREATE CONSTRAINT TRIGGER parted_trig_two AFTER INSERT ON public.parted_constr_copy DEFERRABLE INITIALLY DEFERRED FOR EACH ROW WHEN ((bark(new.b) AND ((new.a % 2) = 1))) EXECUTE FUNCTION trigger_notice_ab()
+(4 rows)
+
+drop table parted_constr_copy;
-- The immediate constraint is fired immediately; the WHEN clause of the
-- deferred constraint is also called immediately. The deferred constraint
-- is fired at commit time.
@@ -3106,6 +3203,18 @@ create trigger iocdu_tt_parted_insert_trig
create trigger iocdu_tt_parted_update_trig
after update on iocdu_tt_parted referencing old table as old_table new table as new_table
for each statement execute procedure dump_update();
+CREATE TABLE iocdu_tt_parted_copy(LIKE iocdu_tt_parted INCLUDING TRIGGERS);
+\d iocdu_tt_parted_copy
+ Table "public.iocdu_tt_parted_copy"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | not null |
+ b | text | | |
+Triggers:
+ iocdu_tt_parted_insert_trig AFTER INSERT ON iocdu_tt_parted_copy REFERENCING NEW TABLE AS new_table FOR EACH STATEMENT EXECUTE FUNCTION dump_insert()
+ iocdu_tt_parted_update_trig AFTER UPDATE ON iocdu_tt_parted_copy REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table FOR EACH STATEMENT EXECUTE FUNCTION dump_update()
+
+DROP TABLE iocdu_tt_parted_copy;
-- inserts only
insert into iocdu_tt_parted values (1, 'AAA'), (2, 'BBB')
on conflict (a) do
@@ -3589,6 +3698,26 @@ begin
end;
$$;
alter function whoami() owner to regress_fn_owner;
+-- CREATE TABLE LIKE INCLUDING TRIGGERS for constraint trigger
+create schema test_trig;
+create table test_trig.trig_t1 (id integer);
+create table test_trig.trig_t2 (id integer);
+alter function whoami() set schema test_trig;
+create constraint trigger con_trig_test after insert on test_trig.trig_t1
+from test_trig.trig_t2
+deferrable initially deferred
+for each row
+execute function test_trig.whoami();
+create table trig_t1_copy(like test_trig.trig_t1 including triggers);
+select pg_get_triggerdef(oid) from pg_trigger where tgrelid = 'trig_t1_copy'::regclass;
+ pg_get_triggerdef
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE CONSTRAINT TRIGGER con_trig_test AFTER INSERT ON public.trig_t1_copy FROM test_trig.trig_t2 DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE FUNCTION test_trig.whoami()
+(1 row)
+
+alter function test_trig.whoami() set schema public;
+drop table test_trig.trig_t1, test_trig.trig_t2, trig_t1_copy;
+drop schema test_trig;
create table defer_trig (id integer);
grant insert on defer_trig to public;
create constraint trigger whoami after insert on defer_trig
diff --git a/src/test/regress/sql/create_table_like.sql b/src/test/regress/sql/create_table_like.sql
index d52a93ef131..1f04c3b67cb 100644
--- a/src/test/regress/sql/create_table_like.sql
+++ b/src/test/regress/sql/create_table_like.sql
@@ -259,6 +259,34 @@ ALTER TABLE ctl_table ALTER COLUMN b SET STORAGE MAIN;
\d+ ctl_table
+CREATE FUNCTION trigger_func1() RETURNS trigger LANGUAGE plpgsql AS '
+BEGIN
+ RAISE NOTICE ''trigger_func1(%) called: action = %, when = %, level = %'', TG_ARGV[0], TG_OP, TG_WHEN, TG_LEVEL;
+ RETURN NULL;
+END;';
+
+CREATE TRIGGER trigtest_before_stmt BEFORE DELETE OR UPDATE ON ctl_table
+FOR EACH ROW WHEN (OLD.a > 0)
+EXECUTE PROCEDURE trigger_func1('trigtest_before_stmt');
+
+CREATE TRIGGER trigtest_after_stmt AFTER UPDATE OF a, b ON ctl_table
+FOR EACH STATEMENT
+EXECUTE PROCEDURE trigger_func1('trigtest_after_stmt');
+
+ALTER TABLE ctl_table ENABLE REPLICA TRIGGER trigtest_before_stmt;
+ALTER TABLE ctl_table ENABLE ALWAYS TRIGGER trigtest_after_stmt;
+
+-- CREATE FOREIGN TABLE LIKE should not copy triggers that have transition tables
+CREATE TRIGGER trigtest_after_stmt1 AFTER INSERT ON ctl_table
+REFERENCING NEW TABLE AS new_table
+FOR EACH STATEMENT
+EXECUTE PROCEDURE trigger_func1('trigtest_after_stmt1');
+
+-- CREATE FOREIGN TABLE LIKE should not copy source table constraint trigger
+CREATE CONSTRAINT TRIGGER constr_trig
+AFTER INSERT ON ctl_table DEFERRABLE INITIALLY DEFERRED enforced FOR EACH ROW
+EXECUTE PROCEDURE trigger_func1();
+
-- Test EXCLUDING ALL
CREATE FOREIGN TABLE ctl_foreign_table1(LIKE ctl_table EXCLUDING ALL) SERVER ctl_s0;
\d+ ctl_foreign_table1
@@ -306,3 +334,4 @@ DROP TABLE ctl_table;
DROP FOREIGN TABLE ctl_foreign_table1;
DROP FOREIGN TABLE ctl_foreign_table2;
DROP FOREIGN DATA WRAPPER ctl_dummy CASCADE;
+DROP FUNCTION trigger_func1;
diff --git a/src/test/regress/sql/triggers.sql b/src/test/regress/sql/triggers.sql
index 2285e90110e..3689172b8dc 100644
--- a/src/test/regress/sql/triggers.sql
+++ b/src/test/regress/sql/triggers.sql
@@ -120,6 +120,18 @@ FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func('before_ins_stmt');
CREATE TRIGGER after_ins_stmt_trig AFTER INSERT ON main_table
FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func('after_ins_stmt');
+CREATE TRIGGER wholetrig BEFORE UPDATE ON main_table FOR EACH ROW
+WHEN (OLD IS NOT NULL)
+EXECUTE PROCEDURE trigger_func('modified_a');
+CREATE TABLE main_table1(LIKE main_table INCLUDING TRIGGERS); -- error, wholerow reference not allowed
+DROP TRIGGER wholetrig ON main_table;
+
+CREATE TRIGGER wholetrig BEFORE UPDATE ON main_table FOR EACH ROW
+WHEN (NEW IS NOT NULL)
+EXECUTE PROCEDURE trigger_func('modified_a');
+CREATE TABLE main_table1(LIKE main_table INCLUDING TRIGGERS); -- error, wholerow reference not allowed
+DROP TRIGGER wholetrig ON main_table;
+
--
-- if neither 'FOR EACH ROW' nor 'FOR EACH STATEMENT' was specified,
-- CREATE TRIGGER should default to 'FOR EACH STATEMENT'
@@ -234,6 +246,24 @@ SELECT pg_get_triggerdef(oid) FROM pg_trigger WHERE tgrelid = 'main_table'::regc
UPDATE main_table SET a = 50;
UPDATE main_table SET b = 10;
+-- CREATE TABLE LIKE INCLUDING TRIGGERS tests
+COMMENT ON TRIGGER before_ins_stmt_trig ON main_table IS 'trigger before_ins_stmt_trig';
+CREATE TABLE main_table1(c int, LIKE main_table INCLUDING TRIGGERS INCLUDING COMMENTS);
+CREATE TABLE main_table2(c int, LIKE main_table EXCLUDING TRIGGERS INCLUDING COMMENTS);
+
+SELECT pg_get_triggerdef(oid)
+FROM pg_trigger
+WHERE NOT tgisinternal AND tgrelid IN ('main_table'::regclass, 'main_table1'::regclass, 'main_table2'::regclass)
+ORDER BY tgname, tgrelid::regclass::text COLLATE "C";
+
+SELECT pc.relname, pd.tgname, obj_description(pd.oid, 'pg_trigger')
+FROM pg_trigger pd JOIN pg_class pc
+ON pc.oid = pd.tgrelid AND pd.tgname = 'before_ins_stmt_trig'
+ORDER BY 1;
+COMMENT ON TRIGGER before_ins_stmt_trig ON main_table IS NULL;
+
+DROP TABLE main_table1, main_table2;
+
--
-- Test case for bug with BEFORE trigger followed by AFTER trigger with WHEN
--
@@ -258,7 +288,13 @@ INSERT INTO some_t VALUES (TRUE);
UPDATE some_t SET some_col = TRUE;
UPDATE some_t SET some_col = FALSE;
UPDATE some_t SET some_col = TRUE;
+ALTER TABLE some_t ENABLE REPLICA TRIGGER some_trig_aftera;
+ALTER TABLE some_t DISABLE TRIGGER some_trig_afterb;
+ALTER TABLE some_t ENABLE ALWAYS TRIGGER some_trig_before;
+CREATE TABLE some_t1 (c INT, LIKE some_t INCLUDING TRIGGERS INCLUDING COMMENTS);
+\d+ some_t1
DROP TABLE some_t;
+DROP TABLE some_t1;
-- bogus cases
CREATE TRIGGER error_upd_and_col BEFORE UPDATE OR UPDATE OF a ON main_table
@@ -621,6 +657,10 @@ FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_upd');
CREATE TRIGGER instead_of_delete_trig INSTEAD OF DELETE ON main_view
FOR EACH ROW EXECUTE PROCEDURE view_trigger('instead_of_del');
+CREATE TABLE main_view_table(LIKE main_view INCLUDING TRIGGERS); -- View triggers are not copied
+\d main_view_table
+DROP TABLE main_view_table;
+
-- Valid BEFORE statement VIEW triggers
CREATE TRIGGER before_ins_stmt_trig BEFORE INSERT ON main_view
FOR EACH STATEMENT EXECUTE PROCEDURE view_trigger('before_view_ins_stmt');
@@ -1609,6 +1649,13 @@ create constraint trigger parted_trig_two after insert on parted_constr
for each row when (bark(new.b) AND new.a % 2 = 1)
execute procedure trigger_notice_ab();
+create table parted_constr_copy (c int, like parted_constr including all, d int);
+select pg_get_triggerdef(oid)
+from pg_trigger
+where not tgisinternal and tgrelid in ('parted_constr_copy'::regclass, 'parted_constr'::regclass)
+order by tgname;
+drop table parted_constr_copy;
+
-- The immediate constraint is fired immediately; the WHEN clause of the
-- deferred constraint is also called immediately. The deferred constraint
-- is fired at commit time.
@@ -2283,6 +2330,10 @@ create trigger iocdu_tt_parted_update_trig
after update on iocdu_tt_parted referencing old table as old_table new table as new_table
for each statement execute procedure dump_update();
+CREATE TABLE iocdu_tt_parted_copy(LIKE iocdu_tt_parted INCLUDING TRIGGERS);
+\d iocdu_tt_parted_copy
+DROP TABLE iocdu_tt_parted_copy;
+
-- inserts only
insert into iocdu_tt_parted values (1, 'AAA'), (2, 'BBB')
on conflict (a) do
@@ -2740,6 +2791,25 @@ end;
$$;
alter function whoami() owner to regress_fn_owner;
+-- CREATE TABLE LIKE INCLUDING TRIGGERS for constraint trigger
+create schema test_trig;
+create table test_trig.trig_t1 (id integer);
+create table test_trig.trig_t2 (id integer);
+alter function whoami() set schema test_trig;
+
+create constraint trigger con_trig_test after insert on test_trig.trig_t1
+from test_trig.trig_t2
+deferrable initially deferred
+for each row
+execute function test_trig.whoami();
+
+create table trig_t1_copy(like test_trig.trig_t1 including triggers);
+select pg_get_triggerdef(oid) from pg_trigger where tgrelid = 'trig_t1_copy'::regclass;
+
+alter function test_trig.whoami() set schema public;
+drop table test_trig.trig_t1, test_trig.trig_t2, trig_t1_copy;
+drop schema test_trig;
+
create table defer_trig (id integer);
grant insert on defer_trig to public;
create constraint trigger whoami after insert on defer_trig
--
2.34.1
view thread (15+ messages)
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], [email protected]
Subject: Re: CREATE TABLE LIKE INCLUDING TRIGGERS
In-Reply-To: <CACJufxE6RsYTWPwMMaTwfHeVsWHzbfdj-uh-+L2jaciAuB28Wg@mail.gmail.com>
* 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