Re: magical eref alias names - Mailing list pgsql-hackers

Robert Haas <robertmhaas@gmail.com> writes:
> Oh, I agree, but I don't see why anyone would care whether rel names
> are unique across different queries. When I mentioned global
> uniqueness, I meant unique within a query, like what
> set_rtable_names() does after the fact.

Okay, but then we still have the problem of how to ensure that in
a query that has inline'd some views.  I think solving the sort of
I-want-to-reference-this problem you describe would require
that we unique-ify the aliases in the rewriter, just after it
finishes incorporating any views.  We could do that, but it seems
like a lot of cycles to expend on something that would be pointless
in the typical case where nobody ever looks at the aliases later.

> Now, I'm firmly convinced that this is a real problem and worth
> solving, but let me be clear that I don't think the solution is
> anywhere on this thread, nor do I think that it is simple.

Agreed.

> My original
> proposal of getting rid of system-generated fake names isn't
> necessary, because you very helpfully pointed out that I can look at
> whether RTE->alias->aliasname exists to figure that out.

Actually, I noticed that we are failing to honor that in the places
where we inject "*SELECT*" and "*SELECT* %d" names, because that
code puts those names into RTE->alias not only RTE->eref.
I experimented with the attached patch to not do that anymore,
which is sort of a subset of what you did but just focused on
not lying about what's generated versus user-written.  We could
alternatively keep the current generated names by extending
addRangeTableEntryForSubquery's API so that alias and generated eref
are passed separately.  (I didn't look to see if anyplace else
is messing up this distinction similarly.)

            regards, tom lane

diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index bf322198a2..3d8a433c19 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -4961,13 +4961,13 @@ SELECT ft1.c1 FROM ft1 JOIN ft2 on ft1.c1 = ft2.c1 WHERE
 -- ===================================================================
 EXPLAIN (verbose, costs off)
 INSERT INTO ft2 (c1,c2,c3) SELECT c1+1000,c2+100, c3 || c3 FROM ft2 LIMIT 20;
-
QUERYPLAN
     

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+
     QUERY PLAN
                    

+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  Insert on public.ft2
    Remote SQL: INSERT INTO "S 1"."T 1"("C 1", c2, c3, c4, c5, c6, c7, c8) VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
    Batch Size: 1
-   ->  Subquery Scan on "*SELECT*"
-         Output: "*SELECT*"."?column?", "*SELECT*"."?column?_1", NULL::integer, "*SELECT*"."?column?_2",
NULL::timestampwith time zone, NULL::timestamp without time zone, NULL::character varying, 'ft2       '::character(10),
NULL::user_enum
+   ->  Subquery Scan on unnamed_subquery
+         Output: unnamed_subquery."?column?", unnamed_subquery."?column?_1", NULL::integer,
unnamed_subquery."?column?_2",NULL::timestamp with time zone, NULL::timestamp without time zone, NULL::character
varying,'ft2       '::character(10), NULL::user_enum 
          ->  Foreign Scan on public.ft2 ft2_1
                Output: (ft2_1.c1 + 1000), (ft2_1.c2 + 100), (ft2_1.c3 || ft2_1.c3)
                Remote SQL: SELECT "C 1", c2, c3 FROM "S 1"."T 1" LIMIT 20::bigint
diff --git a/src/backend/executor/functions.c b/src/backend/executor/functions.c
index 3a2d51c5ad..c58bbd5b78 100644
--- a/src/backend/executor/functions.c
+++ b/src/backend/executor/functions.c
@@ -1962,7 +1962,7 @@ tlist_coercion_finished:
         rte = makeNode(RangeTblEntry);
         rte->rtekind = RTE_SUBQUERY;
         rte->subquery = parse;
-        rte->eref = rte->alias = makeAlias("*SELECT*", colnames);
+        rte->eref = makeAlias("unnamed_subquery", colnames);
         rte->lateral = false;
         rte->inh = false;
         rte->inFromCl = true;
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index 3864a675d2..359d3c390e 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -823,7 +823,7 @@ transformInsertStmt(ParseState *pstate, InsertStmt *stmt)
          */
         nsitem = addRangeTableEntryForSubquery(pstate,
                                                selectQuery,
-                                               makeAlias("*SELECT*", NIL),
+                                               NULL,
                                                false,
                                                false);
         addNSItemToQuery(pstate, nsitem, true, false, false);
