From f0791829076092eed80b64c06e9e936363df13c8 Mon Sep 17 00:00:00 2001 From: Henson Choi Date: Sat, 15 Aug 2026 09:40:43 +0900 Subject: [PATCH] Keep column names of RTEs outside the FROM clause unchanged when deparsing set_relation_column_names() uniquified every RTE's column names against dpns->using_names, including RTEs that get_from_clause() never prints: the NEW and OLD pseudo-RTEs of a rule, and the target relation of an UPDATE or DELETE. Those have nowhere to carry a column alias list, so the renamed name could not be declared anywhere and a reference to it failed to parse back. An unnamed FULL JOIN USING reaches this, since it forces USING names to be unique across the whole query. A rule whose action referenced new.x, or the update target's own x, then deparsed as new.x_1 -- and pg_dump output built that way dropped the rule on restore, reporting only ERROR: column new.x_1 does not exist Skip the uniquification for an RTE that is not marked inFromCl. Names that are actually printed, such as the g1 in the added test, are still re-aliased as before. --- src/backend/utils/adt/ruleutils.c | 13 +++++-- src/test/regress/expected/rules.out | 54 +++++++++++++++++++++++++++++ src/test/regress/sql/rules.sql | 36 +++++++++++++++++++ 3 files changed, 101 insertions(+), 2 deletions(-) diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index a247b4175ec..755cd2f825e 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -5011,8 +5011,17 @@ set_relation_column_names(deparse_namespace *dpns, RangeTblEntry *rte, else colname = real_colname; - /* Unique-ify and insert into colinfo */ - colname = make_colname_unique(colname, dpns, colinfo); + /* + * Unique-ify and insert into colinfo. A relation RTE outside the + * FROM clause -- a rule's NEW or OLD, or the target of an UPDATE + * or DELETE -- has nowhere to carry a column alias list, so a + * renamed column would not reparse. Other kinds reach here with + * inFromCl clear and still get printed, the subquery an INSERT + * ... SELECT reads from among them, so they are renamed as + * before. + */ + if (rte->inFromCl || rte->rtekind != RTE_RELATION) + colname = make_colname_unique(colname, dpns, colinfo); colinfo->colnames[i] = colname; add_to_names_hash(colinfo, colname); diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index 6a3341356da..3057cf49cb9 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -3776,6 +3776,60 @@ INSERT INTO hats VALUES ('h7', 'black') RETURNING *; DROP RULE hat_confsel ON hats; drop table hats; drop table hat_data; +-- An unnamed FULL JOIN USING makes USING names unique query-wide, but an RTE +-- outside the FROM clause has no alias list to carry a renamed column. +create table rule_uniq1 (x int, y int); +create table rule_uniq2 (x int, z int); +create table rule_uniq3 (x int, w int); +create table rule_uniq_log (x int); +create rule rule_uniq_r as on update to rule_uniq1 do also + insert into rule_uniq_log + select g1.y from rule_uniq1 g1, rule_uniq2 full join rule_uniq3 using (x) + where g1.x <> new.x; +create rule rule_uniq_u as on update to rule_uniq1 do also + update rule_uniq_log set x = 1 + from rule_uniq2 full join rule_uniq3 using (x) + where rule_uniq_log.x = 0; +-- g1 is in the FROM clause and is re-aliased to x_1; new and the update +-- target are not, so they keep x +select rulename, definition from pg_rules where tablename = 'rule_uniq1' + order by rulename; + rulename | definition +-------------+----------------------------------------------------------------------------------- + rule_uniq_r | CREATE RULE rule_uniq_r AS + + | ON UPDATE TO public.rule_uniq1 DO INSERT INTO rule_uniq_log (x) SELECT g1.y+ + | FROM rule_uniq1 g1(x_1, y), + + | (rule_uniq2 + + | FULL JOIN rule_uniq3 USING (x)) + + | WHERE (g1.x_1 <> new.x); + rule_uniq_u | CREATE RULE rule_uniq_u AS + + | ON UPDATE TO public.rule_uniq1 DO UPDATE rule_uniq_log SET x = 1 + + | FROM (rule_uniq2 + + | FULL JOIN rule_uniq3 USING (x)) + + | WHERE (rule_uniq_log.x = 0); +(2 rows) + +drop table rule_uniq1, rule_uniq2, rule_uniq3, rule_uniq_log; +-- The subquery an INSERT ... SELECT reads from is outside the FROM clause +-- too, but it is printed, so its unnamed columns still need unique names. +-- LIMIT keeps the subquery from being pulled up, and the unfilled column +-- makes the plan project through a subquery scan. +create table rule_uniq_ins (a int, b int, c text, d int); +explain (verbose, costs off) + insert into rule_uniq_ins (a, b, c) + select a + 1, b + 1, c || c from rule_uniq_ins limit 2; + QUERY PLAN +-------------------------------------------------------------------------------------------------------------------------- + Insert on public.rule_uniq_ins + -> Subquery Scan on unnamed_subquery + Output: unnamed_subquery."?column?", unnamed_subquery."?column?_1", unnamed_subquery."?column?_2", NULL::integer + -> Limit + Output: ((rule_uniq_ins_1.a + 1)), ((rule_uniq_ins_1.b + 1)), ((rule_uniq_ins_1.c || rule_uniq_ins_1.c)) + -> Seq Scan on public.rule_uniq_ins rule_uniq_ins_1 + Output: (rule_uniq_ins_1.a + 1), (rule_uniq_ins_1.b + 1), (rule_uniq_ins_1.c || rule_uniq_ins_1.c) +(7 rows) + +drop table rule_uniq_ins; -- test for pg_get_functiondef properly regurgitating SET parameters -- Note that the function is kept around to stress pg_dump. CREATE FUNCTION func_with_set_params() RETURNS integer diff --git a/src/test/regress/sql/rules.sql b/src/test/regress/sql/rules.sql index abe89d097c9..71b7b001e9c 100644 --- a/src/test/regress/sql/rules.sql +++ b/src/test/regress/sql/rules.sql @@ -1235,6 +1235,42 @@ DROP RULE hat_confsel ON hats; drop table hats; drop table hat_data; +-- An unnamed FULL JOIN USING makes USING names unique query-wide, but an RTE +-- outside the FROM clause has no alias list to carry a renamed column. +create table rule_uniq1 (x int, y int); +create table rule_uniq2 (x int, z int); +create table rule_uniq3 (x int, w int); +create table rule_uniq_log (x int); + +create rule rule_uniq_r as on update to rule_uniq1 do also + insert into rule_uniq_log + select g1.y from rule_uniq1 g1, rule_uniq2 full join rule_uniq3 using (x) + where g1.x <> new.x; + +create rule rule_uniq_u as on update to rule_uniq1 do also + update rule_uniq_log set x = 1 + from rule_uniq2 full join rule_uniq3 using (x) + where rule_uniq_log.x = 0; + +-- g1 is in the FROM clause and is re-aliased to x_1; new and the update +-- target are not, so they keep x +select rulename, definition from pg_rules where tablename = 'rule_uniq1' + order by rulename; + +drop table rule_uniq1, rule_uniq2, rule_uniq3, rule_uniq_log; + +-- The subquery an INSERT ... SELECT reads from is outside the FROM clause +-- too, but it is printed, so its unnamed columns still need unique names. +-- LIMIT keeps the subquery from being pulled up, and the unfilled column +-- makes the plan project through a subquery scan. +create table rule_uniq_ins (a int, b int, c text, d int); + +explain (verbose, costs off) + insert into rule_uniq_ins (a, b, c) + select a + 1, b + 1, c || c from rule_uniq_ins limit 2; + +drop table rule_uniq_ins; + -- test for pg_get_functiondef properly regurgitating SET parameters -- Note that the function is kept around to stress pg_dump. CREATE FUNCTION func_with_set_params() RETURNS integer