| From: | Henson Choi <assam258(at)gmail(dot)com> |
|---|---|
| To: | pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
| Subject: | [DEPARSE] Columns added to a function's result type shift its column aliases |
| Date: | 2026-11-16 05:00:53 |
| Message-ID: | CAAAe_zAwA8+4EHx_n0T-So1yhRqmRFjd7KmBHt2AzxaquAFS0g@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
Hi hackers,
When a function in FROM returns a named composite type that gains
columns after a view is created, ruleutils.c prints a column alias
list that reads back onto the wrong columns, if the range table entry
has several functions (ROWS FROM) or WITH ORDINALITY, or is under a
join with an alias list.
The problem
-----------
create table t1 (a int);
insert into t1 values (1);
create table t2 (b int);
insert into t2 values (2);
create function f1() returns setof t1
language sql as 'select * from t1';
create function f2() returns setof t2
language sql as 'select * from t2';
create view v as
select * from rows from (f1(), f2()) as r(m, n);
alter table t1 add column c int default 99;
select * from v; -- m = 1, n = 2
before: SELECT m, n FROM ROWS FROM(f1(), f2()) r(m, n);
created again, n is 99
after: SELECT m, n FROM ROWS FROM(f1(), f2()) r(m, c, n);
The alias list leaves out the columns added since parse time, but
when the text is read back they show up right after the other columns
of their function, so the aliases after them shift. Above, n names
the new column c.
With WITH ORDINALITY and no alias list written by the user, the new
column takes the name ordinality instead:
create view w as
select * from rows from (f1(), f2()) with ordinality;
alter table t2 add column d int;
before: ... WITH ORDINALITY f1(a, c, b, ordinality);
ERROR: column reference "ordinality" is ambiguous
after: ... WITH ORDINALITY f1(a, c, b, d, ordinality);
Tables are not affected, since their alias lists include the new
columns. Nor is a single function without WITH ORDINALITY, where
they come at the end, unless a join with an alias list is above it:
that list runs over the columns of all its inputs, so the inputs
after the function shift as well.
The fix
-------
0002 builds the alias list in the column order the text is read back
with, added columns included. Dropped columns are skipped, and the
new names are made unique like any other. Nothing changes when no
function has gained columns, and EXPLAIN is not affected. An
existing column whose name a new column took first is printed as, for
example, "ordinality_1 AS ordinality".
Tests
-----
Each case in rangefuncs creates a view, adds columns to the types,
creates the view again from pg_get_viewdef() and queries it:
- one function with ordinality
- the same, returning a domain over a composite type
- two functions without ordinality, the first example above
- two functions with ordinality, one of which had no columns, with a
new column taking the name of a later one and a dropped one among
them
- new columns after a dropped last column, with a name clashing
with an earlier one
The views are kept for the dump and restore comparison in the
pg_upgrade test. If that is more than wanted, the fourth case alone
covers most of the fix and could stay on its own.
Back branches
-------------
I think this should be back-patched to 15. The .patch files are for
master and also go to 18 and 19, with a trivial conflict on the two
allocations. The REL_17 .txt files are for 15-17, which have no
column name hash; on 17 the loop can use foreach_ptr as on master.
On 15 the expected output needs regenerating, as views there print
qualified column names.
This is one of the three ruleutils.c fixes I said in the row pattern
recognition thread [1] I would send on their own, and I have posted
a patch there that takes it out of the series [2]. The other two
follow in separate threads.
[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-a-test-for-columns-added-to-function-result-t.txt | text/plain | 11.8 KB |
| v1-0001-Add-a-test-for-columns-added-to-function-result-t.patch | application/octet-stream | 11.8 KB |
| v1-0002-Fix-alias-lists-of-function-RTEs-whose-composite-.patch | application/octet-stream | 17.7 KB |
| REL_17-v1-0002-Fix-alias-lists-of-function-RTEs-whose-composite-.txt | text/plain | 17.4 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Amit Kapila | 2026-11-16 05:00:54 | Re: Assertion failure in update_local_synced_slot(), slotsync.c:364 |
| Previous Message | Henson Choi | 2026-11-16 04:52:52 | Re: [PATCH] Consider window functions when reordering GROUP BY items |