[DEPARSE] Renamed columns of relations not in FROM break rules and functions

From: Henson Choi <assam258(at)gmail(dot)com>
To: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: [DEPARSE] Renamed columns of relations not in FROM break rules and functions
Date: 2026-11-17 03:02:27
Message-ID: CAAAe_zCrv2GbPcOKxPXWn7VfLh6Mp0v_OJsLzLDBsneAtAXTNg@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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=KmKg@mail.gmail.com
[2]
https://postgr.es/m/CAAAe_zA7eFMiRRJAgHoaQ1S6YZWmMz7P=83kXnavg1jVq%2BNTyQ@mail.gmail.com

Best regards,
Henson

Attachment Content-Type Size
REL_17-v1-0001-Add-tests-for-column-names-of-relations-not-print.txt text/plain 3.2 KB
v1-0001-Add-tests-for-column-names-of-relations-not-print.patch application/octet-stream 4.8 KB
v1-0002-Keep-real-column-names-of-relations-not-printed-i.patch application/octet-stream 8.8 KB
REL_17-v1-0002-Keep-real-column-names-of-relations-not-printed-i.txt text/plain 7.0 KB

Browse pgsql-hackers by date

  From Date Subject
Next Message Henson Choi 2026-11-17 03:03:24 [DEPARSE] Column alias lists of XMLTABLE and JSON_TABLE are not printed
Previous Message Amit Kapila 2026-11-17 02:53:57 Re: Document prepared transaction lock waits with two_phase subscriptions