From 8c80281d3608d78b881e2bbeae70d1624f139c98 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 relation RTEs outside the FROM clause unchanged set_relation_column_names() uniquified every RTE's column names against dpns->using_names, including relation 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_RELATION that is not marked inFromCl. Other kinds keep it even when inFromCl is clear: the subquery RTE an INSERT ... SELECT creates is one, and its own duplicate column names still have to be told apart. Names that are actually printed are 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 c34243df975..8490bcf246e 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -5033,8 +5033,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 1a29d46213e..5ff9d16f524 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