From 47e9afb32b88a107c87f9e3d3db35a2ea517e0c0 Mon Sep 17 00:00:00 2001 From: Henson Choi Date: Fri, 30 Oct 2026 21:57:44 +0900 Subject: [PATCH v1 2/2] Record dependencies on the target columns of MERGE actions find_expr_references_walker() records a dependency on each target column of an INSERT or UPDATE query, so that the column can be neither dropped nor have its type altered while a stored query assigns to it. It did not do so for the INSERT and UPDATE actions of a MERGE, whose target lists are in mergeActionList rather than in the query's targetList. As a result, a SQL-standard function or procedure body containing a MERGE did not prevent DROP COLUMN or ALTER COLUMN TYPE on a column it assigns. The function then failed at execution time, and its dumped definition referred to "........pg.dropped.N........" and could not be restored. Record the dependencies for those actions as well. Existing functions do not acquire the missing dependencies until they are re-created. Oversight in commit 7103ebb7aae, which added MERGE. Backpatch-through: 15 --- src/backend/catalog/dependency.c | 56 +++++++++++++++---- .../regress/expected/create_function_sql.out | 12 +++- 2 files changed, 54 insertions(+), 14 deletions(-) diff --git a/src/backend/catalog/dependency.c b/src/backend/catalog/dependency.c index 4512269bd94..b1b2eb799b3 100644 --- a/src/backend/catalog/dependency.c +++ b/src/backend/catalog/dependency.c @@ -213,6 +213,8 @@ static void reportDependentObjects(const ObjectAddresses *targetObjects, static void deleteOneObject(const ObjectAddress *object, Relation *depRel, int32 flags); static void doDeletion(const ObjectAddress *object, int flags); +static void add_target_column_dependencies(List *targetList, Oid relid, + find_expr_references_context *context); static bool find_expr_references_walker(Node *node, find_expr_references_context *context); static void process_function_rte_ref(RangeTblEntry *rte, AttrNumber attnum, @@ -1823,6 +1825,27 @@ CheckUsageOnTypesInSingleRelExpr(Node *expr, Oid relId, Oid roleid) free_object_addresses(context.addrs); } +/* + * Record dependencies on the target columns named in the target list of an + * INSERT or UPDATE query, or of an INSERT or UPDATE action of a MERGE query. + */ +static void +add_target_column_dependencies(List *targetList, Oid relid, + find_expr_references_context *context) +{ + ListCell *lc; + + foreach(lc, targetList) + { + TargetEntry *tle = lfirst_node(TargetEntry, lc); + + if (tle->resjunk) + continue; /* ignore junk tlist items */ + add_object_address(OCLASS_CLASS, relid, tle->resno, + context->addrs); + } +} + /* * Recursively search an expression tree for object references. * @@ -2339,14 +2362,16 @@ find_expr_references_walker(Node *node, } /* - * If the query is an INSERT or UPDATE, we should create a dependency - * on each target column, to prevent the specific target column from - * being dropped. Although we will visit the TargetEntry nodes again - * during query_tree_walker, we won't have enough context to do this - * conveniently, so do it here. + * If the query is an INSERT, UPDATE or MERGE, we should create a + * dependency on each target column, to prevent the specific target + * column from being dropped. Although we will visit the TargetEntry + * nodes again during query_tree_walker, we won't have enough context + * to do this conveniently, so do it here. (For MERGE, the target + * lists are those of the INSERT and UPDATE actions.) */ if (query->commandType == CMD_INSERT || - query->commandType == CMD_UPDATE) + query->commandType == CMD_UPDATE || + query->commandType == CMD_MERGE) { RangeTblEntry *rte; @@ -2357,15 +2382,22 @@ find_expr_references_walker(Node *node, rte = rt_fetch(query->resultRelation, query->rtable); if (rte->rtekind == RTE_RELATION) { - foreach(lc, query->targetList) + if (query->commandType == CMD_MERGE) { - TargetEntry *tle = (TargetEntry *) lfirst(lc); + foreach(lc, query->mergeActionList) + { + MergeAction *action = lfirst_node(MergeAction, lc); - if (tle->resjunk) - continue; /* ignore junk tlist items */ - add_object_address(OCLASS_CLASS, rte->relid, tle->resno, - context->addrs); + if (action->commandType == CMD_INSERT || + action->commandType == CMD_UPDATE) + add_target_column_dependencies(action->targetList, + rte->relid, + context); + } } + else + add_target_column_dependencies(query->targetList, + rte->relid, context); } } diff --git a/src/test/regress/expected/create_function_sql.out b/src/test/regress/expected/create_function_sql.out index 73a1743a09c..427a4a15879 100644 --- a/src/test/regress/expected/create_function_sql.out +++ b/src/test/regress/expected/create_function_sql.out @@ -465,8 +465,16 @@ CREATE FUNCTION functest_merge() RETURNS void WHEN NOT MATCHED THEN INSERT (k, b) VALUES (s.k, s.v); END; ALTER TABLE functest_mtgt ALTER COLUMN a TYPE bigint; +ERROR: cannot alter type of a column used by a function or procedure +DETAIL: function functest_merge() depends on column "a" ALTER TABLE functest_mtgt DROP COLUMN a; +ERROR: cannot drop column a of table functest_mtgt because other objects depend on it +DETAIL: function functest_merge() depends on column a of table functest_mtgt +HINT: Use DROP ... CASCADE to drop the dependent objects too. ALTER TABLE functest_mtgt DROP COLUMN b; +ERROR: cannot drop column b of table functest_mtgt because other objects depend on it +DETAIL: function functest_merge() depends on column b of table functest_mtgt +HINT: Use DROP ... CASCADE to drop the dependent objects too. ALTER TABLE functest_mtgt DROP COLUMN c; -- not assigned, so OK SELECT pg_get_functiondef('functest_merge'::regproc); pg_get_functiondef @@ -479,9 +487,9 @@ SELECT pg_get_functiondef('functest_merge'::regproc); USING functest_msrc s + ON (t.k = s.k) + WHEN MATCHED + - THEN UPDATE SET "........pg.dropped.2........" = s.v + + THEN UPDATE SET a = s.v + WHEN NOT MATCHED + - THEN INSERT (k, "........pg.dropped.3........") + + THEN INSERT (k, b) + VALUES (s.k, s.v); + END + -- 2.54.0 (Apple Git-157)