Hi hackers,

When a query has a merged USING column of an unnamed join,
ruleutils.c renames other columns of that name so that the merged
column can be referenced unambiguously, and prints a column alias
list that defines the new names.  A relation that is not printed in
FROM (the target of UPDATE, DELETE or MERGE, NEW and OLD of a rule)
cannot have that list, so its renamed columns are defined nowhere and
the text does not read back.

The problem
-----------

    create table a (x int);
    create table b (x int);
    create table t (x int, y int);
    create rule r as on update to a
      do also update t set y = 1
      from a ca full join b cb using (x)
      where t.x = new.x and ca.x = old.x;

    before:  ... UPDATE t AS t SET y = 1
               FROM a ca FULL JOIN b cb USING (x)
              WHERE t.x_1 = new.x_1 AND ca.x = old.x_1;
             restore: ERROR:  column t.x_1 does not exist
    after:   ... UPDATE t SET y = 1
               FROM a ca FULL JOIN b cb USING (x)
              WHERE t.x = new.x AND ca.x = old.x;

So a dump with such a rule or a BEGIN ATOMIC function body does not
restore.  OLD and NEW of RETURNING read the target, so they are
renamed with it.  The target of DELETE ... USING and MERGE is renamed
the same way (dt.x_1, mt.x_1); the tests use UPDATE only, as all
three take the same path.

The fix
-------

The fix keeps the real names of the columns of an RTE that is not in
FROM, and leaves it out of the global comparison with USING names.
Its columns are always qualified with the relation name, so they
cannot be confused with the merged column.  Relations in FROM are
not affected.

The tests add the rule above to rules, and check the same with
RETURNING WITH (OLD AS o, NEW AS n).  They come first with the
output before the fix, so the fix patch shows how the output
changes.

Back branches
-------------

I think this should be back-patched to 15.

  - The master patches apply to 19 and 18 as they are.
  - I also attach a version for 17, which resolves a conflict from
    a different comment in colname_is_unique() and drops the
    RETURNING WITH test.
  - The version for 17 applies to 16 and 15 as it is.

With that, make check passes on all six branches.

This is the second of the three ruleutils.c fixes I said in the row
pattern recognition thread [1] I would send on their own, and the
patch that takes it out of the series is posted there [2].

[1]
https://postgr.es/m/CAAAe_zCb9847vcoaH72wvk61O-xOH_OSzYHt40TH37xjz=k...@mail.gmail.com
[2]
https://postgr.es/m/CAAAe_zA7eFMiRRJAgHoaQ1S6YZWmMz7P=83kxnavg1jvq%[email protected]

Best regards,
Henson
From 8f5513304e9567f6f32011f07a065895ac2c52f6 Mon Sep 17 00:00:00 2001
From: Henson Choi <[email protected]>
Date: Wed, 21 Oct 2026 20:34:12 +0900
Subject: [PATCH v1 1/2] Add tests for column names of relations not printed in
 FROM

The columns of a relation that is not printed in the FROM clause,
such as the target of UPDATE or NEW and OLD of a rule, are renamed to
keep the name of a merged JOIN USING column unique.  The deparsed text
then refers to columns that do not exist.

The expected output shows the behavior before the fix.  The next
commit fixes it and updates the output.
---
 src/test/regress/expected/rules.out | 20 ++++++++++++++++++++
 src/test/regress/sql/rules.sql      | 12 ++++++++++++
 2 files changed, 32 insertions(+)

diff --git a/src/test/regress/expected/rules.out 
b/src/test/regress/expected/rules.out
index e59e7e8bb23..61d0bed0c81 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -3841,3 +3841,23 @@ DROP TABLE ruletest_t3;
 DROP TABLE ruletest_t2;
 DROP TABLE ruletest_t1;
 DROP USER regress_rule_user1;
+-- Test that the columns of a relation not printed in FROM (the target of
+-- UPDATE, NEW and OLD) are not renamed to keep a USING column name unique.
+CREATE TABLE ruletest_cu_a (x int);
+CREATE TABLE ruletest_cu_b (x int);
+CREATE TABLE ruletest_cu_t (x int, a int);
+CREATE RULE ruletest_cu_r AS ON UPDATE TO ruletest_cu_a
+    DO ALSO UPDATE ruletest_cu_t SET a = 1
+    FROM ruletest_cu_a ca FULL JOIN ruletest_cu_b cb USING (x)
+    WHERE ruletest_cu_t.x = NEW.x AND ca.x = OLD.x;
+SELECT pg_get_ruledef(oid) FROM pg_rewrite WHERE rulename = 'ruletest_cu_r';
+                                      pg_get_ruledef                           
            
+-------------------------------------------------------------------------------------------
+ CREATE RULE ruletest_cu_r AS                                                  
           +
+     ON UPDATE TO public.ruletest_cu_a DO  UPDATE ruletest_cu_t AS 
ruletest_cu_t SET a = 1+
+    FROM (ruletest_cu_a ca                                                     
           +
+      FULL JOIN ruletest_cu_b cb USING (x))                                    
           +
+   WHERE ((ruletest_cu_t.x_1 = new.x_1) AND (ca.x = old.x_1));
+(1 row)
+
+DROP TABLE ruletest_cu_a, ruletest_cu_b, ruletest_cu_t;
diff --git a/src/test/regress/sql/rules.sql b/src/test/regress/sql/rules.sql
index 999b53f51a5..eb79409bb6f 100644
--- a/src/test/regress/sql/rules.sql
+++ b/src/test/regress/sql/rules.sql
@@ -1429,3 +1429,15 @@ DROP TABLE ruletest_t2;
 DROP TABLE ruletest_t1;
 
 DROP USER regress_rule_user1;
+
+-- Test that the columns of a relation not printed in FROM (the target of
+-- UPDATE, NEW and OLD) are not renamed to keep a USING column name unique.
+CREATE TABLE ruletest_cu_a (x int);
+CREATE TABLE ruletest_cu_b (x int);
+CREATE TABLE ruletest_cu_t (x int, a int);
+CREATE RULE ruletest_cu_r AS ON UPDATE TO ruletest_cu_a
+    DO ALSO UPDATE ruletest_cu_t SET a = 1
+    FROM ruletest_cu_a ca FULL JOIN ruletest_cu_b cb USING (x)
+    WHERE ruletest_cu_t.x = NEW.x AND ca.x = OLD.x;
+SELECT pg_get_ruledef(oid) FROM pg_rewrite WHERE rulename = 'ruletest_cu_r';
+DROP TABLE ruletest_cu_a, ruletest_cu_b, ruletest_cu_t;
-- 
2.54.0 (Apple Git-157)

Attachment: v1-0001-Add-tests-for-column-names-of-relations-not-print.patch
Description: Binary data

Attachment: v1-0002-Keep-real-column-names-of-relations-not-printed-i.patch
Description: Binary data

From 3feb69a40d85589114f5bd80c68088660828e707 Mon Sep 17 00:00:00 2001
From: Henson Choi <[email protected]>
Date: Fri, 2 Oct 2026 22:44:30 +0900
Subject: [PATCH v1 2/2] Keep real column names of relations not printed in
 FROM

To keep the name of a merged column of an unnamed JOIN USING unique
across the query, colname_is_unique() renames other columns of that
name, for example to x_1, and prints a column alias list so that the
new names are defined.  A relation that is not printed in the FROM
clause (the target of UPDATE, DELETE or MERGE, NEW and OLD of a rule)
can have no column alias list, so its new names were defined
nowhere.  For example, this rule

    CREATE RULE ruletest_cu_r AS ON UPDATE TO ruletest_cu_a
        DO ALSO UPDATE ruletest_cu_t SET a = 1
        FROM ruletest_cu_a ca FULL JOIN ruletest_cu_b cb USING (x)
        WHERE ruletest_cu_t.x = NEW.x AND ca.x = OLD.x;

was printed as

    ... UPDATE ruletest_cu_t AS ruletest_cu_t SET a = 1
       FROM (ruletest_cu_a ca
         FULL JOIN ruletest_cu_b cb USING (x))
      WHERE ((ruletest_cu_t.x_1 = new.x_1) AND (ca.x = old.x_1));

and failed to read back:

    ERROR:  column ruletest_cu_t.x_1 does not exist

The same happened to the target of DELETE ... USING and MERGE, as in
"WHERE dt.x_1 = da.x" and "ON (mt.x_1 = ma.x)".  The tests use UPDATE
only, as all three take the same path.

Keep the real names of the columns of such a relation, and skip the
global comparison for it.  Its columns are always qualified with the
relation name, so they cannot be confused with the merged column.
The rule above is now printed as written:

    ... UPDATE ruletest_cu_t SET a = 1
       FROM (ruletest_cu_a ca
         FULL JOIN ruletest_cu_b cb USING (x))
      WHERE ((ruletest_cu_t.x = new.x) AND (ca.x = old.x));

Backpatch-through: 15
---
 src/backend/utils/adt/ruleutils.c   | 37 +++++++++++++++++++++++------
 src/test/regress/expected/rules.out | 14 +++++------
 2 files changed, 37 insertions(+), 14 deletions(-)

diff --git a/src/backend/utils/adt/ruleutils.c 
b/src/backend/utils/adt/ruleutils.c
index 2d3625c9b70..003d59a5670 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -205,7 +205,9 @@ typedef struct
  * can reference the input columns instead.  That approach can fail for merged
  * JOIN USING columns, however, so when we have one of those in an unnamed
  * join, we have to make that column's alias globally unique across the whole
