Re: BUG #17486: [pg_restore] Restoring a view fails if this view contains an attribute without alias name.

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

diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.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 *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;
         }

         /*
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
         )