Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ns4Pi-0004wr-L9 for pgsql-bugs@arkaria.postgresql.org; Fri, 20 May 2022 15:19:38 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1ns4Ph-0006eN-DM for pgsql-bugs@arkaria.postgresql.org; Fri, 20 May 2022 15:19:37 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ns4Pg-0006eE-RM for pgsql-bugs@lists.postgresql.org; Fri, 20 May 2022 15:19:37 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ns4Pe-0003ju-5u for pgsql-bugs@lists.postgresql.org; Fri, 20 May 2022 15:19:35 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 24KFJXCc347194; Fri, 20 May 2022 11:19:33 -0400 From: Tom Lane To: Daniel Gustafsson cc: n.lutic@loxodata.com, pgsql-bugs@lists.postgresql.org Subject: Re: BUG #17486: [pg_restore] Restoring a view fails if this view contains an attribute without alias name. In-reply-to: <310D60C6-5CF0-41C8-A814-3A3DE7ACF551@yesql.se> References: <17486-1ad6fd786728b8af@postgresql.org> <792E8C73-EBE0-41FC-A83F-2F7A7AF4ABBB@yesql.se> <334334.1653057260@sss.pgh.pa.us> <310D60C6-5CF0-41C8-A814-3A3DE7ACF551@yesql.se> Comments: In-reply-to Daniel Gustafsson message dated "Fri, 20 May 2022 17:07:29 +0200" MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----- =_aaaaaaaaaa0" Content-ID: <347091.1653059884.0@sss.pgh.pa.us> Date: Fri, 20 May 2022 11:19:33 -0400 Message-ID: <347193.1653059973@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------- =_aaaaaaaaaa0 Content-Type: text/plain; charset="us-ascii" Content-ID: <347091.1653059884.1@sss.pgh.pa.us> Content-Transfer-Encoding: quoted-printable Daniel Gustafsson writes: >> On 20 May 2022, at 16:34, Tom Lane wrote: >> Perhaps we should just tweak ruleutils so that the alias is always >> printed for non-Var columns, even when it's "?column?". That's kind of >> ugly, but if you wanted non-ugly you should have selected a better colu= mn >> name to start with. > That might be the path of least confusion, and as you rightly say, if yo= u don't > like the ugliness then there is a very easy way to fix it. Hmm ... it's a very easy code change, but it results in a lot of changes in the regression tests (and I've only tried the core tests so far). Given the lack of prior complaints, I wonder if it's going to be worth this much behavioral churn. It'd be better if we could do this only when the name is actually referenced somewhere, but I don't think that's an easy thing to determine. regards, tom lane ------- =_aaaaaaaaaa0 Content-Type: text/x-diff; name="tweak-ruleutils-column-name-rule.patch"; charset="us-ascii" Content-ID: <347091.1653059884.2@sss.pgh.pa.us> Content-Description: tweak-ruleutils-column-name-rule.patch Content-Transfer-Encoding: quoted-printable diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/rul= eutils.c index 49c4201dde..41275d39b3 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -6042,8 +6042,8 @@ get_target_list(List *targetList, deparse_context *c= ontext, else { get_rule_expr((Node *) tle->expr, context, true); - /* We'll show the AS name unless it's this: */ - attname =3D "?column?"; + /* Always show the assigned column name explicitly. */ + attname =3D NULL; } = /* ------- =_aaaaaaaaaa0 Content-Type: text/x-diff; name="regression.diffs"; charset="us-ascii" Content-ID: <347091.1653059884.3@sss.pgh.pa.us> Content-Description: regression.diffs Content-Transfer-Encoding: quoted-printable diff -U3 /home/postgres/pgsql/src/test/regress/expected/create_view.out /h= ome/postgres/pgsql/src/test/regress/results/create_view.out --- /home/postgres/pgsql/src/test/regress/expected/create_view.out 2022-05= -05 11:23:56.699131282 -0400 +++ /home/postgres/pgsql/src/test/regress/results/create_view.out 2022-05-= 20 11:14:00.316538617 -0400 @@ -444,7 +444,7 @@ tt1.f2, tt1.f3 FROM tt1 - WHERE (EXISTS ( SELECT 1 + WHERE (EXISTS ( SELECT 1 AS "?column?" FROM tx1 WHERE tt1.f1 =3D tx1.x1)); = @@ -460,7 +460,7 @@ a1.f2, a1.f3 FROM tt1 a1 - WHERE (EXISTS ( SELECT 1 + WHERE (EXISTS ( SELECT 1 AS "?column?" FROM tx1 WHERE a1.f1 =3D tx1.x1)); = @@ -476,7 +476,7 @@ tt1.f2, tt1.f3 FROM tt1 - WHERE (EXISTS ( SELECT 1 + WHERE (EXISTS ( SELECT 1 AS "?column?" FROM tx1 a2 WHERE tt1.f1 =3D a2.x1)); = @@ -492,7 +492,7 @@ tt1.f2, tt1.f3 FROM temp_view_test.tt1 - WHERE (EXISTS ( SELECT 1 + WHERE (EXISTS ( SELECT 1 AS "?column?" FROM tt1 tt1_1 WHERE tt1.y1 =3D tt1_1.f1)); = @@ -509,7 +509,7 @@ tt1.f2, tt1.f3 FROM tt1 - WHERE (EXISTS ( SELECT 1 + WHERE (EXISTS ( SELECT 1 AS "?column?" FROM a1 WHERE tt1.f1 =3D a1.x1)); = @@ -525,7 +525,7 @@ a1.f2, a1.f3 FROM tt1 a1 - WHERE (EXISTS ( SELECT 1 + WHERE (EXISTS ( SELECT 1 AS "?column?" FROM a1 a1_1 WHERE a1.f1 =3D a1_1.x1)); = @@ -541,7 +541,7 @@ tt1.f2, tt1.f3 FROM tt1 - WHERE (EXISTS ( SELECT 1 + WHERE (EXISTS ( SELECT 1 AS "?column?" FROM a1 a2 WHERE tt1.f1 =3D a2.x1)); = @@ -557,7 +557,7 @@ tt1.f2, tt1.f3 FROM temp_view_test.tt1 - WHERE (EXISTS ( SELECT 1 + WHERE (EXISTS ( SELECT 1 AS "?column?" FROM tt1 tt1_1 WHERE tt1.y1 =3D tt1_1.f1)); = @@ -574,7 +574,7 @@ a2.f2, a2.f3 FROM a2 - WHERE (EXISTS ( SELECT 1 + WHERE (EXISTS ( SELECT 1 AS "?column?" FROM a1 WHERE a2.f1 =3D a1.x1)); = @@ -590,7 +590,7 @@ a1.f2, a1.f3 FROM a2 a1 - WHERE (EXISTS ( SELECT 1 + WHERE (EXISTS ( SELECT 1 AS "?column?" FROM a1 a1_1 WHERE a1.f1 =3D a1_1.x1)); = @@ -606,7 +606,7 @@ a2.f2, a2.f3 FROM a2 - WHERE (EXISTS ( SELECT 1 + WHERE (EXISTS ( SELECT 1 AS "?column?" FROM a1 a2_1 WHERE a2.f1 =3D a2_1.x1)); = @@ -622,7 +622,7 @@ tt1.f2, tt1.f3 FROM temp_view_test.tt1 - WHERE (EXISTS ( SELECT 1 + WHERE (EXISTS ( SELECT 1 AS "?column?" FROM a2 WHERE tt1.y1 =3D a2.f1)); = @@ -639,7 +639,7 @@ a2.f2, a2.f3 FROM a2 - WHERE (EXISTS ( SELECT 1 + WHERE (EXISTS ( SELECT 1 AS "?column?" FROM tt1 WHERE a2.f1 =3D tt1.x1)); = @@ -655,7 +655,7 @@ a1.f2, a1.f3 FROM a2 a1 - WHERE (EXISTS ( SELECT 1 + WHERE (EXISTS ( SELECT 1 AS "?column?" FROM tt1 WHERE a1.f1 =3D tt1.x1)); = @@ -671,7 +671,7 @@ a2.f2, a2.f3 FROM a2 - WHERE (EXISTS ( SELECT 1 + WHERE (EXISTS ( SELECT 1 AS "?column?" FROM tt1 a2_1 WHERE a2.f1 =3D a2_1.x1)); = @@ -687,7 +687,7 @@ tt1.f2, tt1.f3 FROM temp_view_test.tt1 - WHERE (EXISTS ( SELECT 1 + WHERE (EXISTS ( SELECT 1 AS "?column?" FROM a2 WHERE tt1.y1 =3D a2.f1)); = @@ -705,7 +705,7 @@ tx1.f2, tx1.f3 FROM temp_view_test.tx1 - WHERE (EXISTS ( SELECT 1 + WHERE (EXISTS ( SELECT 1 AS "?column?" FROM tt1 WHERE tx1.f1 =3D tt1.x1)); = @@ -721,7 +721,7 @@ a1.f2, a1.f3 FROM temp_view_test.tx1 a1 - WHERE (EXISTS ( SELECT 1 + WHERE (EXISTS ( SELECT 1 AS "?column?" FROM tt1 WHERE a1.f1 =3D tt1.x1)); = @@ -737,7 +737,7 @@ tx1.f2, tx1.f3 FROM temp_view_test.tx1 - WHERE (EXISTS ( SELECT 1 + WHERE (EXISTS ( SELECT 1 AS "?column?" FROM tt1 a2 WHERE tx1.f1 =3D a2.x1)); = @@ -753,7 +753,7 @@ tt1.f2, tt1.f3 FROM temp_view_test.tt1 - WHERE (EXISTS ( SELECT 1 + WHERE (EXISTS ( SELECT 1 AS "?column?" FROM temp_view_test.tx1 WHERE tt1.y1 =3D tx1.f1)); = @@ -772,7 +772,7 @@ tx1.f2, tx1.f3 FROM temp_view_test.tx1 - WHERE (EXISTS ( SELECT 1 + WHERE (EXISTS ( SELECT 1 AS "?column?" FROM tt1 WHERE tx1.f1 =3D tt1.x1)); = @@ -788,7 +788,7 @@ a1.f2, a1.f3 FROM temp_view_test.tx1 a1 - WHERE (EXISTS ( SELECT 1 + WHERE (EXISTS ( SELECT 1 AS "?column?" FROM tt1 WHERE a1.f1 =3D tt1.x1)); = @@ -804,7 +804,7 @@ tx1.f2, tx1.f3 FROM temp_view_test.tx1 - WHERE (EXISTS ( SELECT 1 + WHERE (EXISTS ( SELECT 1 AS "?column?" FROM tt1 a2 WHERE tx1.f1 =3D a2.x1)); = @@ -820,7 +820,7 @@ tx1.f2, tx1.f3 FROM tx1 - WHERE (EXISTS ( SELECT 1 + WHERE (EXISTS ( SELECT 1 AS "?column?" FROM temp_view_test.tx1 tx1_1 WHERE tx1.y1 =3D tx1_1.f1)); = diff -U3 /home/postgres/pgsql/src/test/regress/expected/create_function_sq= l.out /home/postgres/pgsql/src/test/regress/results/create_function_sql.ou= t --- /home/postgres/pgsql/src/test/regress/expected/create_function_sql.out= 2022-03-15 14:54:21.139676523 -0400 +++ /home/postgres/pgsql/src/test/regress/results/create_function_sql.out = 2022-05-20 11:14:00.599539001 -0400 @@ -384,14 +384,14 @@ (1 row) = SELECT pg_get_functiondef('functest_S_10'::regproc); - pg_get_functiondef = -------------------------------------------------------------------------- - CREATE OR REPLACE FUNCTION temp_func_test.functest_s_10(a text, b date)+ - RETURNS boolean + - LANGUAGE sql + - BEGIN ATOMIC + - SELECT ((a =3D 'abcd'::text) AND (b > '01-01-2001'::date)); = + - END + + pg_get_functiondef = = +-------------------------------------------------------------------------= - + CREATE OR REPLACE FUNCTION temp_func_test.functest_s_10(a text, b date) = + + RETURNS boolean = + + LANGUAGE sql = + + BEGIN ATOMIC = + + SELECT ((a =3D 'abcd'::text) AND (b > '01-01-2001'::date)) AS "?column?= ";+ + END = + = (1 row) = @@ -402,8 +402,8 @@ RETURNS boolean + LANGUAGE sql + BEGIN ATOMIC + - SELECT 1; + - SELECT false; + + SELECT 1 AS "?column?"; + + SELECT false AS "?column?"; + END + = (1 row) @@ -425,14 +425,14 @@ (1 row) = SELECT pg_get_functiondef('functest_S_16'::regproc); - pg_get_functiondef = = --------------------------------------------------------------------------= ------ - CREATE OR REPLACE FUNCTION temp_func_test.functest_s_16(a integer, b int= eger)+ - RETURNS void = + - LANGUAGE sql = + - BEGIN ATOMIC = + - INSERT INTO functest1 (i) SELECT (functest_s_16.a + functest_s_16.b); = + - END = + + pg_get_functiondef = = +-------------------------------------------------------------------------= -------------- + CREATE OR REPLACE FUNCTION temp_func_test.functest_s_16(a integer, b int= eger) + + RETURNS void = + + LANGUAGE sql = + + BEGIN ATOMIC = + + INSERT INTO functest1 (i) SELECT (functest_s_16.a + functest_s_16.b) A= S "?column?";+ + END = + = (1 row) = diff -U3 /home/postgres/pgsql/src/test/regress/expected/matview.out /home/= postgres/pgsql/src/test/regress/results/matview.out --- /home/postgres/pgsql/src/test/regress/expected/matview.out 2021-12-20 = 12:30:54.358454587 -0500 +++ /home/postgres/pgsql/src/test/regress/results/matview.out 2022-05-20 1= 1:14:02.077541009 -0400 @@ -347,11 +347,11 @@ ?column? | integer | | | | plain | = View definition: SELECT mvtest_vt1.moo, - 2 * mvtest_vt1.moo + 2 * mvtest_vt1.moo AS "?column?" FROM mvtest_vt1 UNION ALL SELECT mvtest_vt1.moo, - 3 * mvtest_vt1.moo + 3 * mvtest_vt1.moo AS "?column?" FROM mvtest_vt1; = CREATE MATERIALIZED VIEW mv_test2 AS SELECT moo, 2*moo FROM mvtest_vt2 UN= ION ALL SELECT moo, 3*moo FROM mvtest_vt2; @@ -363,11 +363,11 @@ ?column? | integer | | | | plain | = | = View definition: SELECT mvtest_vt2.moo, - 2 * mvtest_vt2.moo + 2 * mvtest_vt2.moo AS "?column?" FROM mvtest_vt2 UNION ALL SELECT mvtest_vt2.moo, - 3 * mvtest_vt2.moo + 3 * mvtest_vt2.moo AS "?column?" FROM mvtest_vt2; = CREATE MATERIALIZED VIEW mv_test3 AS SELECT * FROM mv_test2 WHERE moo =3D= 12345; diff -U3 /home/postgres/pgsql/src/test/regress/expected/rules.out /home/po= stgres/pgsql/src/test/regress/results/rules.out --- /home/postgres/pgsql/src/test/regress/expected/rules.out 2022-05-19 17= :33:41.008350365 -0400 +++ /home/postgres/pgsql/src/test/regress/results/rules.out 2022-05-20 11:= 14:03.175542500 -0400 @@ -2458,7 +2458,7 @@ array_agg(pg_mcv_list_items.frequency) AS most_common_freqs, array_agg(pg_mcv_list_items.base_frequency) AS most_common_ba= se_freqs FROM pg_mcv_list_items(sd.stxdmcv) pg_mcv_list_items(index, "v= alues", nulls, frequency, base_frequency)) m ON ((sd.stxdmcv IS NOT NULL))= ) - WHERE ((NOT (EXISTS ( SELECT 1 + WHERE ((NOT (EXISTS ( SELECT 1 AS "?column?" FROM (unnest(s.stxkeys) k(k) JOIN pg_attribute a ON (((a.attrelid =3D s.stxrelid) AND (a.= attnum =3D k.k)))) WHERE (NOT has_column_privilege(c.oid, a.attnum, 'select'::text= ))))) AND ((c.relrowsecurity =3D false) OR (NOT row_security_active(c.oid)= ))); diff -U3 /home/postgres/pgsql/src/test/regress/expected/with.out /home/pos= tgres/pgsql/src/test/regress/results/with.out --- /home/postgres/pgsql/src/test/regress/expected/with.out 2022-05-19 17:= 33:41.008350365 -0400 +++ /home/postgres/pgsql/src/test/regress/results/with.out 2022-05-20 11:1= 4:05.002544982 -0400 @@ -442,7 +442,7 @@ WITH RECURSIVE t(n) AS ( VALUES (1) UNION ALL - SELECT t_1.n + 1 + SELECT t_1.n + 1 AS "?column?" FROM t t_1 WHERE t_1.n < 100 ) ------- =_aaaaaaaaaa0--