- * query to ensure it can be referenced unambiguously.
+ * query to ensure it can be referenced unambiguously.  The exception is the
+ * columns of an RTE that is not printed in the FROM clause: they keep their
+ * real names (see keep_real_names).
  *
  * Another problem is that a JOIN USING clause requires the columns to be
  * merged to have the same aliases in both input RTEs, and that no other
@@ -266,6 +268,14 @@ typedef struct
        /* This flag tells whether we should actually print a column alias list 
*/
        bool            printaliases;
 
+       /*
+        * This flag is set for an RTE that is not in the FROM clause (for
+        * example, a DML target or rule OLD and NEW).  Such an RTE cannot have 
a
+        * column alias list, so its column names must stay the real ones.  They
+        * are therefore exempt from the global uniqueness of USING column 
names.
+        */
+       bool            keep_real_names;
+
        /* This list has all names used as USING names in joins above this RTE 
*/
        List       *parentUsing;        /* names assigned to parent merged 
columns */
 
@@ -4048,7 +4058,10 @@ set_deparse_for_query(deparse_namespace *dpns, Query 
*query,
                if (rte->rtekind == RTE_JOIN)
                        set_join_column_names(dpns, rte, colinfo);
                else
+               {
+                       colinfo->keep_real_names = !rte->inFromCl;
                        set_relation_column_names(dpns, rte, colinfo);
+               }
        }
 }
 
@@ -4095,7 +4108,10 @@ set_simple_column_names(deparse_namespace *dpns)
  * since that will prevent legal references to tables below the join.)
  * To ensure that every column in the query is unambiguously referenceable,
  * we must assign such merged columns names that are globally unique across
- * the whole query, aliasing other columns out of the way as necessary.
+ * the whole query, aliasing other columns out of the way as necessary.  (An
+ * RTE that is not printed in FROM, such as a DML target, is exempt: it can't
+ * have a column alias list, and its columns are always qualified with the
+ * RTE name, so it keeps its real column names.)
  *
  * Because the ensuing re-aliasing is fairly damaging to the readability of
  * the query, we don't do this unless we have to.  So, we must pre-scan
@@ -4816,13 +4832,20 @@ colname_is_unique(const char *colname, 
deparse_namespace *dpns,
                        return false;
        }
 
-       /* Also check against USING-column names that must be globally unique */
-       foreach(lc, dpns->using_names)
+       /*
+        * Also check against USING-column names that must be globally unique.
+        * Skip this check for an RTE that is not printed in FROM: it cannot 
have
+        * a column alias list, so its column names must stay the real ones.
+        */
+       if (!colinfo->keep_real_names)
        {
-               char       *oldname = (char *) lfirst(lc);
+               foreach(lc, dpns->using_names)
+               {
+                       char       *oldname = (char *) lfirst(lc);
 
-               if (strcmp(oldname, colname) == 0)
-                       return false;
+                       if (strcmp(oldname, colname) == 0)
+                               return false;
+               }
        }
 
        /* Also check against names already assigned for parent-join USING cols 
*/
diff --git a/src/test/regress/expected/rules.out 
b/src/test/regress/expected/rules.out
index 61d0bed0c81..2e22bf2e236 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -3851,13 +3851,13 @@ CREATE RULE ruletest_cu_r AS ON UPDATE TO ruletest_cu_a
     FROM ruletest_cu_a ca FULL JOIN ruletest_cu_b cb USING (x)
     WHERE ruletest_cu_t.x = NEW.x AND ca.x = OLD.x;
 SELECT pg_get_ruledef(oid) FROM pg_rewrite WHERE rulename = 'ruletest_cu_r';
-                                      pg_get_ruledef                           
            
--------------------------------------------------------------------------------------------
- CREATE RULE ruletest_cu_r AS                                                  
           +
-     ON UPDATE TO public.ruletest_cu_a DO  UPDATE ruletest_cu_t AS 
ruletest_cu_t SET a = 1+
-    FROM (ruletest_cu_a ca                                                     
           +
-      FULL JOIN ruletest_cu_b cb USING (x))                                    
           +
-   WHERE ((ruletest_cu_t.x_1 = new.x_1) AND (ca.x = old.x_1));
+                              pg_get_ruledef                              
+--------------------------------------------------------------------------
+ CREATE RULE ruletest_cu_r AS                                            +
+     ON UPDATE TO public.ruletest_cu_a DO  UPDATE ruletest_cu_t SET a = 1+
+    FROM (ruletest_cu_a ca                                               +
+      FULL JOIN ruletest_cu_b cb USING (x))                              +
+   WHERE ((ruletest_cu_t.x = new.x) AND (ca.x = old.x));
 (1 row)
 
 DROP TABLE ruletest_cu_a, ruletest_cu_b, ruletest_cu_t;
-- 
2.54.0 (Apple Git-157)

Reply via email to