From 4dbbbc191162cb2b61a220fb25ee35bad39c757d Mon Sep 17 00:00:00 2001 From: jian he 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/CACJufxGA6KVQy7DbHGLVw9s9KKmpGyZt5ME6C7kEfjDpr2wZCw@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_dKCpw3TfJiutfuywAQ@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