Re: Add system column support to the USING clause

On 13.09.2024 17:56, Tom Lane wrote:
> I think this is an actively bad idea, and it was likely intentional
> that it's not supported today. A few reasons why:

Thank you, Tom and David, for your feedback.

I admit my mistake. I should have asked if this problem was worth
solving before diving in. However, since I’ve already spent a lot of
time into the patch, so I'll try to fight a little ;-)

It looks like this feature hasn't been added because it's not obvious
how to do it. And it is difficult to assess the consequences of adding a
system column in RTE. Personally, I had to sweat to do it.

>* There are, fundamentally, no use-cases for joining on system
>columns. The only one that is stable enough to even consider
>using for the purpose is tableoid, and I'm not detecting a reason
>why that'd be useful. If there are any edge cases where people
>would actually wish to do that, it can be done easily enough with
>a standard JOIN ON clause.

But after all, it's implemented in `JOIN ON`. Accordingly, it seems like
it should also be supported in `JOIN USING`. And is there any guarantee
that new system columns won't be added in the future that may be more
useful?

> * This breaks ruleutils.c's mechanism for dealing with name
> conflicts across multiple USING clauses. That relies on being
> able to assign aliases to the USING inputs at the table level
> (that is, "FROM realtable AS aliastable(aliascolumn,...)").
> There's no way to alias a system column in the FROM syntax.

Could you please provide an example of such a query? I've tried creating
multi-join queries with aliases, but I couldn't break it. For example:
```sql
CREATE TABLE t    (id1 int);
CREATE TABLE tt   (id2 int);
CREATE TABLE ttt  (id3 int);
CREATE TABLE tttt (id4 int);

BEGIN;
INSERT INTO t    VALUES (1);
INSERT INTO tt   VALUES (101);
INSERT INTO ttt  VALUES (201);
INSERT INTO tttt VALUES (301);
COMMIT;

BEGIN;
INSERT INTO t    VALUES (2);
INSERT INTO tt   VALUES (102);
INSERT INTO ttt  VALUES (202);
INSERT INTO tttt VALUES (302);
COMMIT;

INSERT INTO t    VALUES (3);
INSERT INTO tt   VALUES (103);
INSERT INTO ttt  VALUES (203);
INSERT INTO tttt VALUES (303);

SELECT *FROM t FULL JOIN tt USING (xmin);
-- xmin | id1 | id2
--------+-----+-----
-- 1057 |   1 | 101
-- 1058 |   2 | 102
-- 1059 |   3 |
-- 1060 |     | 103
--(4 rows)

SELECT *FROM ttt FULL JOIN tttt USING (xmin);
-- xmin | id3 | id4
--------+-----+-----
-- 1057 | 201 | 301
-- 1058 | 202 | 302
-- 1061 | 203 |
-- 1062 |     | 303
--(4 rows)

SELECT * FROM t FULL JOIN tt USING (xmin) FULL JOIN ttt USING (xmin);
-- xmin | id1 | id2 | id3
--------+-----+-----+-----
-- 1057 |   1 | 101 | 201
-- 1058 |   2 | 102 | 202
-- 1059 |   3 |     |
-- 1060 |     | 103 |
-- 1061 |     |     | 203
--(5 rows)

SELECT *FROM
    (t FULL JOIN tt USING (xmin)) AS alias1(col1, col21, col31)
    JOIN
    (ttt FULL JOIN tttt USING (xmin)) AS alias2(col1, col22, col32)
    USING (col1);
-- col1 | col21 | col31 | col22 | col32
--------+-------+-------+-------+-------
-- 1057 |     1 |   101 |   201 |   301
-- 1058 |     2 |   102 |   202 |   302
--(2 rows)
```

I noticed that after adding it to the RTE, the negative system column
attributes will be used in `ruleutils.c` (see
[here](https://github.com/postgres/postgres/blob/52c707483ce4d0161127e4958d981d1b5655865e/src/backend/utils/adt/ruleutils.c#L5055))
and then in the `colinfo` structure. However, I didn't find any issues
with `colinfo`. For example:

```sql
create table tt2 (a int, b int, c int);
create table tt3 (ax int8, b int2, c numeric);
create table tt4 (ay int, b int, q int);
create view v2 as select * from
tt2 join tt3 using (b,c,xmin) join tt4 using (b, xmin);
select pg_get_viewdef('v2', true);
-- SELECT tt2.b, tt2.xmin, tt3.c, tt2.a, tt3.ax, tt4.ay, tt4.q
--    FROM tt2 JOIN tt3 USING (b, c, xmin) JOIN tt4 USING (b, xmin);
alter table tt2 add column d int;
alter table tt2 add column e int;
select pg_get_viewdef('v2', true);
-- SELECT tt2.b, tt2.xmin, tt3.c, tt2.a, tt3.ax, tt4.ay, tt4.q
--    FROM tt2 JOIN tt3 USING (b, c, xmin) JOIN tt4 USING (b, xmin);
--       alter table tt3 rename c to d;

select pg_get_viewdef('v2', true);
-- SELECT tt2.b, tt2.xmin, tt3.c, tt2.a, tt3.ax, tt4.ay, tt4.q
--    FROM tt2 JOIN tt3 tt3(ax, b, c) USING (b, c, xmin) JOIN tt4 USING
(b, xmin);
alter table tt3 add column c int;
alter table tt3 add column e int;
select pg_get_viewdef('v2', true);
-- SELECT tt2.b, tt2.xmin, tt3.c, tt2.a, tt3.ax, tt4.ay, tt4.q
--    FROM tt2 JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c, xmin)
--       JOIN tt4 USING (b, xmin);

alter table tt2 drop column d;
select pg_get_viewdef('v2', true);
-- SELECT tt2.b, tt2.xmin, tt3.c, tt2.a, tt3.ax, tt4.ay, tt4.q
--    FROM tt2 JOIN tt3 tt3(ax, b, c, c_1, e) USING (b, c, xmin)
--       JOIN tt4 USING (b, xmin);
```

--
Best regards,
Denis Garsh