@@ -2147,7 +2147,6 @@ transformSetOperationTree(ParseState *pstate, SelectStmt *stmt,
     {
         /* Process leaf SELECT */
         Query       *selectQuery;
-        char        selectName[32];
         ParseNamespaceItem *nsitem;
         RangeTblRef *rtr;
         ListCell   *tl;
@@ -2203,11 +2202,9 @@ transformSetOperationTree(ParseState *pstate, SelectStmt *stmt,
         /*
          * Make the leaf query be a subquery in the top-level rangetable.
          */
-        snprintf(selectName, sizeof(selectName), "*SELECT* %d",
-                 list_length(pstate->p_rtable) + 1);
         nsitem = addRangeTableEntryForSubquery(pstate,
                                                selectQuery,
-                                               makeAlias(selectName, NIL),
+                                               NULL,
                                                false,
                                                false);

diff --git a/src/test/regress/expected/rangefuncs.out b/src/test/regress/expected/rangefuncs.out
index 397a8b35d6..7f9c441f90 100644
--- a/src/test/regress/expected/rangefuncs.out
+++ b/src/test/regress/expected/rangefuncs.out
@@ -2130,10 +2130,10 @@ select testrngfunc();

 explain (verbose, costs off)
 select * from testrngfunc();
-                        QUERY PLAN
-----------------------------------------------------------
- Subquery Scan on "*SELECT*"
-   Output: "*SELECT*"."?column?", "*SELECT*"."?column?_1"
+                              QUERY PLAN
+----------------------------------------------------------------------
+ Subquery Scan on unnamed_subquery
+   Output: unnamed_subquery."?column?", unnamed_subquery."?column?_1"
    ->  Unique
          Output: (1), (2)
          ->  Sort
diff --git a/src/test/regress/expected/union.out b/src/test/regress/expected/union.out
index caa8fe70a0..cde8154220 100644
--- a/src/test/regress/expected/union.out
+++ b/src/test/regress/expected/union.out
@@ -942,7 +942,7 @@ SELECT q1 FROM int8_tbl EXCEPT SELECT q2 FROM int8_tbl ORDER BY q2 LIMIT 1;
 ERROR:  column "q2" does not exist
 LINE 1: ... int8_tbl EXCEPT SELECT q2 FROM int8_tbl ORDER BY q2 LIMIT 1...
                                                              ^
-DETAIL:  There is a column named "q2" in table "*SELECT* 2", but it cannot be referenced from this part of the query.
+DETAIL:  There is a column named "q2" in table "unnamed_subquery", but it cannot be referenced from this part of the
query.
 -- But this should work:
 SELECT q1 FROM int8_tbl EXCEPT (((SELECT q2 FROM int8_tbl ORDER BY q2 LIMIT 1))) ORDER BY 1;
         q1
@@ -1338,14 +1338,14 @@ where q2 = q2;
 ----------------------------------------------------------
  Unique
    ->  Merge Append
-         Sort Key: "*SELECT* 1".q1
-         ->  Subquery Scan on "*SELECT* 1"
+         Sort Key: unnamed_subquery.q1
+         ->  Subquery Scan on unnamed_subquery
                ->  Unique
                      ->  Sort
                            Sort Key: i81.q1, i81.q2
                            ->  Seq Scan on int8_tbl i81
                                  Filter: (q2 IS NOT NULL)
-         ->  Subquery Scan on "*SELECT* 2"
+         ->  Subquery Scan on unnamed_subquery_1
                ->  Unique
                      ->  Sort
                            Sort Key: i82.q1, i82.q2
@@ -1374,14 +1374,14 @@ where -q1 = q2;
 --------------------------------------------------------
  Unique
    ->  Merge Append
-         Sort Key: "*SELECT* 1".q1
-         ->  Subquery Scan on "*SELECT* 1"
+         Sort Key: unnamed_subquery.q1
+         ->  Subquery Scan on unnamed_subquery
                ->  Unique
                      ->  Sort
                            Sort Key: i81.q1, i81.q2
                            ->  Seq Scan on int8_tbl i81
                                  Filter: ((- q1) = q2)
-         ->  Subquery Scan on "*SELECT* 2"
+         ->  Subquery Scan on unnamed_subquery_1
                ->  Unique
                      ->  Sort
                            Sort Key: i82.q1, i82.q2