hi.
v2-0001, v2-0002 is the same as [1].
The parameters relOid and refRelOid are removed from the CreateTrigger function
and instead added to the CreateTrigStmt structure.
The main reason is to avoid repeated name lookups.
In fact, we already did this in CreateTriggerFiringOn, see below code snippet.
If the OID is valid, use it preferentially; otherwise, fall back to resolving
the relation via the RangeVar.
```
if (OidIsValid(relOid))
rel = table_open(relOid, ShareRowExclusiveLock);
else
rel = table_openrv(stmt->relation, ShareRowExclusiveLock);
if (OidIsValid(refRelOid))
{
LockRelationOid(refRelOid, AccessShareLock);
constrrelid = refRelOid;
}
else if (stmt->constrrel != NULL)
constrrelid = RangeVarGetRelid(stmt->constrrel, AccessShareLock,
false);
```
v2-0003: is the actual implementation.
The general workflow would be as follows: collect all affected triggers,
retrieve their definitions, drop the existing triggers, and then recreate them.
Any comments associated with the triggers will also be dropped and recreated.
[1]:
https://postgr.es/m/cacjufxfu7y4fhvkakt2kaj8ym2t5tcwn93cr_6h4x66ilrs...@mail.gmail.com
--
jian
https://www.enterprisedb.com/
From c18ea0dbc66832a5eba9ce9cf959583dfc2d594c Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Sat, 27 Dec 2025 13:55:59 +0800
Subject: [PATCH v2 1/3] add relOid field to CreateTrigStmt
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
add the relOid field to CreateTrigStmt is very useful for copying or recreating
a CreateTrigStmt.
Copying CreateTrigStmt node can happen like CREATE TABLE LIKE INCLUDING TRIGGERS.
To let ALTER COLUMN SET DATA TYPE cope with trigger dependencies, we need get
the trigger definition then do parse analysis for CreateTrigStmt again.
in function CreateTrigger, we have below comments:
```
* relOid, if nonzero, is the relation on which the trigger should be * created.
If zero, the name provided in the statement will be looked up.
```
We can move the "relOid" argument out of the CreateTrigger function’s parameter
list and instead store it in CreateTrigStmt.
The following are the reason why:
1. CreateTrigger has too many arguments; reducing the argument list by one is a good thing.
2. To implement CREATE TABLE LIKE INCLUDING TRIGGERS, we need to pass the new
relation OID to CreateTrigger, but it's not doable from
ProcessUtilitySlow->CreateTrigger.
3. To allow ALTER COLUMN SET DATA TYPE to cope with trigger dependencies, we
also need to pass the new relation OID to CreateTrigger, which for the same
reason as above cannot be done from ProcessUtilitySlow. This can help to reduce
repeated lookup issue.
discussion: https://postgr.es/m/cacjufxhjar2fjbeb6ghg_-n5dxx5jvnjkslouxoyt4teaaw...@mail.gmail.com
discussion: https://postgr.es/m/cacjufxgkqyrmwmdvuoupet0443outgf_dkcpw3tfjiutfuy...@mail.gmail.com
commitfest: https://commitfest.postgresql.org/patch/6087
commitfest: https://commitfest.postgresql.org/patch/6089
---
src/backend/catalog/index.c | 3 ++-
src/backend/commands/tablecmds.c | 12 ++++++++----
src/backend/commands/trigger.c | 16 +++++++---------
src/backend/parser/gram.y | 2 ++
src/backend/tcop/utility.c | 2 +-
src/include/commands/trigger.h | 4 ++--
src/include/nodes/parsenodes.h | 11 +++++++++++
7 files changed, 33 insertions(+), 17 deletions(-)
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index 8dea58ad96b..8e74ff1dd33 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -2028,6 +2028,7 @@ index_constraint_create(Relation heapRelation,
"PK_ConstraintTrigger" :
"Unique_ConstraintTrigger";
trigger->relation = NULL;
+ trigger->relOid = RelationGetRelid(heapRelation);
trigger->funcname = SystemFuncName("unique_key_recheck");
trigger->args = NIL;
trigger->row = true;
@@ -2040,7 +2041,7 @@ index_constraint_create(Relation heapRelation,
trigger->initdeferred = initdeferred;
trigger->constrrel = NULL;
- (void) CreateTrigger(trigger, NULL, RelationGetRelid(heapRelation),
+ (void) CreateTrigger(trigger, NULL,
InvalidOid, conOid, indexRelationId, InvalidOid,
InvalidOid, NULL, true, false);
}
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 1d9565b09fc..ee400c1cd58 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -13840,6 +13840,7 @@ CreateFKCheckTrigger(Oid myRelOid, Oid refRelOid, Constraint *fkconstraint,
fk_trigger->isconstraint = true;
fk_trigger->trigname = "RI_ConstraintTrigger_c";
fk_trigger->relation = NULL;
+ fk_trigger->relOid = myRelOid;
/* Either ON INSERT or ON UPDATE */
if (on_insert)
@@ -13863,7 +13864,7 @@ CreateFKCheckTrigger(Oid myRelOid, Oid refRelOid, Constraint *fkconstraint,
fk_trigger->initdeferred = fkconstraint->initdeferred;
fk_trigger->constrrel = NULL;
- trigAddress = CreateTrigger(fk_trigger, NULL, myRelOid, refRelOid,
+ trigAddress = CreateTrigger(fk_trigger, NULL, refRelOid,
constraintOid, indexOid, InvalidOid,
parentTrigOid, NULL, true, false);
@@ -13899,6 +13900,7 @@ createForeignKeyActionTriggers(Oid myRelOid, Oid refRelOid, Constraint *fkconstr
fk_trigger->isconstraint = true;
fk_trigger->trigname = "RI_ConstraintTrigger_a";
fk_trigger->relation = NULL;
+ fk_trigger->relOid = refRelOid;
fk_trigger->args = NIL;
fk_trigger->row = true;
fk_trigger->timing = TRIGGER_TYPE_AFTER;
@@ -13941,7 +13943,7 @@ createForeignKeyActionTriggers(Oid myRelOid, Oid refRelOid, Constraint *fkconstr
break;
}
- trigAddress = CreateTrigger(fk_trigger, NULL, refRelOid, myRelOid,
+ trigAddress = CreateTrigger(fk_trigger, NULL, myRelOid,
constraintOid, indexOid, InvalidOid,
parentDelTrigger, NULL, true, false);
if (deleteTrigOid)
@@ -13959,6 +13961,7 @@ createForeignKeyActionTriggers(Oid myRelOid, Oid refRelOid, Constraint *fkconstr
fk_trigger->isconstraint = true;
fk_trigger->trigname = "RI_ConstraintTrigger_a";
fk_trigger->relation = NULL;
+ fk_trigger->relOid = refRelOid;
fk_trigger->args = NIL;
fk_trigger->row = true;
fk_trigger->timing = TRIGGER_TYPE_AFTER;
@@ -14001,7 +14004,7 @@ createForeignKeyActionTriggers(Oid myRelOid, Oid refRelOid, Constraint *fkconstr
break;
}
- trigAddress = CreateTrigger(fk_trigger, NULL, refRelOid, myRelOid,
+ trigAddress = CreateTrigger(fk_trigger, NULL, myRelOid,
constraintOid, indexOid, InvalidOid,
parentUpdTrigger, NULL, true, false);
if (updateTrigOid)
@@ -20905,6 +20908,7 @@ CloneRowTriggersToPartition(Relation parent, Relation partition)
trigStmt->isconstraint = OidIsValid(trigForm->tgconstraint);
trigStmt->trigname = NameStr(trigForm->tgname);
trigStmt->relation = NULL;
+ trigStmt->relOid = RelationGetRelid(partition);
trigStmt->funcname = NULL; /* passed separately */
trigStmt->args = trigargs;
trigStmt->row = true;
@@ -20917,7 +20921,7 @@ CloneRowTriggersToPartition(Relation parent, Relation partition)
trigStmt->initdeferred = trigForm->tginitdeferred;
trigStmt->constrrel = NULL; /* passed separately */
- CreateTriggerFiringOn(trigStmt, NULL, RelationGetRelid(partition),
+ CreateTriggerFiringOn(trigStmt, NULL,
trigForm->tgconstrrelid, InvalidOid, InvalidOid,
trigForm->tgfoid, trigForm->oid, qual,
false, true, trigForm->tgenabled);
diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c
index 12c97f2c023..6e079213863 100644
--- a/src/backend/commands/trigger.c
+++ b/src/backend/commands/trigger.c
@@ -111,9 +111,6 @@ static HeapTuple check_modified_virtual_generated(TupleDesc tupdesc, HeapTuple t
* queryString is the source text of the CREATE TRIGGER command.
* This must be supplied if a whenClause is specified, else it can be NULL.
*
- * relOid, if nonzero, is the relation on which the trigger should be
- * created. If zero, the name provided in the statement will be looked up.
- *
* refRelOid, if nonzero, is the relation to which the constraint trigger
* refers. If zero, the constraint relation name provided in the statement
* will be looked up as needed.
@@ -158,12 +155,12 @@ static HeapTuple check_modified_virtual_generated(TupleDesc tupdesc, HeapTuple t
*/
ObjectAddress
CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
- Oid relOid, Oid refRelOid, Oid constraintOid, Oid indexOid,
+ Oid refRelOid, Oid constraintOid, Oid indexOid,
Oid funcoid, Oid parentTriggerOid, Node *whenClause,
bool isInternal, bool in_partition)
{
return
- CreateTriggerFiringOn(stmt, queryString, relOid, refRelOid,
+ CreateTriggerFiringOn(stmt, queryString, refRelOid,
constraintOid, indexOid, funcoid,
parentTriggerOid, whenClause, isInternal,
in_partition, TRIGGER_FIRES_ON_ORIGIN);
@@ -175,7 +172,7 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
*/
ObjectAddress
CreateTriggerFiringOn(CreateTrigStmt *stmt, const char *queryString,
- Oid relOid, Oid refRelOid, Oid constraintOid,
+ Oid refRelOid, Oid constraintOid,
Oid indexOid, Oid funcoid, Oid parentTriggerOid,
Node *whenClause, bool isInternal, bool in_partition,
char trigger_fires_when)
@@ -208,8 +205,8 @@ CreateTriggerFiringOn(CreateTrigStmt *stmt, const char *queryString,
bool existing_isInternal = false;
bool existing_isClone = false;
- if (OidIsValid(relOid))
- rel = table_open(relOid, ShareRowExclusiveLock);
+ if (OidIsValid(stmt->relOid))
+ rel = table_open(stmt->relOid, ShareRowExclusiveLock);
else
rel = table_openrv(stmt->relation, ShareRowExclusiveLock);
@@ -1186,8 +1183,9 @@ CreateTriggerFiringOn(CreateTrigStmt *stmt, const char *queryString,
map_partition_varattnos((List *) qual, PRS2_NEW_VARNO,
childTbl, rel);
+ childStmt->relOid = partdesc->oids[i];
CreateTriggerFiringOn(childStmt, queryString,
- partdesc->oids[i], refRelOid,
+ refRelOid,
InvalidOid, InvalidOid,
funcoid, trigoid, qual,
isInternal, true, trigger_fires_when);
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 28f4e11e30f..01e66ba615e 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -6139,6 +6139,7 @@ CreateTrigStmt:
n->isconstraint = false;
n->trigname = $4;
n->relation = $8;
+ n->relOid = InvalidOid;
n->funcname = $14;
n->args = $16;
n->row = $10;
@@ -6188,6 +6189,7 @@ CreateTrigStmt:
n->isconstraint = true;
n->trigname = $5;
n->relation = $9;
+ n->relOid = InvalidOid;
n->funcname = $18;
n->args = $20;
n->row = true;
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index d18a3a60a46..7d6387ad9b5 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -1701,7 +1701,7 @@ ProcessUtilitySlow(ParseState *pstate,
case T_CreateTrigStmt:
address = CreateTrigger((CreateTrigStmt *) parsetree,
- queryString, InvalidOid, InvalidOid,
+ queryString, InvalidOid,
InvalidOid, InvalidOid, InvalidOid,
InvalidOid, NULL, false, false);
break;
diff --git a/src/include/commands/trigger.h b/src/include/commands/trigger.h
index cfd7daa20ed..ae7abd456bf 100644
--- a/src/include/commands/trigger.h
+++ b/src/include/commands/trigger.h
@@ -152,11 +152,11 @@ extern PGDLLIMPORT int SessionReplicationRole;
#define TRIGGER_DISABLED 'D'
extern ObjectAddress CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
- Oid relOid, Oid refRelOid, Oid constraintOid, Oid indexOid,
+ Oid refRelOid, Oid constraintOid, Oid indexOid,
Oid funcoid, Oid parentTriggerOid, Node *whenClause,
bool isInternal, bool in_partition);
extern ObjectAddress CreateTriggerFiringOn(CreateTrigStmt *stmt, const char *queryString,
- Oid relOid, Oid refRelOid, Oid constraintOid,
+ Oid refRelOid, Oid constraintOid,
Oid indexOid, Oid funcoid, Oid parentTriggerOid,
Node *whenClause, bool isInternal, bool in_partition,
char trigger_fires_when);
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index bc7adba4a0f..f876f00ab9f 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -3138,6 +3138,17 @@ typedef struct CreateTrigStmt
bool isconstraint; /* This is a constraint trigger */
char *trigname; /* TRIGGER's name */
RangeVar *relation; /* relation trigger is on */
+
+ /*
+ * The OID of the relation on which the trigger is to be created. If this
+ * is InvalidOid, CreateTrigStmt.relation is used to perform the lookup;
+ * otherwise, use this directly. This is useful when CreateTrigger is
+ * invoked indirectly rather than directly from the parser.
+ *
+ * Using the OID also avoids repeated relation name lookups.
+ */
+ Oid relOid;
+
List *funcname; /* qual. name of function to call */
List *args; /* list of String or NIL */
bool row; /* ROW/STATEMENT */
--
2.34.1
From 7859802d453d3161291dacef5468380f9d7780b0 Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Sat, 27 Dec 2025 13:58:11 +0800
Subject: [PATCH v2 2/3] add constrrelOid field to CreateTrigStmt
In the spirit of the change made to add the relOid field to CreateTrigStmt.
discussion: https://postgr.es/m/cacjufxhjar2fjbeb6ghg_-n5dxx5jvnjkslouxoyt4teaaw...@mail.gmail.com
discussion: https://postgr.es/m/cacjufxgkqyrmwmdvuoupet0443outgf_dkcpw3tfjiutfuy...@mail.gmail.com
commitfest: https://commitfest.postgresql.org/patch/6087
commitfest: https://commitfest.postgresql.org/patch/6089
---
src/backend/catalog/index.c | 3 ++-
src/backend/commands/tablecmds.c | 13 ++++++++-----
src/backend/commands/trigger.c | 18 +++++++-----------
src/backend/parser/gram.y | 2 ++
src/backend/tcop/utility.c | 2 +-
src/include/commands/trigger.h | 4 ++--
src/include/nodes/parsenodes.h | 1 +
7 files changed, 23 insertions(+), 20 deletions(-)
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index 8e74ff1dd33..b3b034cf3b8 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -2040,9 +2040,10 @@ index_constraint_create(Relation heapRelation,
trigger->deferrable = true;
trigger->initdeferred = initdeferred;
trigger->constrrel = NULL;
+ trigger->constrrelOid = InvalidOid;
(void) CreateTrigger(trigger, NULL,
- InvalidOid, conOid, indexRelationId, InvalidOid,
+ conOid, indexRelationId, InvalidOid,
InvalidOid, NULL, true, false);
}
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index ee400c1cd58..cae3d5218aa 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -13863,8 +13863,9 @@ CreateFKCheckTrigger(Oid myRelOid, Oid refRelOid, Constraint *fkconstraint,
fk_trigger->deferrable = fkconstraint->deferrable;
fk_trigger->initdeferred = fkconstraint->initdeferred;
fk_trigger->constrrel = NULL;
+ fk_trigger->constrrelOid = refRelOid;
- trigAddress = CreateTrigger(fk_trigger, NULL, refRelOid,
+ trigAddress = CreateTrigger(fk_trigger, NULL,
constraintOid, indexOid, InvalidOid,
parentTrigOid, NULL, true, false);
@@ -13909,6 +13910,7 @@ createForeignKeyActionTriggers(Oid myRelOid, Oid refRelOid, Constraint *fkconstr
fk_trigger->whenClause = NULL;
fk_trigger->transitionRels = NIL;
fk_trigger->constrrel = NULL;
+ fk_trigger->constrrelOid = myRelOid;
switch (fkconstraint->fk_del_action)
{
@@ -13943,7 +13945,7 @@ createForeignKeyActionTriggers(Oid myRelOid, Oid refRelOid, Constraint *fkconstr
break;
}
- trigAddress = CreateTrigger(fk_trigger, NULL, myRelOid,
+ trigAddress = CreateTrigger(fk_trigger, NULL,
constraintOid, indexOid, InvalidOid,
parentDelTrigger, NULL, true, false);
if (deleteTrigOid)
@@ -13970,6 +13972,7 @@ createForeignKeyActionTriggers(Oid myRelOid, Oid refRelOid, Constraint *fkconstr
fk_trigger->whenClause = NULL;
fk_trigger->transitionRels = NIL;
fk_trigger->constrrel = NULL;
+ fk_trigger->constrrelOid = myRelOid;
switch (fkconstraint->fk_upd_action)
{
@@ -14004,7 +14007,7 @@ createForeignKeyActionTriggers(Oid myRelOid, Oid refRelOid, Constraint *fkconstr
break;
}
- trigAddress = CreateTrigger(fk_trigger, NULL, myRelOid,
+ trigAddress = CreateTrigger(fk_trigger, NULL,
constraintOid, indexOid, InvalidOid,
parentUpdTrigger, NULL, true, false);
if (updateTrigOid)
@@ -20920,9 +20923,9 @@ CloneRowTriggersToPartition(Relation parent, Relation partition)
trigStmt->deferrable = trigForm->tgdeferrable;
trigStmt->initdeferred = trigForm->tginitdeferred;
trigStmt->constrrel = NULL; /* passed separately */
+ trigStmt->constrrelOid = trigForm->tgconstrrelid;
- CreateTriggerFiringOn(trigStmt, NULL,
- trigForm->tgconstrrelid, InvalidOid, InvalidOid,
+ CreateTriggerFiringOn(trigStmt, NULL, InvalidOid, InvalidOid,
trigForm->tgfoid, trigForm->oid, qual,
false, true, trigForm->tgenabled);
diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c
index 6e079213863..18cd3cc41b3 100644
--- a/src/backend/commands/trigger.c
+++ b/src/backend/commands/trigger.c
@@ -111,10 +111,6 @@ static HeapTuple check_modified_virtual_generated(TupleDesc tupdesc, HeapTuple t
* queryString is the source text of the CREATE TRIGGER command.
* This must be supplied if a whenClause is specified, else it can be NULL.
*
- * refRelOid, if nonzero, is the relation to which the constraint trigger
- * refers. If zero, the constraint relation name provided in the statement
- * will be looked up as needed.
- *
* constraintOid, if nonzero, says that this trigger is being created
* internally to implement that constraint. A suitable pg_depend entry will
* be made to link the trigger to that constraint. constraintOid is zero when
@@ -155,12 +151,12 @@ static HeapTuple check_modified_virtual_generated(TupleDesc tupdesc, HeapTuple t
*/
ObjectAddress
CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
- Oid refRelOid, Oid constraintOid, Oid indexOid,
+ Oid constraintOid, Oid indexOid,
Oid funcoid, Oid parentTriggerOid, Node *whenClause,
bool isInternal, bool in_partition)
{
return
- CreateTriggerFiringOn(stmt, queryString, refRelOid,
+ CreateTriggerFiringOn(stmt, queryString,
constraintOid, indexOid, funcoid,
parentTriggerOid, whenClause, isInternal,
in_partition, TRIGGER_FIRES_ON_ORIGIN);
@@ -172,7 +168,7 @@ CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
*/
ObjectAddress
CreateTriggerFiringOn(CreateTrigStmt *stmt, const char *queryString,
- Oid refRelOid, Oid constraintOid,
+ Oid constraintOid,
Oid indexOid, Oid funcoid, Oid parentTriggerOid,
Node *whenClause, bool isInternal, bool in_partition,
char trigger_fires_when)
@@ -324,10 +320,10 @@ CreateTriggerFiringOn(CreateTrigStmt *stmt, const char *queryString,
* might end up creating a pg_constraint entry referencing a
* nonexistent table.
*/
- if (OidIsValid(refRelOid))
+ if (OidIsValid(stmt->constrrelOid))
{
- LockRelationOid(refRelOid, AccessShareLock);
- constrrelid = refRelOid;
+ LockRelationOid(stmt->constrrelOid, AccessShareLock);
+ constrrelid = stmt->constrrelOid;
}
else if (stmt->constrrel != NULL)
constrrelid = RangeVarGetRelid(stmt->constrrel, AccessShareLock,
@@ -1184,8 +1180,8 @@ CreateTriggerFiringOn(CreateTrigStmt *stmt, const char *queryString,
childTbl, rel);
childStmt->relOid = partdesc->oids[i];
+ childStmt->constrrelOid = stmt->constrrelOid;
CreateTriggerFiringOn(childStmt, queryString,
- refRelOid,
InvalidOid, InvalidOid,
funcoid, trigoid, qual,
isInternal, true, trigger_fires_when);
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 01e66ba615e..4a0bab33e70 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -6151,6 +6151,7 @@ CreateTrigStmt:
n->deferrable = false;
n->initdeferred = false;
n->constrrel = NULL;
+ n->constrrelOid = InvalidOid;
$$ = (Node *) n;
}
| CREATE opt_or_replace CONSTRAINT TRIGGER name AFTER TriggerEvents ON
@@ -6202,6 +6203,7 @@ CreateTrigStmt:
&n->deferrable, &n->initdeferred, &dummy,
NULL, NULL, yyscanner);
n->constrrel = $10;
+ n->constrrelOid = InvalidOid;
$$ = (Node *) n;
}
;
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index 7d6387ad9b5..6c3642e00bc 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -1701,7 +1701,7 @@ ProcessUtilitySlow(ParseState *pstate,
case T_CreateTrigStmt:
address = CreateTrigger((CreateTrigStmt *) parsetree,
- queryString, InvalidOid,
+ queryString,
InvalidOid, InvalidOid, InvalidOid,
InvalidOid, NULL, false, false);
break;
diff --git a/src/include/commands/trigger.h b/src/include/commands/trigger.h
index ae7abd456bf..a2b69576093 100644
--- a/src/include/commands/trigger.h
+++ b/src/include/commands/trigger.h
@@ -152,11 +152,11 @@ extern PGDLLIMPORT int SessionReplicationRole;
#define TRIGGER_DISABLED 'D'
extern ObjectAddress CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
- Oid refRelOid, Oid constraintOid, Oid indexOid,
+ Oid constraintOid, Oid indexOid,
Oid funcoid, Oid parentTriggerOid, Node *whenClause,
bool isInternal, bool in_partition);
extern ObjectAddress CreateTriggerFiringOn(CreateTrigStmt *stmt, const char *queryString,
- Oid refRelOid, Oid constraintOid,
+ Oid constraintOid,
Oid indexOid, Oid funcoid, Oid parentTriggerOid,
Node *whenClause, bool isInternal, bool in_partition,
char trigger_fires_when);
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index f876f00ab9f..a3bf11e4fb7 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -3164,6 +3164,7 @@ typedef struct CreateTrigStmt
bool deferrable; /* [NOT] DEFERRABLE */
bool initdeferred; /* INITIALLY {DEFERRED|IMMEDIATE} */
RangeVar *constrrel; /* opposite relation, if RI trigger */
+ Oid constrrelOid; /* opposite relation Oid, if RI trigger */
} CreateTrigStmt;
/* ----------------------
--
2.34.1
From 4dbbbc191162cb2b61a220fb25ee35bad39c757d Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Mon, 29 Dec 2025 09:26:23 +0800
Subject: [PATCH v2 3/3] let SET DATA TYPE cope with trigger dependency
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
Previously, we can not change column data type if any trigger depend on it. This
is to removes that restriction.
The general workflow would be as follows: collect all affected triggers,
retrieve their definitions, drop the existing triggers, and then recreate them.
Any comments associated with the triggers will also be dropped and recreated.
Foreign key–related internal triggers are not directly dependent on the relation
itself; instead, they depend directly on the constraint. Therefore, we don't
need to worry about internal triggers here.
Trigger containing whole-row references is handled in thread:
(https://postgr.es/m/cacjufxga6kvqy7dbhglvw9s9kkmpgyzt5me6c7kefjdpr2w...@mail.gmail.com)
demo:
CREATE TABLE main_table (a int, b int);
CREATE FUNCTION trigger_func() RETURNS trigger LANGUAGE plpgsql AS '
BEGIN
RAISE NOTICE ''trigger_func(%) called: action = %, when = %, level = %'',
TG_ARGV[0], TG_OP, TG_WHEN, TG_LEVEL;
RETURN NULL;
END;';
CREATE TRIGGER modified_a BEFORE UPDATE OF a ON main_table
FOR EACH ROW WHEN (OLD.a <> NEW.a) EXECUTE PROCEDURE trigger_func('modified_a');
ALTER TABLE main_table ALTER COLUMN a SET DATA TYPE INT8;
discussion: https://postgr.es/m/cacjufxgkqyrmwmdvuoupet0443outgf_dkcpw3tfjiutfuy...@mail.gmail.com
commitfest: https://commitfest.postgresql.org/patch/6089
---
src/backend/catalog/index.c | 1 +
src/backend/commands/tablecmds.c | 152 ++++++++++++++++--
src/backend/commands/trigger.c | 55 +++++++
src/backend/parser/gram.y | 2 +
src/backend/utils/adt/ruleutils.c | 10 ++
src/include/commands/trigger.h | 1 +
src/include/nodes/parsenodes.h | 2 +
src/include/utils/ruleutils.h | 1 +
.../test_ddl_deparse/test_ddl_deparse.c | 3 +
src/test/regress/expected/foreign_data.out | 21 +++
src/test/regress/expected/triggers.out | 67 +++++++-
src/test/regress/sql/foreign_data.sql | 15 ++
src/test/regress/sql/triggers.sql | 23 ++-
13 files changed, 335 insertions(+), 18 deletions(-)
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index b3b034cf3b8..ff3e7c5feec 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -2041,6 +2041,7 @@ index_constraint_create(Relation heapRelation,
trigger->initdeferred = initdeferred;
trigger->constrrel = NULL;
trigger->constrrelOid = InvalidOid;
+ trigger->trigcomment = NULL;
(void) CreateTrigger(trigger, NULL,
conOid, indexRelationId, InvalidOid,
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index cae3d5218aa..64e0c26a34b 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -208,6 +208,8 @@ typedef struct AlteredTableInfo
char *clusterOnIndex; /* index to use for CLUSTER */
List *changedStatisticsOids; /* OIDs of statistics to rebuild */
List *changedStatisticsDefs; /* string definitions of same */
+ List *changedTriggerOids; /* OIDs of trigger to rebuild */
+ List *changedTriggerDefs; /* string definitions of same */
} AlteredTableInfo;
/* Struct describing one new constraint to check in Phase 3 scan */
@@ -546,6 +548,8 @@ static ObjectAddress ATExecAddIndex(AlteredTableInfo *tab, Relation rel,
IndexStmt *stmt, bool is_rebuild, LOCKMODE lockmode);
static ObjectAddress ATExecAddStatistics(AlteredTableInfo *tab, Relation rel,
CreateStatsStmt *stmt, bool is_rebuild, LOCKMODE lockmode);
+static ObjectAddress ATExecAddTrigger(AlteredTableInfo *tab, Relation rel, CreateTrigStmt *stmt,
+ bool is_rebuild, LOCKMODE lockmode);
static ObjectAddress ATExecAddConstraint(List **wqueue,
AlteredTableInfo *tab, Relation rel,
Constraint *newConstraint, bool recurse, bool is_readd,
@@ -651,6 +655,7 @@ static void RememberAllDependentForRebuilding(AlteredTableInfo *tab, AlterTableT
static void RememberConstraintForRebuilding(Oid conoid, AlteredTableInfo *tab);
static void RememberIndexForRebuilding(Oid indoid, AlteredTableInfo *tab);
static void RememberStatisticsForRebuilding(Oid stxoid, AlteredTableInfo *tab);
+static void RememberTriggerForRebuilding(Oid trigoid, AlteredTableInfo *tab);
static void ATPostAlterTypeCleanup(List **wqueue, AlteredTableInfo *tab,
LOCKMODE lockmode);
static void ATPostAlterTypeParse(Oid oldId, Oid oldRelId, Oid refRelId,
@@ -5464,6 +5469,10 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab,
address = ATExecAddStatistics(tab, rel, (CreateStatsStmt *) cmd->def,
true, lockmode);
break;
+ case AT_ReAddTrigger: /* ADD TRIGGER */
+ address = ATExecAddTrigger(tab, rel, castNode(CreateTrigStmt, cmd->def),
+ true, lockmode);
+ break;
case AT_AddConstraint: /* ADD CONSTRAINT */
/* Transform the command only during initial examination */
if (cur_pass == AT_PASS_ADD_CONSTR)
@@ -6751,6 +6760,8 @@ alter_table_type_to_string(AlterTableType cmdtype)
return "ALTER COLUMN ... DROP IDENTITY";
case AT_ReAddStatistics:
return NULL; /* not real grammar */
+ case AT_ReAddTrigger:
+ return NULL; /* not real grammar */
}
return NULL;
@@ -9723,6 +9734,27 @@ ATExecAddStatistics(AlteredTableInfo *tab, Relation rel,
return address;
}
+/*
+ * ALTER TABLE ADD TRIGGER
+ *
+ * This is no such command in the grammar, but we use this internally to add
+ * AT_ReAddTrigger subcommands to rebuild trigger after a table
+ * column type change.
+ */
+static ObjectAddress
+ATExecAddTrigger(AlteredTableInfo *tab, Relation rel,
+ CreateTrigStmt *stmt, bool is_rebuild, LOCKMODE lockmode)
+{
+ ObjectAddress address;
+
+ Assert(IsA(stmt, CreateTrigStmt));
+
+ address = CreateTrigger(castNode(CreateTrigStmt, stmt), NULL,
+ InvalidOid, InvalidOid, InvalidOid, InvalidOid,
+ NULL, false, false);
+ return address;
+}
+
/*
* ALTER TABLE ADD CONSTRAINT USING INDEX
*
@@ -13864,6 +13896,7 @@ CreateFKCheckTrigger(Oid myRelOid, Oid refRelOid, Constraint *fkconstraint,
fk_trigger->initdeferred = fkconstraint->initdeferred;
fk_trigger->constrrel = NULL;
fk_trigger->constrrelOid = refRelOid;
+ fk_trigger->trigcomment = NULL;
trigAddress = CreateTrigger(fk_trigger, NULL,
constraintOid, indexOid, InvalidOid,
@@ -13911,6 +13944,7 @@ createForeignKeyActionTriggers(Oid myRelOid, Oid refRelOid, Constraint *fkconstr
fk_trigger->transitionRels = NIL;
fk_trigger->constrrel = NULL;
fk_trigger->constrrelOid = myRelOid;
+ fk_trigger->trigcomment = NULL;
switch (fkconstraint->fk_del_action)
{
@@ -13973,6 +14007,7 @@ createForeignKeyActionTriggers(Oid myRelOid, Oid refRelOid, Constraint *fkconstr
fk_trigger->transitionRels = NIL;
fk_trigger->constrrel = NULL;
fk_trigger->constrrelOid = myRelOid;
+ fk_trigger->trigcomment = NULL;
switch (fkconstraint->fk_upd_action)
{
@@ -15184,21 +15219,13 @@ RememberAllDependentForRebuilding(AlteredTableInfo *tab, AlterTableType subtype,
case TriggerRelationId:
/*
- * A trigger can depend on a column because the column is
- * specified as an update target, or because the column is
- * used in the trigger's WHEN condition. The first case would
- * not require any extra work, but the second case would
- * require updating the WHEN expression, which has the same
- * issues as above. Since we can't easily tell which case
- * applies, we punt for both. FIXME someday.
+ * Internally-generated trigger for a constraint will have
+ * internal dependency of the constraint. It won't have direct
+ * dependency with the relation. So no need to worry about
+ * internal trigger here.
*/
if (subtype == AT_AlterColumnType)
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("cannot alter type of a column used in a trigger definition"),
- errdetail("%s depends on column \"%s\"",
- getObjectDescription(&foundObject, false),
- colName)));
+ RememberTriggerForRebuilding(foundObject.objectId, tab);
break;
case PolicyRelationId:
@@ -15459,6 +15486,33 @@ RememberStatisticsForRebuilding(Oid stxoid, AlteredTableInfo *tab)
}
}
+/*
+ * Subroutine for ATExecAlterColumnType: remember that a trigger object
+ * needs to be rebuilt (which we might already know).
+ */
+static void
+RememberTriggerForRebuilding(Oid trigoid, AlteredTableInfo *tab)
+{
+ /*
+ * This de-duplication check is critical for two independent reasons: we
+ * mustn't try to recreate the same trigger object twice, and if the
+ * trigger object depends on more than one column whose type is to be
+ * altered, we must capture its definition string before applying any of
+ * the type changes. ruleutils.c will get confused if we ask again later.
+ */
+ if (!list_member_oid(tab->changedTriggerOids, trigoid))
+ {
+ /* OK, capture the trigger object's existing definition string */
+ char *defstring = pg_get_triggerobjdef_string(trigoid);
+
+ tab->changedTriggerOids = lappend_oid(tab->changedTriggerOids,
+ trigoid);
+ tab->changedTriggerDefs = lappend(tab->changedTriggerDefs,
+ defstring);
+ }
+}
+
+
/*
* Cleanup after we've finished all the ALTER TYPE or SET EXPRESSION
* operations for a particular relation. We have to drop and recreate all the
@@ -15603,6 +15657,49 @@ ATPostAlterTypeCleanup(List **wqueue, AlteredTableInfo *tab, LOCKMODE lockmode)
add_exact_object_address(&obj, objects);
}
+ /* add dependencies for new triggers */
+ forboth(oid_item, tab->changedTriggerOids,
+ def_item, tab->changedTriggerDefs)
+ {
+ List *relids;
+ Oid refRelId = InvalidOid;
+ Oid oldId = lfirst_oid(oid_item);
+
+ relids = TriggerGetRelations(oldId);
+
+ Assert(relids != NIL);
+
+ /*
+ * As above, make sure we have lock on the trigger object's table if
+ * it's not the same table. However, we take ShareRowExclusiveLock
+ * here, aligning with the lock level used in CreateTriggerFiringOn.
+ *
+ * CAUTION: this should be done after all cases that grab
+ * AccessExclusiveLock, else we risk causing deadlock due to needing
+ * to promote our table lock.
+ */
+ foreach_oid(relid, relids)
+ {
+ if (relid != tab->relid)
+ LockRelationOid(relid, ShareRowExclusiveLock);
+ }
+
+ /*
+ * refRelId is the RI trigger opposite relation OID. It is passed to
+ * ATPostAlterTypeParse, where it will assigned to
+ * CreateTrigStmt.constrrelOid.
+ */
+ if (list_length(relids) == 2)
+ refRelId = lsecond_oid(relids);
+
+ ATPostAlterTypeParse(oldId, linitial_oid(relids), refRelId,
+ (char *) lfirst(def_item),
+ wqueue, lockmode, tab->rewrite);
+
+ ObjectAddressSet(obj, TriggerRelationId, oldId);
+ add_exact_object_address(&obj, objects);
+ }
+
/*
* Queue up command to restore replica identity index marking
*/
@@ -15651,9 +15748,9 @@ ATPostAlterTypeCleanup(List **wqueue, AlteredTableInfo *tab, LOCKMODE lockmode)
}
/*
- * Parse the previously-saved definition string for a constraint, index or
- * statistics object against the newly-established column data type(s), and
- * queue up the resulting command parsetrees for execution.
+ * Parse the previously-saved definition string for a constraint, index,
+ * statistics object or trigger against the newly-established column data
+ * type(s), and queue up the resulting command parsetrees for execution.
*
* This might fail if, for example, you have a WHERE clause that uses an
* operator that's not available for the new column type.
@@ -15704,6 +15801,15 @@ ATPostAlterTypeParse(Oid oldId, Oid oldRelId, Oid refRelId, char *cmd,
transformStatsStmt(oldRelId,
(CreateStatsStmt *) stmt,
cmd));
+ else if (IsA(stmt, CreateTrigStmt))
+ {
+ CreateTrigStmt *trigstmt = castNode(CreateTrigStmt, stmt);
+
+ trigstmt->relOid = oldRelId;
+ trigstmt->constrrelOid = refRelId;
+
+ querytree_list = lappend(querytree_list, trigstmt);
+ }
else
querytree_list = lappend(querytree_list, stmt);
}
@@ -15854,6 +15960,20 @@ ATPostAlterTypeParse(Oid oldId, Oid oldRelId, Oid refRelId, char *cmd,
tab->subcmds[AT_PASS_MISC] =
lappend(tab->subcmds[AT_PASS_MISC], newcmd);
}
+ else if (IsA(stm, CreateTrigStmt))
+ {
+ CreateTrigStmt *stmt = (CreateTrigStmt *) stm;
+ AlterTableCmd *newcmd;
+
+ /* keep the trigger object's comment */
+ stmt->trigcomment = GetComment(oldId, TriggerRelationId, 0);
+
+ newcmd = makeNode(AlterTableCmd);
+ newcmd->subtype = AT_ReAddTrigger;
+ newcmd->def = (Node *) stmt;
+ tab->subcmds[AT_PASS_MISC] =
+ lappend(tab->subcmds[AT_PASS_MISC], newcmd);
+ }
else
elog(ERROR, "unexpected statement type: %d",
(int) nodeTag(stm));
diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c
index 18cd3cc41b3..132b8f1b254 100644
--- a/src/backend/commands/trigger.c
+++ b/src/backend/commands/trigger.c
@@ -30,6 +30,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 "miscadmin.h"
@@ -1198,6 +1199,11 @@ CreateTriggerFiringOn(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;
}
@@ -1407,6 +1413,55 @@ get_trigger_oid(Oid relid, const char *trigname, bool missing_ok)
return oid;
}
+ /*
+ * TriggerGetRelations
+ *
+ * Collect all relations this trigger depends on. The constraint trigger may
+ * reference another relation, we include it as well.
+ */
+List *
+TriggerGetRelations(Oid trigId)
+{
+ HeapTuple ht_trig;
+ Form_pg_trigger trigrec;
+ Relation tgrel;
+ ScanKeyData skey[1];
+ SysScanDesc tgscan;
+ List *result = NIL;
+
+ /*
+ * find the pg_trigger tuple by the Oid of the trigger
+ */
+ tgrel = table_open(TriggerRelationId, AccessShareLock);
+
+ ScanKeyInit(&skey[0],
+ Anum_pg_trigger_oid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(trigId));
+ tgscan = systable_beginscan(tgrel, TriggerOidIndexId, true,
+ NULL, 1, skey);
+ ht_trig = systable_getnext(tgscan);
+
+ if (HeapTupleIsValid(ht_trig))
+ {
+ trigrec = (Form_pg_trigger) GETSTRUCT(ht_trig);
+
+ Assert(trigrec->oid = trigId);
+
+ result = lappend_oid(result, trigrec->tgrelid);
+
+ if (OidIsValid(trigrec->tgconstrrelid))
+ result = lappend_oid(result, trigrec->tgconstrrelid);
+ }
+
+ /* Clean up */
+ systable_endscan(tgscan);
+
+ table_close(tgrel, AccessShareLock);
+
+ return result;
+}
+
/*
* Perform permissions and integrity checks before acquiring a relation lock.
*/
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 4a0bab33e70..8cde1e5ffff 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -6152,6 +6152,7 @@ CreateTrigStmt:
n->initdeferred = false;
n->constrrel = NULL;
n->constrrelOid = InvalidOid;
+ n->trigcomment = NULL;
$$ = (Node *) n;
}
| CREATE opt_or_replace CONSTRAINT TRIGGER name AFTER TriggerEvents ON
@@ -6204,6 +6205,7 @@ CreateTrigStmt:
NULL, NULL, yyscanner);
n->constrrel = $10;
n->constrrelOid = InvalidOid;
+ n->trigcomment = NULL;
$$ = (Node *) n;
}
;
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 9f85eb86da1..2339827b225 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -881,6 +881,16 @@ pg_get_triggerdef(PG_FUNCTION_ARGS)
PG_RETURN_TEXT_P(string_to_text(res));
}
+/*
+ * Internal version for use by ALTER TABLE.
+ * Returns a palloc'd C string; no pretty-printing.
+ */
+char *
+pg_get_triggerobjdef_string(Oid trigid)
+{
+ return pg_get_triggerdef_worker(trigid, false);
+}
+
Datum
pg_get_triggerdef_ext(PG_FUNCTION_ARGS)
{
diff --git a/src/include/commands/trigger.h b/src/include/commands/trigger.h
index a2b69576093..7134441f042 100644
--- a/src/include/commands/trigger.h
+++ b/src/include/commands/trigger.h
@@ -167,6 +167,7 @@ extern void TriggerSetParentTrigger(Relation trigRel,
Oid childTableId);
extern void RemoveTriggerById(Oid trigOid);
extern Oid get_trigger_oid(Oid relid, const char *trigname, bool missing_ok);
+extern List *TriggerGetRelations(Oid trigId);
extern ObjectAddress renametrig(RenameStmt *stmt);
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index a3bf11e4fb7..20fd5585c08 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2508,6 +2508,7 @@ typedef enum AlterTableType
AT_SetIdentity, /* SET identity column options */
AT_DropIdentity, /* DROP IDENTITY */
AT_ReAddStatistics, /* internal to commands/tablecmds.c */
+ AT_ReAddTrigger, /* internal to commands/tablecmds.c */
} AlterTableType;
typedef struct AlterTableCmd /* one subcommand of an ALTER TABLE */
@@ -3165,6 +3166,7 @@ typedef struct CreateTrigStmt
bool initdeferred; /* INITIALLY {DEFERRED|IMMEDIATE} */
RangeVar *constrrel; /* opposite relation, if RI trigger */
Oid constrrelOid; /* opposite relation Oid, if RI trigger */
+ char *trigcomment; /* comment to apply to trigger, or NULL */
} CreateTrigStmt;
/* ----------------------
diff --git a/src/include/utils/ruleutils.h b/src/include/utils/ruleutils.h
index 7ba7d887914..1bb89b57493 100644
--- a/src/include/utils/ruleutils.h
+++ b/src/include/utils/ruleutils.h
@@ -53,5 +53,6 @@ extern char *generate_opclass_name(Oid opclass);
extern char *get_range_partbound_string(List *bound_datums);
extern char *pg_get_statisticsobjdef_string(Oid statextid);
+extern char *pg_get_triggerobjdef_string(Oid trigid);
#endif /* RULEUTILS_H */
diff --git a/src/test/modules/test_ddl_deparse/test_ddl_deparse.c b/src/test/modules/test_ddl_deparse/test_ddl_deparse.c
index 17d72e412ff..46e81b15f5e 100644
--- a/src/test/modules/test_ddl_deparse/test_ddl_deparse.c
+++ b/src/test/modules/test_ddl_deparse/test_ddl_deparse.c
@@ -314,6 +314,9 @@ get_altertable_subcmdinfo(PG_FUNCTION_ARGS)
case AT_ReAddStatistics:
strtype = "(re) ADD STATS";
break;
+ case AT_ReAddTrigger:
+ strtype = "(re) ADD TRIGGER";
+ break;
}
if (subcmd->recurse)
diff --git a/src/test/regress/expected/foreign_data.out b/src/test/regress/expected/foreign_data.out
index cce49e509ab..d80143fafef 100644
--- a/src/test/regress/expected/foreign_data.out
+++ b/src/test/regress/expected/foreign_data.out
@@ -1394,6 +1394,27 @@ ALTER FOREIGN TABLE foreign_schema.foreign_table_1
DISABLE TRIGGER trigtest_before_stmt;
ALTER FOREIGN TABLE foreign_schema.foreign_table_1
ENABLE TRIGGER trigtest_before_stmt;
+CREATE TRIGGER trigtest_before_rowwhen BEFORE INSERT OR UPDATE
+ON foreign_schema.foreign_table_1
+FOR EACH ROW
+WHEN (NEW.c7 = 0)
+EXECUTE PROCEDURE dummy_trigger();
+ALTER FOREIGN TABLE foreign_schema.foreign_table_1 ALTER COLUMN c7 SET DATA TYPE text; --error
+ERROR: operator does not exist: text = integer
+DETAIL: No operator of that name accepts the given argument types.
+HINT: You might need to add explicit type casts.
+ALTER FOREIGN TABLE foreign_schema.foreign_table_1 ALTER COLUMN c7 SET DATA TYPE numeric;
+SELECT pg_get_triggerdef(oid, true)
+FROM pg_trigger
+WHERE tgrelid = 'foreign_schema.foreign_table_1'::regclass
+AND tgname = 'trigtest_before_rowwhen';
+ pg_get_triggerdef
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE TRIGGER trigtest_before_rowwhen BEFORE INSERT OR UPDATE ON foreign_schema.foreign_table_1 FOR EACH ROW WHEN (new.c7 = 0::numeric) EXECUTE FUNCTION dummy_trigger()
+(1 row)
+
+ALTER FOREIGN TABLE foreign_schema.foreign_table_1 ALTER COLUMN c7 SET DATA TYPE int;
+DROP TRIGGER trigtest_before_rowwhen ON foreign_schema.foreign_table_1;
DROP TRIGGER trigtest_before_stmt ON foreign_schema.foreign_table_1;
DROP TRIGGER trigtest_before_row ON foreign_schema.foreign_table_1;
DROP TRIGGER trigtest_after_stmt ON foreign_schema.foreign_table_1;
diff --git a/src/test/regress/expected/triggers.out b/src/test/regress/expected/triggers.out
index 1eb8fba0953..b2c90ea8842 100644
--- a/src/test/regress/expected/triggers.out
+++ b/src/test/regress/expected/triggers.out
@@ -241,12 +241,14 @@ CREATE TRIGGER insert_when BEFORE INSERT ON main_table
FOR EACH STATEMENT WHEN (true) EXECUTE PROCEDURE trigger_func('insert_when');
CREATE TRIGGER delete_when AFTER DELETE ON main_table
FOR EACH STATEMENT WHEN (true) EXECUTE PROCEDURE trigger_func('delete_when');
+PREPARE get_trigger_info(text[]) AS
SELECT trigger_name, event_manipulation, event_object_schema, event_object_table,
action_order, action_condition, action_orientation, action_timing,
action_reference_old_table, action_reference_new_table
FROM information_schema.triggers
- WHERE event_object_table IN ('main_table')
+ WHERE event_object_table = ANY ($1)
ORDER BY trigger_name COLLATE "C", 2;
+EXECUTE get_trigger_info('{main_table}');
trigger_name | event_manipulation | event_object_schema | event_object_table | action_order | action_condition | action_orientation | action_timing | action_reference_old_table | action_reference_new_table
----------------------+--------------------+---------------------+--------------------+--------------+--------------------------------+--------------------+---------------+----------------------------+----------------------------
after_ins_stmt_trig | INSERT | public | main_table | 1 | | STATEMENT | AFTER | |
@@ -261,6 +263,34 @@ SELECT trigger_name, event_manipulation, event_object_schema, event_object_table
modified_any | UPDATE | public | main_table | 2 | (old.* IS DISTINCT FROM new.*) | ROW | BEFORE | |
(10 rows)
+COMMENT ON TRIGGER modified_a ON main_table IS 'modified_a trigger';
+ALTER TABLE main_table ALTER COLUMN a SET DATA TYPE TEXT; --error
+ERROR: operator does not exist: text = integer
+DETAIL: No operator of that name accepts the given argument types.
+HINT: You might need to add explicit type casts.
+ALTER TABLE main_table ALTER COLUMN a SET DATA TYPE numeric;
+EXECUTE get_trigger_info('{main_table}');
+ trigger_name | event_manipulation | event_object_schema | event_object_table | action_order | action_condition | action_orientation | action_timing | action_reference_old_table | action_reference_new_table
+----------------------+--------------------+---------------------+--------------------+--------------+--------------------------------+--------------------+---------------+----------------------------+----------------------------
+ after_ins_stmt_trig | INSERT | public | main_table | 1 | | STATEMENT | AFTER | |
+ after_upd_row_trig | UPDATE | public | main_table | 1 | | ROW | AFTER | |
+ after_upd_stmt_trig | UPDATE | public | main_table | 1 | | STATEMENT | AFTER | |
+ before_ins_stmt_trig | INSERT | public | main_table | 1 | | STATEMENT | BEFORE | |
+ delete_a | DELETE | public | main_table | 1 | (old.a = (123)::numeric) | ROW | AFTER | |
+ delete_when | DELETE | public | main_table | 1 | true | STATEMENT | AFTER | |
+ insert_a | INSERT | public | main_table | 1 | (new.a = (123)::numeric) | ROW | AFTER | |
+ insert_when | INSERT | public | main_table | 2 | true | STATEMENT | BEFORE | |
+ modified_a | UPDATE | public | main_table | 1 | (old.a <> new.a) | ROW | BEFORE | |
+ modified_any | UPDATE | public | main_table | 2 | (old.* IS DISTINCT FROM new.*) | ROW | BEFORE | |
+(10 rows)
+
+\dd modified_a
+ Object descriptions
+ Schema | Name | Object | Description
+--------+------------+---------+--------------------
+ public | modified_a | trigger | modified_a trigger
+(1 row)
+
INSERT INTO main_table (a) VALUES (123), (456);
NOTICE: trigger_func(before_ins_stmt) called: action = INSERT, when = BEFORE, level = STATEMENT
NOTICE: trigger_func(insert_when) called: action = INSERT, when = BEFORE, level = STATEMENT
@@ -298,6 +328,13 @@ SELECT * FROM main_table ORDER BY a, b;
|
(8 rows)
+SELECT pg_get_triggerdef(oid, true) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'insert_a';
+ pg_get_triggerdef
+---------------------------------------------------------------------------------------------------------------------------------------
+ CREATE TRIGGER insert_a AFTER INSERT ON main_table FOR EACH ROW WHEN (new.a = 123::numeric) EXECUTE FUNCTION trigger_func('insert_a')
+(1 row)
+
+ALTER TABLE main_table ALTER COLUMN a SET DATA TYPE INT;
SELECT pg_get_triggerdef(oid, true) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'modified_a';
pg_get_triggerdef
-------------------------------------------------------------------------------------------------------------------------------------------
@@ -343,6 +380,13 @@ create trigger oid_unchanged_trig after update on table_with_oids
for each row
when (new.tableoid = old.tableoid AND new.tableoid <> 0)
execute procedure trigger_func('after_upd_oid_unchanged');
+alter table table_with_oids alter column a set data type numeric;
+execute get_trigger_info('{table_with_oids}');
+ trigger_name | event_manipulation | event_object_schema | event_object_table | action_order | action_condition | action_orientation | action_timing | action_reference_old_table | action_reference_new_table
+--------------------+--------------------+---------------------+--------------------+--------------+----------------------------------------------------------------+--------------------+---------------+----------------------------+----------------------------
+ oid_unchanged_trig | UPDATE | public | table_with_oids | 1 | ((new.tableoid = old.tableoid) AND (new.tableoid <> (0)::oid)) | ROW | AFTER | |
+(1 row)
+
update table_with_oids set a = a + 1;
NOTICE: trigger_func(after_upd_oid_unchanged) called: action = UPDATE, when = AFTER, level = ROW
drop table table_with_oids;
@@ -2165,6 +2209,14 @@ insert into parted_irreg_ancestor values ('aasvogel', 3);
NOTICE: aasvogel <- woof!
NOTICE: trigger parted_trig on parted1_irreg AFTER INSERT for ROW: (a,b)=(3,aasvogel)
NOTICE: trigger parted_trig_odd on parted1_irreg AFTER INSERT for ROW: (a,b)=(3,aasvogel)
+alter table parted_irreg_ancestor alter column a set data type numeric;
+execute get_trigger_info('{parted_irreg_ancestor, parted1_irreg}');
+ trigger_name | event_manipulation | event_object_schema | event_object_table | action_order | action_condition | action_orientation | action_timing | action_reference_old_table | action_reference_new_table
+-----------------+--------------------+---------------------+--------------------+--------------+-----------------------------------------------------------+--------------------+---------------+----------------------------+----------------------------
+ parted_trig | INSERT | public | parted1_irreg | 1 | | ROW | AFTER | |
+ parted_trig_odd | INSERT | public | parted1_irreg | 2 | (bark(new.b) AND ((new.a % (2)::numeric) = (1)::numeric)) | ROW | AFTER | |
+(2 rows)
+
drop table parted_irreg_ancestor;
-- Before triggers and partitions
create table parted (a int, b int, c text) partition by list (a);
@@ -2318,6 +2370,19 @@ 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();
+alter table parted_constr_ancestor alter column a set data type numeric;
+execute get_trigger_info('{parted_constr_ancestor, parted_constr, parted_constr_ancestor, parted1_constr}');
+ trigger_name | event_manipulation | event_object_schema | event_object_table | action_order | action_condition | action_orientation | action_timing | action_reference_old_table | action_reference_new_table
+-----------------+--------------------+---------------------+------------------------+--------------+-----------------------------------------------------------+--------------------+---------------+----------------------------+----------------------------
+ parted_trig | INSERT | public | parted1_constr | 1 | | ROW | AFTER | |
+ parted_trig | INSERT | public | parted_constr | 1 | | ROW | AFTER | |
+ parted_trig | INSERT | public | parted_constr_ancestor | 1 | | ROW | AFTER | |
+ parted_trig_two | INSERT | public | parted1_constr | 2 | (bark(new.b) AND ((new.a % (2)::numeric) = (1)::numeric)) | ROW | AFTER | |
+ parted_trig_two | INSERT | public | parted_constr | 2 | (bark(new.b) AND ((new.a % (2)::numeric) = (1)::numeric)) | ROW | AFTER | |
+(5 rows)
+
+alter table parted_constr_ancestor alter column a set data type int;
+deallocate get_trigger_info;
-- 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.
diff --git a/src/test/regress/sql/foreign_data.sql b/src/test/regress/sql/foreign_data.sql
index aa147b14a90..bbd87bf4816 100644
--- a/src/test/regress/sql/foreign_data.sql
+++ b/src/test/regress/sql/foreign_data.sql
@@ -625,6 +625,21 @@ ALTER FOREIGN TABLE foreign_schema.foreign_table_1
ALTER FOREIGN TABLE foreign_schema.foreign_table_1
ENABLE TRIGGER trigtest_before_stmt;
+CREATE TRIGGER trigtest_before_rowwhen BEFORE INSERT OR UPDATE
+ON foreign_schema.foreign_table_1
+FOR EACH ROW
+WHEN (NEW.c7 = 0)
+EXECUTE PROCEDURE dummy_trigger();
+ALTER FOREIGN TABLE foreign_schema.foreign_table_1 ALTER COLUMN c7 SET DATA TYPE text; --error
+ALTER FOREIGN TABLE foreign_schema.foreign_table_1 ALTER COLUMN c7 SET DATA TYPE numeric;
+
+SELECT pg_get_triggerdef(oid, true)
+FROM pg_trigger
+WHERE tgrelid = 'foreign_schema.foreign_table_1'::regclass
+AND tgname = 'trigtest_before_rowwhen';
+ALTER FOREIGN TABLE foreign_schema.foreign_table_1 ALTER COLUMN c7 SET DATA TYPE int;
+
+DROP TRIGGER trigtest_before_rowwhen ON foreign_schema.foreign_table_1;
DROP TRIGGER trigtest_before_stmt ON foreign_schema.foreign_table_1;
DROP TRIGGER trigtest_before_row ON foreign_schema.foreign_table_1;
DROP TRIGGER trigtest_after_stmt ON foreign_schema.foreign_table_1;
diff --git a/src/test/regress/sql/triggers.sql b/src/test/regress/sql/triggers.sql
index 5f7f75d7ba5..83e93525c4a 100644
--- a/src/test/regress/sql/triggers.sql
+++ b/src/test/regress/sql/triggers.sql
@@ -174,12 +174,22 @@ CREATE TRIGGER insert_when BEFORE INSERT ON main_table
FOR EACH STATEMENT WHEN (true) EXECUTE PROCEDURE trigger_func('insert_when');
CREATE TRIGGER delete_when AFTER DELETE ON main_table
FOR EACH STATEMENT WHEN (true) EXECUTE PROCEDURE trigger_func('delete_when');
+PREPARE get_trigger_info(text[]) AS
SELECT trigger_name, event_manipulation, event_object_schema, event_object_table,
action_order, action_condition, action_orientation, action_timing,
action_reference_old_table, action_reference_new_table
FROM information_schema.triggers
- WHERE event_object_table IN ('main_table')
+ WHERE event_object_table = ANY ($1)
ORDER BY trigger_name COLLATE "C", 2;
+
+EXECUTE get_trigger_info('{main_table}');
+COMMENT ON TRIGGER modified_a ON main_table IS 'modified_a trigger';
+
+ALTER TABLE main_table ALTER COLUMN a SET DATA TYPE TEXT; --error
+ALTER TABLE main_table ALTER COLUMN a SET DATA TYPE numeric;
+EXECUTE get_trigger_info('{main_table}');
+\dd modified_a
+
INSERT INTO main_table (a) VALUES (123), (456);
COPY main_table FROM stdin;
123 999
@@ -188,6 +198,8 @@ COPY main_table FROM stdin;
DELETE FROM main_table WHERE a IN (123, 456);
UPDATE main_table SET a = 50, b = 60;
SELECT * FROM main_table ORDER BY a, b;
+SELECT pg_get_triggerdef(oid, true) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'insert_a';
+ALTER TABLE main_table ALTER COLUMN a SET DATA TYPE INT;
SELECT pg_get_triggerdef(oid, true) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'modified_a';
SELECT pg_get_triggerdef(oid, false) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'modified_a';
SELECT pg_get_triggerdef(oid, true) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'modified_any';
@@ -211,6 +223,8 @@ create trigger oid_unchanged_trig after update on table_with_oids
for each row
when (new.tableoid = old.tableoid AND new.tableoid <> 0)
execute procedure trigger_func('after_upd_oid_unchanged');
+alter table table_with_oids alter column a set data type numeric;
+execute get_trigger_info('{table_with_oids}');
update table_with_oids set a = a + 1;
drop table table_with_oids;
@@ -1495,6 +1509,8 @@ create trigger parted_trig_odd after insert on parted_irreg for each row
insert into parted_irreg values (1, 'aardvark'), (2, 'aanimals');
insert into parted1_irreg values ('aardwolf', 2);
insert into parted_irreg_ancestor values ('aasvogel', 3);
+alter table parted_irreg_ancestor alter column a set data type numeric;
+execute get_trigger_info('{parted_irreg_ancestor, parted1_irreg}');
drop table parted_irreg_ancestor;
-- Before triggers and partitions
@@ -1608,6 +1624,11 @@ 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();
+alter table parted_constr_ancestor alter column a set data type numeric;
+execute get_trigger_info('{parted_constr_ancestor, parted_constr, parted_constr_ancestor, parted1_constr}');
+alter table parted_constr_ancestor alter column a set data type int;
+deallocate get_trigger_info;
+
-- 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.
--
2.34.1
