Hi hackers, A SQL-standard function or procedure body that uses MERGE does not depend on the columns assigned by the MERGE actions, so those columns can be dropped or have their type altered. The same goes for a rule whose action has a MERGE in WITH. The function then fails at execution time, and its dumped definition cannot be restored. The two attached patches add tests and fix it.
The problem
-----------
create table t (k int, a int, b int);
create table s (k int, v int);
create function f() returns void
begin atomic
merge into t using s on t.k = s.k
when matched then update set a = s.v
when not matched then insert (k, b) values (s.k, s.v);
end;
alter table t drop column a; -- succeeds
alter table t drop column b; -- succeeds
select pg_get_functiondef('f'::regproc);
...
THEN UPDATE SET "........pg.dropped.2........" = s.v
WHEN NOT MATCHED
THEN INSERT (k, "........pg.dropped.3........")
...
ALTER COLUMN TYPE succeeds as well, and calling the function then
fails with "table row type and query-specified row type do not
match".
find_expr_references_walker() records a dependency on each target
column of an INSERT or UPDATE query (since c6be1f43ab0). But MERGE
keeps the target lists of its INSERT and UPDATE actions in
mergeActionList, not in the query's targetList, so they were never
looked at. This goes back to 15, where MERGE was added
(7103ebb7aae).
The fix
-------
0002 records the dependencies for the target lists of the INSERT and
UPDATE actions as well. The existing loop is moved into a small
helper used for both cases. With it, DROP COLUMN and ALTER COLUMN
TYPE on an assigned column fail, as they do for INSERT and UPDATE.
Columns that the MERGE does not assign can still be dropped.
Existing functions and rules get the dependencies only when they are
created again.
Tests
-----
0001 adds the tests with the output before the fix, and 0002 updates
it. create_function_sql covers a function body, and rules covers a
rule with a MERGE in WITH. Each tries ALTER COLUMN TYPE and DROP
COLUMN on the assigned columns and on one that is not assigned, and
then prints the definition.
Back branches
-------------
I think this should be back-patched to 15. The patches apply as they
are to REL_19_STABLE. On 17 and 18 the code applies as it is. In
the tests, the function test has to be moved, since the neighbouring
test it follows does not exist there, and the DETAIL of the ALTER
COLUMN TYPE errors has no trailing period there. A version for 16 is
attached separately as .txt files, and it also applies to 15 as it
is. It differs as follows:
- foreach and lfirst_node are used instead of foreach_node.
- add_object_address() is passed OCLASS_CLASS instead of
RelationRelationId. The latter also compiles there without a
warning but is wrong, so this needs care when back-patching.
- The rule test is dropped, since MERGE in WITH is allowed only
from 17.
On master and on 15 to 19, the tests show the bug with 0001 alone,
and check-world passes with both patches (on 17 and 18 with the test
adjusted as above).
This also came out of the AI-assisted audit of ruleutils.c, the same
one as my earlier report on parameters in SQL-standard function
bodies.
Best regards,
Henson
From 64aabe232ca4825e99f6ac9f74a2713625b49ab1 Mon Sep 17 00:00:00 2001 From: Henson Choi <[email protected]> Date: Fri, 30 Oct 2026 21:56:52 +0900 Subject: [PATCH v1 1/2] Add tests for column dependencies of MERGE actions A SQL-standard function body that uses MERGE does not depend on the target columns of the MERGE actions, so ALTER COLUMN TYPE and DROP COLUMN on those columns succeed and the stored query is left referring to dropped columns. The expected output shows the current behavior; the next commit fixes it. --- .../regress/expected/create_function_sql.out | 35 +++++++++++++++++++ src/test/regress/sql/create_function_sql.sql | 20 +++++++++++ 2 files changed, 55 insertions(+) diff --git a/src/test/regress/expected/create_function_sql.out b/src/test/regress/expected/create_function_sql.out index 50aca5940ff..73a1743a09c 100644 --- a/src/test/regress/expected/create_function_sql.out +++ b/src/test/regress/expected/create_function_sql.out @@ -454,6 +454,41 @@ DROP TABLE functest3 CASCADE; NOTICE: drop cascades to 2 other objects DETAIL: drop cascades to view functestv3 drop cascades to function functest_s_14() +-- Check dependencies on the target columns of MERGE actions within +-- SQL-standard body, like those of INSERT and UPDATE +CREATE TABLE functest_mtgt (k int, a int, b int, c int); +CREATE TABLE functest_msrc (k int, v int); +CREATE FUNCTION functest_merge() RETURNS void + BEGIN ATOMIC + MERGE INTO functest_mtgt t USING functest_msrc s ON t.k = s.k + WHEN MATCHED THEN UPDATE SET a = s.v + WHEN NOT MATCHED THEN INSERT (k, b) VALUES (s.k, s.v); + END; +ALTER TABLE functest_mtgt ALTER COLUMN a TYPE bigint; +ALTER TABLE functest_mtgt DROP COLUMN a; +ALTER TABLE functest_mtgt DROP COLUMN b; +ALTER TABLE functest_mtgt DROP COLUMN c; -- not assigned, so OK +SELECT pg_get_functiondef('functest_merge'::regproc); + pg_get_functiondef +------------------------------------------------------------ + CREATE OR REPLACE FUNCTION temp_func_test.functest_merge()+ + RETURNS void + + LANGUAGE sql + + BEGIN ATOMIC + + MERGE INTO functest_mtgt AS t + + USING functest_msrc s + + ON (t.k = s.k) + + WHEN MATCHED + + THEN UPDATE SET "........pg.dropped.2........" = s.v + + WHEN NOT MATCHED + + THEN INSERT (k, "........pg.dropped.3........") + + VALUES (s.k, s.v); + + END + + +(1 row) + +DROP FUNCTION functest_merge(); +DROP TABLE functest_mtgt, functest_msrc; -- information_schema tests CREATE FUNCTION functest_IS_1(a int, b int default 1, c text default 'foo') RETURNS int diff --git a/src/test/regress/sql/create_function_sql.sql b/src/test/regress/sql/create_function_sql.sql index 89e9af3a499..f28ed03da2b 100644 --- a/src/test/regress/sql/create_function_sql.sql +++ b/src/test/regress/sql/create_function_sql.sql @@ -241,6 +241,26 @@ SELECT functest_S_14(); DROP TABLE functest3 CASCADE; +-- Check dependencies on the target columns of MERGE actions within +-- SQL-standard body, like those of INSERT and UPDATE +CREATE TABLE functest_mtgt (k int, a int, b int, c int); +CREATE TABLE functest_msrc (k int, v int); + +CREATE FUNCTION functest_merge() RETURNS void + BEGIN ATOMIC + MERGE INTO functest_mtgt t USING functest_msrc s ON t.k = s.k + WHEN MATCHED THEN UPDATE SET a = s.v + WHEN NOT MATCHED THEN INSERT (k, b) VALUES (s.k, s.v); + END; + +ALTER TABLE functest_mtgt ALTER COLUMN a TYPE bigint; +ALTER TABLE functest_mtgt DROP COLUMN a; +ALTER TABLE functest_mtgt DROP COLUMN b; +ALTER TABLE functest_mtgt DROP COLUMN c; -- not assigned, so OK +SELECT pg_get_functiondef('functest_merge'::regproc); + +DROP FUNCTION functest_merge(); +DROP TABLE functest_mtgt, functest_msrc; -- information_schema tests -- 2.54.0 (Apple Git-157)
v1-0001-Add-tests-for-column-dependencies-of-MERGE-action.patch
Description: Binary data
v1-0002-Record-dependencies-on-the-target-columns-of-MERG.patch
Description: Binary data
From 47e9afb32b88a107c87f9e3d3db35a2ea517e0c0 Mon Sep 17 00:00:00 2001 From: Henson Choi <[email protected]> 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)
