hi.
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;
ERROR: cannot alter type of a column used in a trigger definition
DETAIL: trigger modified_a on table main_table depends on column "a"
With the attached patch, the previous error will no longer occur.
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 in this context.
v1-0001: "refactor CreateTrigger and CreateTriggerFiringOn".
used also in
https://postgr.es/m/cacjufxhjar2fjbeb6ghg_-n5dxx5jvnjkslouxoyt4teaaw...@mail.gmail.com
v1-0002, the actual implementation.
From 41337dcde4b5a76eb224aff12e006fc430b9e5e1 Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Thu, 25 Sep 2025 16:41:36 +0800
Subject: [PATCH v1 1/2] refactor CreateTrigger and CreateTriggerFiringOn
discussion: https://postgr.es/m/
---
src/backend/catalog/index.c | 3 +-
src/backend/commands/tablecmds.c | 14 ++-
src/backend/commands/trigger.c | 180 ++++++++---------------------
src/backend/parser/gram.y | 2 +
src/backend/parser/parse_utilcmd.c | 144 +++++++++++++++++++++++
src/backend/tcop/utility.c | 2 +-
src/include/commands/trigger.h | 4 +-
src/include/nodes/parsenodes.h | 1 +
src/include/parser/parse_utilcmd.h | 2 +
9 files changed, 212 insertions(+), 140 deletions(-)
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index 5d9db167e59..8d09d61cae2 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -2039,10 +2039,11 @@ index_constraint_create(Relation heapRelation,
trigger->deferrable = true;
trigger->initdeferred = initdeferred;
trigger->constrrel = NULL;
+ trigger->transformed = true;
(void) CreateTrigger(trigger, NULL, RelationGetRelid(heapRelation),
InvalidOid, conOid, indexRelationId, InvalidOid,
- InvalidOid, NULL, true, false);
+ InvalidOid, true, false);
}
/*
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index fc89352b661..bf8120c12f2 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -13802,10 +13802,11 @@ CreateFKCheckTrigger(Oid myRelOid, Oid refRelOid, Constraint *fkconstraint,
fk_trigger->deferrable = fkconstraint->deferrable;
fk_trigger->initdeferred = fkconstraint->initdeferred;
fk_trigger->constrrel = NULL;
+ fk_trigger->transformed = true;
trigAddress = CreateTrigger(fk_trigger, NULL, myRelOid, refRelOid,
constraintOid, indexOid, InvalidOid,
- parentTrigOid, NULL, true, false);
+ parentTrigOid, true, false);
/* Make changes-so-far visible */
CommandCounterIncrement();
@@ -13847,6 +13848,7 @@ createForeignKeyActionTriggers(Oid myRelOid, Oid refRelOid, Constraint *fkconstr
fk_trigger->whenClause = NULL;
fk_trigger->transitionRels = NIL;
fk_trigger->constrrel = NULL;
+ fk_trigger->transformed = true;
switch (fkconstraint->fk_del_action)
{
@@ -13883,7 +13885,7 @@ createForeignKeyActionTriggers(Oid myRelOid, Oid refRelOid, Constraint *fkconstr
trigAddress = CreateTrigger(fk_trigger, NULL, refRelOid, myRelOid,
constraintOid, indexOid, InvalidOid,
- parentDelTrigger, NULL, true, false);
+ parentDelTrigger, true, false);
if (deleteTrigOid)
*deleteTrigOid = trigAddress.objectId;
@@ -13907,6 +13909,7 @@ createForeignKeyActionTriggers(Oid myRelOid, Oid refRelOid, Constraint *fkconstr
fk_trigger->whenClause = NULL;
fk_trigger->transitionRels = NIL;
fk_trigger->constrrel = NULL;
+ fk_trigger->transformed = true;
switch (fkconstraint->fk_upd_action)
{
@@ -13943,7 +13946,7 @@ createForeignKeyActionTriggers(Oid myRelOid, Oid refRelOid, Constraint *fkconstr
trigAddress = CreateTrigger(fk_trigger, NULL, refRelOid, myRelOid,
constraintOid, indexOid, InvalidOid,
- parentUpdTrigger, NULL, true, false);
+ parentUpdTrigger, true, false);
if (updateTrigOid)
*updateTrigOid = trigAddress.objectId;
}
@@ -20823,15 +20826,16 @@ CloneRowTriggersToPartition(Relation parent, Relation partition)
trigStmt->timing = trigForm->tgtype & TRIGGER_TYPE_TIMING_MASK;
trigStmt->events = trigForm->tgtype & TRIGGER_TYPE_EVENT_MASK;
trigStmt->columns = cols;
- trigStmt->whenClause = NULL; /* passed separately */
+ trigStmt->whenClause = qual;
trigStmt->transitionRels = NIL; /* not supported at present */
trigStmt->deferrable = trigForm->tgdeferrable;
trigStmt->initdeferred = trigForm->tginitdeferred;
trigStmt->constrrel = NULL; /* passed separately */
+ trigStmt->transformed = true; /* whenClause alerady transformed */
CreateTriggerFiringOn(trigStmt, NULL, RelationGetRelid(partition),
trigForm->tgconstrrelid, InvalidOid, InvalidOid,
- trigForm->tgfoid, trigForm->oid, qual,
+ trigForm->tgfoid, trigForm->oid,
false, true, trigForm->tgenabled);
MemoryContextSwitchTo(oldcxt);
diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c
index 579ac8d76ae..f1431d99a56 100644
--- a/src/backend/commands/trigger.c
+++ b/src/backend/commands/trigger.c
@@ -40,6 +40,7 @@
#include "parser/parse_collate.h"
#include "parser/parse_func.h"
#include "parser/parse_relation.h"
+#include "parser/parse_utilcmd.h"
#include "partitioning/partdesc.h"
#include "pgstat.h"
#include "rewrite/rewriteHandler.h"
@@ -139,9 +140,6 @@ static HeapTuple check_modified_virtual_generated(TupleDesc tupdesc, HeapTuple t
* create the trigger on partitions, 2) when creating child foreign key
* triggers; see CreateFKCheckTrigger() and createForeignKeyActionTriggers().
*
- * If whenClause is passed, it is an already-transformed expression for
- * WHEN. In this case, we ignore any that may come in stmt->whenClause.
- *
* If isInternal is true then this is an internally-generated trigger.
* This argument sets the tgisinternal field of the pg_trigger entry, and
* if true causes us to modify the given trigger name to ensure uniqueness.
@@ -159,13 +157,13 @@ 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 funcoid, Oid parentTriggerOid, Node *whenClause,
+ Oid funcoid, Oid parentTriggerOid,
bool isInternal, bool in_partition)
{
return
CreateTriggerFiringOn(stmt, queryString, relOid, refRelOid,
constraintOid, indexOid, funcoid,
- parentTriggerOid, whenClause, isInternal,
+ parentTriggerOid, isInternal,
in_partition, TRIGGER_FIRES_ON_ORIGIN);
}
@@ -177,15 +175,15 @@ ObjectAddress
CreateTriggerFiringOn(CreateTrigStmt *stmt, const char *queryString,
Oid relOid, Oid refRelOid, Oid constraintOid,
Oid indexOid, Oid funcoid, Oid parentTriggerOid,
- Node *whenClause, bool isInternal, bool in_partition,
+ bool isInternal, bool in_partition,
char trigger_fires_when)
{
int16 tgtype;
int ncolumns;
int16 *columns;
int2vector *tgattr;
- List *whenRtable;
- char *qual;
+ List *whenRtable = NIL;
+ char *qual = NULL;
Datum values[Natts_pg_trigger];
bool nulls[Natts_pg_trigger];
Relation rel;
@@ -207,6 +205,7 @@ CreateTriggerFiringOn(CreateTrigStmt *stmt, const char *queryString,
Oid existing_constraint_oid = InvalidOid;
bool existing_isInternal = false;
bool existing_isClone = false;
+ Node *whenClause = NULL;
if (OidIsValid(relOid))
rel = table_open(relOid, ShareRowExclusiveLock);
@@ -557,133 +556,21 @@ CreateTriggerFiringOn(CreateTrigStmt *stmt, const char *queryString,
/*
* Parse the WHEN clause, if any and we weren't passed an already
* transformed one.
- *
- * Note that as a side effect, we fill whenRtable when parsing. If we got
- * an already parsed clause, this does not occur, which is what we want --
- * no point in adding redundant dependencies below.
*/
- if (!whenClause && stmt->whenClause)
+ if (stmt->whenClause)
{
- ParseState *pstate;
- ParseNamespaceItem *nsitem;
- List *varList;
- ListCell *lc;
-
- /* Set up a pstate to parse with */
- pstate = make_parsestate(NULL);
- pstate->p_sourcetext = queryString;
-
- /*
- * Set up nsitems for OLD and NEW references.
- *
- * 'OLD' must always have varno equal to 1 and 'NEW' equal to 2.
- */
- nsitem = addRangeTableEntryForRelation(pstate, rel,
- AccessShareLock,
- makeAlias("old", NIL),
- false, false);
- addNSItemToQuery(pstate, nsitem, false, true, true);
- nsitem = addRangeTableEntryForRelation(pstate, rel,
- AccessShareLock,
- makeAlias("new", NIL),
- 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);
-
- /*
- * Check for disallowed references to OLD/NEW.
- *
- * NB: pull_var_clause is okay here only because we don't allow
- * subselects in WHEN clauses; it would fail to examine the contents
- * of subselects.
- */
- varList = pull_var_clause(whenClause, 0);
- foreach(lc, varList)
+ if (!stmt->transformed)
{
- Var *var = (Var *) lfirst(lc);
+ stmt = transformCreateTriggerStmt(RelationGetRelid(rel), stmt,
+ queryString);
- switch (var->varno)
- {
- case PRS2_OLD_VARNO:
- if (!TRIGGER_FOR_ROW(tgtype))
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
- errmsg("statement trigger's WHEN condition cannot reference column values"),
- parser_errposition(pstate, var->location)));
- if (TRIGGER_FOR_INSERT(tgtype))
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
- errmsg("INSERT trigger's WHEN condition cannot reference OLD values"),
- parser_errposition(pstate, var->location)));
- /* system columns are okay here */
- break;
- case PRS2_NEW_VARNO:
- if (!TRIGGER_FOR_ROW(tgtype))
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
- errmsg("statement trigger's WHEN condition cannot reference column values"),
- parser_errposition(pstate, var->location)));
- if (TRIGGER_FOR_DELETE(tgtype))
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
- errmsg("DELETE trigger's WHEN condition cannot reference NEW values"),
- parser_errposition(pstate, var->location)));
- if (var->varattno < 0 && TRIGGER_FOR_BEFORE(tgtype))
- ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("BEFORE trigger's WHEN condition cannot reference NEW system columns"),
- parser_errposition(pstate, var->location)));
- if (TRIGGER_FOR_BEFORE(tgtype) &&
- var->varattno == 0 &&
- RelationGetDescr(rel)->constr &&
- (RelationGetDescr(rel)->constr->has_generated_stored ||
- RelationGetDescr(rel)->constr->has_generated_virtual))
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
- errmsg("BEFORE trigger's WHEN condition cannot reference NEW generated columns"),
- errdetail("A whole-row reference is used and the table contains generated columns."),
- parser_errposition(pstate, var->location)));
- if (TRIGGER_FOR_BEFORE(tgtype) &&
- var->varattno > 0 &&
- TupleDescAttr(RelationGetDescr(rel), var->varattno - 1)->attgenerated)
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
- errmsg("BEFORE trigger's WHEN condition cannot reference NEW generated columns"),
- errdetail("Column \"%s\" is a generated column.",
- NameStr(TupleDescAttr(RelationGetDescr(rel), var->varattno - 1)->attname)),
- parser_errposition(pstate, var->location)));
- break;
- default:
- /* can't happen without add_missing_from, so just elog */
- elog(ERROR, "trigger WHEN condition cannot contain references to other relations");
- break;
- }
+ whenClause = stmt->whenClause;
+ Assert(whenClause != NULL);
}
+ else
+ whenClause = stmt->whenClause;
- /* we'll need the rtable for recordDependencyOnExpr */
- whenRtable = pstate->p_rtable;
-
- qual = nodeToString(whenClause);
-
- free_parsestate(pstate);
- }
- else if (!whenClause)
- {
- whenClause = NULL;
- whenRtable = NIL;
- qual = NULL;
- }
- else
- {
qual = nodeToString(whenClause);
- whenRtable = NIL;
}
/*
@@ -1129,10 +1016,40 @@ CreateTriggerFiringOn(CreateTrigStmt *stmt, const char *queryString,
* If it has a WHEN clause, add dependencies on objects mentioned in the
* expression (eg, functions, as well as any columns used).
*/
- if (whenRtable != NIL)
+ if (whenClause != NULL)
+ {
+ ParseState *pstate;
+ ParseNamespaceItem *nsitem;
+
+ /* Set up a pstate to parse with */
+ pstate = make_parsestate(NULL);
+ pstate->p_sourcetext = queryString;
+
+ /*
+ * Set up nsitems for OLD and NEW references.
+ *
+ * 'OLD' must always have varno equal to 1 and 'NEW' equal to 2.
+ */
+ nsitem = addRangeTableEntryForRelation(pstate, rel,
+ AccessShareLock,
+ makeAlias("old", NIL),
+ false, false);
+ addNSItemToQuery(pstate, nsitem, false, true, true);
+
+ nsitem = addRangeTableEntryForRelation(pstate, rel,
+ AccessShareLock,
+ makeAlias("new", NIL),
+ false, false);
+ addNSItemToQuery(pstate, nsitem, false, true, true);
+
+ /* we'll need the rtable for recordDependencyOnExpr */
+ whenRtable = pstate->p_rtable;
recordDependencyOnExpr(&myself, whenClause, whenRtable,
DEPENDENCY_NORMAL);
+ free_parsestate(pstate);
+ }
+
/* Post creation hook for new trigger */
InvokeObjectPostCreateHookArg(TriggerRelationId, trigoid, 0,
isInternal);
@@ -1175,7 +1092,6 @@ CreateTriggerFiringOn(CreateTrigStmt *stmt, const char *queryString,
*/
childStmt = copyObject(stmt);
childStmt->funcname = NIL;
- childStmt->whenClause = NULL;
/* If there is a WHEN clause, create a modified copy of it */
qual = copyObject(whenClause);
@@ -1185,11 +1101,13 @@ CreateTriggerFiringOn(CreateTrigStmt *stmt, const char *queryString,
qual = (Node *)
map_partition_varattnos((List *) qual, PRS2_NEW_VARNO,
childTbl, rel);
+ childStmt->whenClause = qual;
+ childStmt->transformed = true;
CreateTriggerFiringOn(childStmt, queryString,
partdesc->oids[i], refRelOid,
InvalidOid, InvalidOid,
- funcoid, trigoid, qual,
+ funcoid, trigoid,
isInternal, true, trigger_fires_when);
table_close(childTbl, NoLock);
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 9fd48acb1f8..05ff0c5973f 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -6070,6 +6070,7 @@ CreateTrigStmt:
n->deferrable = false;
n->initdeferred = false;
n->constrrel = NULL;
+ n->transformed = false;
$$ = (Node *) n;
}
| CREATE opt_or_replace CONSTRAINT TRIGGER name AFTER TriggerEvents ON
@@ -6120,6 +6121,7 @@ CreateTrigStmt:
&n->deferrable, &n->initdeferred, &dummy,
NULL, NULL, yyscanner);
n->constrrel = $10;
+ n->transformed = false;
$$ = (Node *) n;
}
;
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index e96b38a59d5..fd6d5c922ec 100644
--- a/src/backend/parser/parse_utilcmd.c
+++ b/src/backend/parser/parse_utilcmd.c
@@ -38,6 +38,7 @@
#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"
@@ -3133,6 +3134,149 @@ transformIndexStmt(Oid relid, IndexStmt *stmt, const char *queryString)
return stmt;
}
+/*
+ * transformCreateTriggerStmt - parse analysis for CREATE TRIGGER
+ *
+ * Note: This is for parse analysis CreateTrigStmt->whenClause only, other
+ * CreateTrigStmt error checking happen in CreateTriggerFiringOn.
+ *
+ * To avoid race conditions, it's important that this function relies only on
+ * the passed-in relid (and not on stmt->relation) to determine the target
+ * relation.
+ */
+CreateTrigStmt *
+transformCreateTriggerStmt(Oid relid, CreateTrigStmt *stmt, const char *queryString)
+{
+ int16 tgtype;
+ ParseState *pstate;
+ ParseNamespaceItem *nsitem;
+ List *varList;
+ Relation rel;
+
+ /* Nothing to do if statement already transformed. */
+ if (stmt->transformed)
+ return stmt;
+
+ /* Compute tgtype */
+ TRIGGER_CLEAR_TYPE(tgtype);
+ if (stmt->row)
+ TRIGGER_SETT_ROW(tgtype);
+ tgtype |= stmt->timing;
+ tgtype |= stmt->events;
+
+ /* Set up a pstate to parse with */
+ pstate = make_parsestate(NULL);
+ pstate->p_sourcetext = queryString;
+
+ /*
+ * Put the parent table into the rtable so that the expressions can refer
+ * to its fields without qualification. Caller is responsible for locking
+ * relation, but we still need to open it.
+ */
+ rel = relation_open(relid, NoLock);
+
+ /*
+ * Set up nsitems for OLD and NEW references.
+ *
+ * 'OLD' must always have varno equal to 1 and 'NEW' equal to 2.
+ */
+ nsitem = addRangeTableEntryForRelation(pstate, rel,
+ AccessShareLock,
+ makeAlias("old", NIL),
+ false, false);
+ addNSItemToQuery(pstate, nsitem, false, true, true);
+ nsitem = addRangeTableEntryForRelation(pstate, rel,
+ AccessShareLock,
+ makeAlias("new", NIL),
+ false, false);
+ addNSItemToQuery(pstate, nsitem, false, true, true);
+
+ stmt->whenClause = transformWhereClause(pstate,
+ stmt->whenClause,
+ EXPR_KIND_TRIGGER_WHEN,
+ "WHEN");
+ /* we have to fix its collations too */
+ assign_expr_collations(pstate, stmt->whenClause);
+
+ /*
+ * Check for disallowed references to OLD/NEW.
+ *
+ * NB: pull_var_clause is okay here only because we don't allow
+ * subselects in WHEN clauses; it would fail to examine the contents
+ * of subselects.
+ */
+ varList = pull_var_clause(stmt->whenClause, 0);
+ foreach_node(Var, var, varList)
+ {
+ switch (var->varno)
+ {
+ case PRS2_OLD_VARNO:
+ if (!TRIGGER_FOR_ROW(tgtype))
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+ errmsg("statement trigger's WHEN condition cannot reference column values"),
+ parser_errposition(pstate, var->location));
+ if (TRIGGER_FOR_INSERT(tgtype))
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+ errmsg("INSERT trigger's WHEN condition cannot reference OLD values"),
+ parser_errposition(pstate, var->location));
+ /* system columns are okay here */
+ break;
+ case PRS2_NEW_VARNO:
+ if (!TRIGGER_FOR_ROW(tgtype))
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+ errmsg("statement trigger's WHEN condition cannot reference column values"),
+ parser_errposition(pstate, var->location));
+ if (TRIGGER_FOR_DELETE(tgtype))
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+ errmsg("DELETE trigger's WHEN condition cannot reference NEW values"),
+ parser_errposition(pstate, var->location));
+ if (var->varattno < 0 && TRIGGER_FOR_BEFORE(tgtype))
+ ereport(ERROR,
+ errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("BEFORE trigger's WHEN condition cannot reference NEW system columns"),
+ parser_errposition(pstate, var->location));
+ if (TRIGGER_FOR_BEFORE(tgtype) &&
+ var->varattno == 0 &&
+ RelationGetDescr(rel)->constr &&
+ (RelationGetDescr(rel)->constr->has_generated_stored ||
+ RelationGetDescr(rel)->constr->has_generated_virtual))
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+ errmsg("BEFORE trigger's WHEN condition cannot reference NEW generated columns"),
+ errdetail("A whole-row reference is used and the table contains generated columns."),
+ parser_errposition(pstate, var->location));
+ if (TRIGGER_FOR_BEFORE(tgtype) &&
+ var->varattno > 0 &&
+ TupleDescAttr(RelationGetDescr(rel), var->varattno - 1)->attgenerated)
+ ereport(ERROR,
+ errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+ errmsg("BEFORE trigger's WHEN condition cannot reference NEW generated columns"),
+ errdetail("Column \"%s\" is a generated column.",
+ NameStr(TupleDescAttr(RelationGetDescr(rel), var->varattno - 1)->attname)),
+ parser_errposition(pstate, var->location));
+ break;
+ default:
+ /* can't happen without add_missing_from, so just elog */
+ elog(ERROR, "trigger WHEN condition cannot contain references to other relations");
+ break;
+ }
+ }
+
+ free_parsestate(pstate);
+
+ /* Close relation */
+ table_close(rel, NoLock);
+
+ /* Mark statement as successfully transformed */
+ stmt->transformed = true;
+
+ return stmt;
+}
+
/*
* transformStatsStmt - parse analysis for CREATE STATISTICS
*
diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index 918db53dd5e..73d47b5ebf2 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -1695,7 +1695,7 @@ ProcessUtilitySlow(ParseState *pstate,
address = CreateTrigger((CreateTrigStmt *) parsetree,
queryString, InvalidOid, InvalidOid,
InvalidOid, InvalidOid, InvalidOid,
- InvalidOid, NULL, false, false);
+ InvalidOid, false, false);
break;
case T_CreatePLangStmt:
diff --git a/src/include/commands/trigger.h b/src/include/commands/trigger.h
index cfd7daa20ed..3f4951bca61 100644
--- a/src/include/commands/trigger.h
+++ b/src/include/commands/trigger.h
@@ -153,12 +153,12 @@ extern PGDLLIMPORT int SessionReplicationRole;
extern ObjectAddress CreateTrigger(CreateTrigStmt *stmt, const char *queryString,
Oid relOid, Oid refRelOid, Oid constraintOid, Oid indexOid,
- Oid funcoid, Oid parentTriggerOid, Node *whenClause,
+ Oid funcoid, Oid parentTriggerOid,
bool isInternal, bool in_partition);
extern ObjectAddress CreateTriggerFiringOn(CreateTrigStmt *stmt, const char *queryString,
Oid relOid, Oid refRelOid, Oid constraintOid,
Oid indexOid, Oid funcoid, Oid parentTriggerOid,
- Node *whenClause, bool isInternal, bool in_partition,
+ bool isInternal, bool in_partition,
char trigger_fires_when);
extern void TriggerSetParentTrigger(Relation trigRel,
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 4ed14fc5b78..fdb0b8a29dd 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -3122,6 +3122,7 @@ typedef struct CreateTrigStmt
bool deferrable; /* [NOT] DEFERRABLE */
bool initdeferred; /* INITIALLY {DEFERRED|IMMEDIATE} */
RangeVar *constrrel; /* opposite relation, if RI trigger */
+ bool transformed; /* true when transformCreateTriggerStmt is finished */
} CreateTrigStmt;
/* ----------------------
diff --git a/src/include/parser/parse_utilcmd.h b/src/include/parser/parse_utilcmd.h
index 4965fac4495..5482719d997 100644
--- a/src/include/parser/parse_utilcmd.h
+++ b/src/include/parser/parse_utilcmd.h
@@ -28,6 +28,8 @@ extern IndexStmt *transformIndexStmt(Oid relid, IndexStmt *stmt,
const char *queryString);
extern CreateStatsStmt *transformStatsStmt(Oid relid, CreateStatsStmt *stmt,
const char *queryString);
+extern CreateTrigStmt *transformCreateTriggerStmt(Oid relid, CreateTrigStmt *stmt,
+ const char *queryString);
extern void transformRuleStmt(RuleStmt *stmt, const char *queryString,
List **actions, Node **whereClause);
extern List *transformCreateSchemaStmtElements(List *schemaElts,
--
2.34.1
From 443352430ea0daca1c285a130144bfb59999256e Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Mon, 29 Sep 2025 19:07:49 +0800
Subject: [PATCH v1 2/2] let ALTER COLUMN SET DATA TYPE cope with trigger
dependency
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
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;
ERROR: cannot alter type of a column used in a trigger definition
DETAIL: trigger modified_a on table main_table depends on column "a"
With the attached patch, the previous error will no longer occur.
Foreign key–related internal triggers are not directly dependent on the relation
itself; instead, they depend directly on the constraint. Therefore, internal
triggers are not a concern in this context.
discussion: https://postgr.es/m/
---
src/backend/catalog/index.c | 1 +
src/backend/commands/tablecmds.c | 148 ++++++++++++++++--
src/backend/commands/trigger.c | 61 ++++++++
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/triggers.out | 62 ++++++++
src/test/regress/sql/triggers.sql | 14 ++
11 files changed, 288 insertions(+), 17 deletions(-)
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index 8d09d61cae2..712014e6f95 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -2039,6 +2039,7 @@ 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),
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index bf8120c12f2..dc262e257e6 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,
@@ -5449,6 +5454,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)
@@ -6716,6 +6725,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;
@@ -9663,6 +9674,33 @@ 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));
+
+ /* The CreateTrigStmt has already been through transformCreateTriggerStmt */
+ Assert(stmt->transformed);
+
+ address = CreateTrigger((CreateTrigStmt *) stmt,
+ NULL,
+ InvalidOid, InvalidOid, InvalidOid,
+ InvalidOid, InvalidOid, InvalidOid,
+ false, false);
+ return address;
+}
+
+
/*
* ALTER TABLE ADD CONSTRAINT USING INDEX
*
@@ -13802,6 +13840,7 @@ 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,
@@ -13848,6 +13887,7 @@ 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)
@@ -13909,6 +13949,7 @@ 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)
@@ -15121,21 +15162,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 on the relation(s). 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:
@@ -15396,6 +15429,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
@@ -15540,6 +15600,40 @@ 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 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);
+ }
+
+ ATPostAlterTypeParse(oldId, linitial_oid(relids), InvalidOid,
+ (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
*/
@@ -15588,9 +15682,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.
@@ -15641,6 +15735,11 @@ ATPostAlterTypeParse(Oid oldId, Oid oldRelId, Oid refRelId, char *cmd,
transformStatsStmt(oldRelId,
(CreateStatsStmt *) stmt,
cmd));
+ else if (IsA(stmt, CreateTrigStmt))
+ querytree_list = lappend(querytree_list,
+ transformCreateTriggerStmt(oldRelId,
+ castNode(CreateTrigStmt, stmt),
+ cmd));
else
querytree_list = lappend(querytree_list, stmt);
}
@@ -15791,6 +15890,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));
@@ -20831,6 +20944,7 @@ CloneRowTriggersToPartition(Relation parent, Relation partition)
trigStmt->deferrable = trigForm->tgdeferrable;
trigStmt->initdeferred = trigForm->tginitdeferred;
trigStmt->constrrel = NULL; /* passed separately */
+ trigStmt->trigcomment = NULL;
trigStmt->transformed = true; /* whenClause alerady transformed */
CreateTriggerFiringOn(trigStmt, NULL, RelationGetRelid(partition),
diff --git a/src/backend/commands/trigger.c b/src/backend/commands/trigger.c
index f1431d99a56..4f01dad5853 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"
@@ -1122,6 +1123,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;
}
@@ -1331,6 +1337,61 @@ 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))
+ {
+ systable_endscan(tgscan);
+ table_close(tgrel, AccessShareLock);
+ return result;
+ }
+
+ 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 05ff0c5973f..4112c34dace 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -6070,6 +6070,7 @@ CreateTrigStmt:
n->deferrable = false;
n->initdeferred = false;
n->constrrel = NULL;
+ n->trigcomment = NULL;
n->transformed = false;
$$ = (Node *) n;
}
@@ -6121,6 +6122,7 @@ CreateTrigStmt:
&n->deferrable, &n->initdeferred, &dummy,
NULL, NULL, yyscanner);
n->constrrel = $10;
+ n->trigcomment = NULL;
n->transformed = false;
$$ = (Node *) n;
}
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 0408a95941d..792266e434c 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 3f4951bca61..a02de432495 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 fdb0b8a29dd..4114488ca82 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2477,6 +2477,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 */
@@ -3122,6 +3123,7 @@ 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 when transformCreateTriggerStmt is finished */
} 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 193669f2bc1..5009681fbb9 100644
--- a/src/test/modules/test_ddl_deparse/test_ddl_deparse.c
+++ b/src/test/modules/test_ddl_deparse/test_ddl_deparse.c
@@ -308,6 +308,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/triggers.out b/src/test/regress/expected/triggers.out
index 1eb8fba0953..ecb2f36074e 100644
--- a/src/test/regress/expected/triggers.out
+++ b/src/test/regress/expected/triggers.out
@@ -316,6 +316,38 @@ SELECT pg_get_triggerdef(oid, true) FROM pg_trigger WHERE tgrelid = 'main_table'
CREATE TRIGGER modified_any BEFORE UPDATE OF a ON main_table FOR EACH ROW WHEN (old.* IS DISTINCT FROM new.*) EXECUTE FUNCTION trigger_func('modified_any')
(1 row)
+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 INT8;
+\d main_table
+ Table "public.main_table"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | bigint | | |
+ b | integer | | |
+Triggers:
+ after_ins_stmt_trig AFTER INSERT ON main_table FOR EACH STATEMENT EXECUTE FUNCTION trigger_func('after_ins_stmt')
+ after_upd_row_trig AFTER UPDATE ON main_table FOR EACH ROW EXECUTE FUNCTION trigger_func('after_upd_row')
+ after_upd_stmt_trig AFTER UPDATE ON main_table FOR EACH STATEMENT EXECUTE FUNCTION trigger_func('after_upd_stmt')
+ before_ins_stmt_trig BEFORE INSERT ON main_table FOR EACH STATEMENT EXECUTE FUNCTION trigger_func('before_ins_stmt')
+ delete_a AFTER DELETE ON main_table FOR EACH ROW WHEN (old.a = 123) EXECUTE FUNCTION trigger_func('delete_a')
+ delete_when AFTER DELETE ON main_table FOR EACH STATEMENT WHEN (true) EXECUTE FUNCTION trigger_func('delete_when')
+ insert_a AFTER INSERT ON main_table FOR EACH ROW WHEN (new.a = 123) EXECUTE FUNCTION trigger_func('insert_a')
+ insert_when BEFORE INSERT ON main_table FOR EACH STATEMENT WHEN (true) EXECUTE FUNCTION trigger_func('insert_when')
+ modified_a BEFORE UPDATE OF a ON main_table FOR EACH ROW WHEN (old.a <> new.a) EXECUTE FUNCTION trigger_func('modified_a')
+ modified_any BEFORE UPDATE OF a ON main_table FOR EACH ROW WHEN (old.* IS DISTINCT FROM new.*) EXECUTE FUNCTION trigger_func('modified_any')
+
+\dd modified_a
+ Object descriptions
+ Schema | Name | Object | Description
+--------+------------+---------+--------------------
+ public | modified_a | trigger | modified_a trigger
+(1 row)
+
+ALTER TABLE main_table ALTER COLUMN a SET DATA TYPE INT4; --change it back
-- Test RENAME TRIGGER
ALTER TRIGGER modified_a ON main_table RENAME TO modified_modified_a;
SELECT count(*) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'modified_a';
@@ -343,6 +375,7 @@ 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 bigint;
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 +2198,20 @@ 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 bigint;
+\d parted_irreg
+ Partitioned table "public.parted_irreg"
+ Column | Type | Collation | Nullable | Default
+--------+--------+-----------+----------+---------
+ a | bigint | | |
+ b | text | | |
+Partition of: parted_irreg_ancestor FOR VALUES FROM ('aaaa') TO ('zzzz')
+Partition key: RANGE (b)
+Triggers:
+ parted_trig AFTER INSERT ON parted_irreg FOR EACH ROW EXECUTE FUNCTION trigger_notice_ab()
+ parted_trig_odd AFTER INSERT ON parted_irreg FOR EACH ROW WHEN (bark(new.b) AND (new.a % 2::bigint) = 1) EXECUTE FUNCTION trigger_notice_ab()
+Number of partitions: 1 (Use \d+ to list them.)
+
drop table parted_irreg_ancestor;
-- Before triggers and partitions
create table parted (a int, b int, c text) partition by list (a);
@@ -2318,6 +2365,21 @@ 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 bigint;
+\d parted_constr
+ Partitioned table "public.parted_constr"
+ Column | Type | Collation | Nullable | Default
+--------+--------+-----------+----------+---------
+ a | bigint | | |
+ b | text | | |
+Partition of: parted_constr_ancestor FOR VALUES FROM ('aaaa') TO ('zzzz')
+Partition key: RANGE (b)
+Triggers:
+ parted_trig AFTER INSERT ON parted_constr DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE FUNCTION trigger_notice_ab(), ON TABLE parted_constr_ancestor
+ parted_trig_two AFTER INSERT ON parted_constr DEFERRABLE INITIALLY DEFERRED FOR EACH ROW WHEN (bark(new.b) AND (new.a % 2::bigint) = 1) EXECUTE FUNCTION trigger_notice_ab()
+Number of partitions: 1 (Use \d+ to list them.)
+
+alter table parted_constr_ancestor alter column a set data type int;
-- 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/triggers.sql b/src/test/regress/sql/triggers.sql
index 5f7f75d7ba5..6513eb83d27 100644
--- a/src/test/regress/sql/triggers.sql
+++ b/src/test/regress/sql/triggers.sql
@@ -192,6 +192,13 @@ SELECT pg_get_triggerdef(oid, true) FROM pg_trigger WHERE tgrelid = 'main_table'
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';
+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 INT8;
+\d main_table
+\dd modified_a
+ALTER TABLE main_table ALTER COLUMN a SET DATA TYPE INT4; --change it back
+
-- Test RENAME TRIGGER
ALTER TRIGGER modified_a ON main_table RENAME TO modified_modified_a;
SELECT count(*) FROM pg_trigger WHERE tgrelid = 'main_table'::regclass AND tgname = 'modified_a';
@@ -211,6 +218,7 @@ 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 bigint;
update table_with_oids set a = a + 1;
drop table table_with_oids;
@@ -1495,6 +1503,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 bigint;
+\d parted_irreg
drop table parted_irreg_ancestor;
-- Before triggers and partitions
@@ -1608,6 +1618,10 @@ 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 bigint;
+\d parted_constr
+alter table parted_constr_ancestor alter column a set data type int;
+
-- 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
