Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sq618-006qPt-Tj for pgsql-hackers@arkaria.postgresql.org; Mon, 16 Sep 2024 07:19:28 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1sq618-00DBuF-9T for pgsql-hackers@arkaria.postgresql.org; Mon, 16 Sep 2024 07:19:26 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sq617-00DBpk-DW for pgsql-hackers@lists.postgresql.org; Mon, 16 Sep 2024 07:19:25 +0000 Received: from forward501a.mail.yandex.net ([178.154.239.81]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sq611-001PEn-5g for pgsql-hackers@lists.postgresql.org; Mon, 16 Sep 2024 07:19:23 +0000 Received: from mail-nwsmtp-smtp-production-main-31.vla.yp-c.yandex.net (mail-nwsmtp-smtp-production-main-31.vla.yp-c.yandex.net [IPv6:2a02:6b8:c0f:26bf:0:640:efa0:0]) by forward501a.mail.yandex.net (Yandex) with ESMTPS id 0FFC06174E; Mon, 16 Sep 2024 10:19:16 +0300 (MSK) Received: by mail-nwsmtp-smtp-production-main-31.vla.yp-c.yandex.net (smtp/Yandex) with ESMTPSA id DJFG2wOUi4Y0-GLmWNqhh; Mon, 16 Sep 2024 10:19:15 +0300 X-Yandex-Fwd: 1 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=arenadata.io; s=mail; t=1726471155; bh=SQdOgIqDRBo1XvhehR+ncVhG6qXc/S+gAeVfQPMakT4=; h=In-Reply-To:Cc:Date:References:To:Subject:Message-ID:From; b=lGNhlEINU4p1haxzsK/XtCuDgmORdnkHBlnH7C8ZSRtQGe1MsmNkUcdUWCAjvBpBL 7RV9goubuTAhsCyTmBQ3xLHAhkbuBuMoY14Ml/razzBlsL5q1gDvlJM2ueaF9lty1n yemIMaByZbMne2j9W00AqzWg1dcpCTa8acAjykeE= Authentication-Results: mail-nwsmtp-smtp-production-main-31.vla.yp-c.yandex.net; dkim=pass header.i=@arenadata.io Content-Type: multipart/alternative; boundary="------------TtMhC2xuqHH1EKMU3QcQ9J0r" Message-ID: <161910fa-f8fd-4e12-ace7-952e2c33f6c4@arenadata.io> Date: Mon, 16 Sep 2024 10:19:13 +0300 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Add system column support to the USING clause To: Tom Lane Cc: "pgsql-hackers@lists.postgresql.org" References: <333431726204057@mail.yandex.ru> <86136.1726239379@sss.pgh.pa.us> Content-Language: en-US From: Denis Garsh In-Reply-To: <86136.1726239379@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------TtMhC2xuqHH1EKMU3QcQ9J0r Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit 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 --------------TtMhC2xuqHH1EKMU3QcQ9J0r Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit


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
--------------TtMhC2xuqHH1EKMU3QcQ9J0r--