From: Tom Lane <tgl@sss.pgh.pa.us>
To: Daniel Gustafsson <daniel@yesql.se>
Cc: n.lutic@loxodata.com
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17486: [pg_restore] Restoring a view fails if this view contains an attribute without alias name.
Date: Fri, 20 May 2022 11:19:33 -0400
Message-ID: <347193.1653059973@sss.pgh.pa.us> (raw)
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>
Daniel Gustafsson <daniel@yesql.se> writes:
>> On 20 May 2022, at 16:34, Tom Lane <tgl@sss.pgh.pa.us> 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 column
>> name to start with.
> That might be the path of least confusion, and as you rightly say, if you 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
Attachments:
[text/x-diff] tweak-ruleutils-column-name-rule.patch (516B, ../347193.1653059973@sss.pgh.pa.us/2-tweak-ruleutils-column-name-rule.patch)
download | inline diff:diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.cindex 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 *context,
else
{
get_rule_expr((Node *) tle->expr, context, true);
- /* We'll show the AS name unless it's this: */- attname = "?column?";+ /* Always show the assigned column name explicitly. */+ attname = NULL;
}
/*
[text/x-diff] regression.diffs (11.2K, ../347193.1653059973@sss.pgh.pa.us/3-regression.diffs)
download | inline diff:
diff -U3 /home/postgres/pgsql/src/test/regress/expected/create_view.out /home/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 = 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 = 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 = 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 = 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 = 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 = 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 = 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 = 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 = 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 = 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 = 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 = 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 = 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 = 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 = 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 = 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 = 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 = 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 = 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 = 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 = 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 = 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 = 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 = tx1_1.f1));
diff -U3 /home/postgres/pgsql/src/test/regress/expected/create_function_sql.out /home/postgres/pgsql/src/test/regress/results/create_function_sql.out
--- /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 = '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 = '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 integer)+- 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 integer) ++ RETURNS void ++ LANGUAGE sql ++ BEGIN ATOMIC ++ INSERT INTO functest1 (i) SELECT (functest_s_16.a + functest_s_16.b) AS "?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 11: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 UNION 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 = 12345;
diff -U3 /home/postgres/pgsql/src/test/regress/expected/rules.out /home/postgres/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_base_freqs
FROM pg_mcv_list_items(sd.stxdmcv) pg_mcv_list_items(index, "values", 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 = s.stxrelid) AND (a.attnum = k.k))))
WHERE (NOT has_column_privilege(c.oid, a.attnum, 'select'::text))))) AND ((c.relrowsecurity = false) OR (NOT row_security_active(c.oid))));
diff -U3 /home/postgres/pgsql/src/test/regress/expected/with.out /home/postgres/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:14: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
)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, daniel@yesql.se, 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: <347193.1653059973@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox