Hi Tatsuo, Jian,
Attached as a wip patch is one change I would like an opinion on
before it goes into the series. It is not in v51; it sits after that
posting on my branch.
The case
It is in the regression suite already, written down as a failure
rather than as a fix. The commit that made get_rule_define() print
bare column names put it there, and said the ambiguity would be closed
later in the series by the commit that pins those names. This is that
commit.
Two tables joined, a DEFINE that names a column of one of them, and
then the other side acquires a column of the same name:
CREATE TEMP TABLE sa (id int, price int);
CREATE TEMP TABLE sb (id int, qty int);
CREATE TEMP VIEW sv AS
SELECT a.id, count(*) OVER w AS cnt
FROM sa a JOIN sb b ON a.id = b.id
WINDOW w AS (ORDER BY a.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED
FOLLOWING PATTERN (UP+) DEFINE UP AS price > 0);
ALTER TABLE sb ADD COLUMN price int;
pg_get_viewdef() then prints "up AS price > 0" unchanged, and that
text no longer means anything. Feeding it back gives
ERROR: column reference "price" is ambiguous
The test carried that state deliberately: the comment on sv said it
is unrestorable and that is why it had to be temporary and dropped
at the end, sv3 showed the deparsed text being rejected on a fresh
CREATE VIEW, and sv4 showed the column alias list that a person would
have to write by hand for the view to survive.
The reason the deparser has nothing better to print is that the
standard gives the qualifier slot in a DEFINE expression to pattern
variables. A qualified column is not something the parser reads back
there -- every qualified spelling is rejected -- which leaves the bare
name as the only re-parseable one, and that is why that earlier commit
sets varprefix to false for the clause. So the bare name has to
resolve exactly as printed. A column merged by USING is in the same
position, and that is where I took the implementation from.
What it prints now
The same query, under the names the patch's own test uses, with the
psql padding taken out:
SELECT j1.id,
count(*) OVER w AS cnt
FROM rpr_pin_j1 j1
JOIN rpr_pin_j2 j2(id, qty, price_1) ON j1.id = j2.id
WINDOW w AS (ORDER BY j1.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED
FOLLOWING
AFTER MATCH SKIP PAST LAST ROW
INITIAL
PATTERN (a+)
DEFINE
a AS price > 0);
The alias list sv4 had to carry by hand is what the deparser emits on
its own now: the newcomer moves aside to price_1, the DEFINE reference
keeps meaning what it meant, and the view restores.
How
set_using_names() already meets the same problem for a column merged
by USING: it too is printed bare, so it too has to keep resolving. It
reserves the name -- no other relation of the query may be given it,
and the column itself is exempt from renaming -- and the rest of the
deparser reacts on its own. The alias list above is that reaction,
not new code.
set_define_names() is the same reservation made for DEFINE references.
It runs once, right after set_using_names(), walks each DEFINE clause,
and pins the name of every column the clause reads.
The collision is then settled by machinery that was already there. A
column whose name is reserved is passed over when aliases are assigned,
so it prints as written; and when the same pass reaches the relation
that acquired the colliding name, that name no longer passes the
uniqueness test, so it is treated as any other duplicate -- a digit is
appended, and a column alias list is printed for that relation so the
new name is declared where it is used.
Whether the direction taken here is the right one, whether there is a
defect in it, and whether it carries a side effect I have not seen --
I would be grateful for your opinion.
Best regards,
Henson
From 48826523d0578527af2a10bea8bb0020b9dbc80f Mon Sep 17 00:00:00 2001
From: Henson Choi <[email protected]>
Date: Sat, 15 Aug 2026 09:38:55 +0900
Subject: [PATCH] Pin the column names a DEFINE clause references when
deparsing
A DEFINE clause names a column without a qualifier: the qualifier slot
belongs to the pattern variable, and get_rule_define() deparses with
varprefix off for that reason. The bare name therefore has to resolve
exactly as printed. When another relation of the same query acquires a
column of that name, the deparsed view no longer re-parses and pg_dump
output fails to restore.
Reserve those names the way set_using_names() reserves a merged USING
name: register each one in dpns->using_names so no other RTE can be
assigned it, and store it into the owning RTE's colnames entry, which
exempts the column itself from being renamed. set_relation_column_names()
then uniquifies the intruding column to name_1 and prints a column alias
list for its RTE, and the definition round-trips again. A name already
chosen by set_using_names(), for a join's merged column or one of its
inputs, is reserved as it stands.
Which name to reserve is the whole of it, and a Var can carry two. When
it reads a column of an aliased join, varno holds the child relation the
reference was flattened to and varnosyn holds the join, and get_variable()
prints from varnosyn whenever it is set and a parse tree is being
deparsed. Resolve the same way here. Reserving the child's name instead
protects a name that never reaches the output and leaves the printed one
free, so the collision this commit exists to prevent still happens:
CREATE VIEW v AS SELECT count(*) OVER w AS c
FROM (ka JOIN kb ON ka.i = kb.k) j(p, q, r, s), kd
WINDOW w AS (ORDER BY kd.m ROWS BETWEEN CURRENT ROW AND UNBOUNDED
FOLLOWING INITIAL PATTERN (a) DEFINE a AS q > 0);
ALTER TABLE kd ADD COLUMN q int;
-- feeding pg_get_viewdef() back:
ERROR: column reference "q" is ambiguous
A join RTE is therefore no longer skipped when the name is taken from
eref->colnames. The branch that skipped it reasoned that a join column
takes its name from the child later on and the child is pinned in its own
right, which is true and is exactly why the printed name was left
unguarded.
One consequence is deliberate. dpns->using_names is consulted by
colname_is_unique() for every RTE of the query, so reserving a join's
column name also pushes aside a column of that name in one of the join's
own inputs -- even though an aliased join hides its inputs and that name
could not have been referenced. Narrowing the reservation means changing
what colname_is_unique() does with using_names, which serves USING columns
as much as this, so the wider reservation stands and only the deparsed
text carries the extra alias list.
No alias list appears unless a collision actually occurs, and nothing
happens for a query without a DEFINE clause.
The restriction this rests on -- that a DEFINE clause names a column
without a qualifier, so the name must be unique across the FROM clause
-- is now stated on the SELECT reference page.
---
doc/src/sgml/ref/select.sgml | 12 +
src/backend/utils/adt/ruleutils.c | 135 +++++++++++
src/test/regress/expected/rpr_base.out | 310 +++++++++++++++++++------
src/test/regress/sql/rpr_base.sql | 193 +++++++++++----
4 files changed, 543 insertions(+), 107 deletions(-)
diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml
index 0a03957fd23..fbdc89f8b7e 100644
--- a/doc/src/sgml/ref/select.sgml
+++ b/doc/src/sgml/ref/select.sgml
@@ -1228,6 +1228,18 @@ DEFINE <replaceable
class="parameter">definition_variable_name</replaceable> AS
to <literal>WITH RECURSIVE</literal>.
</para>
+ <para>
+ A column reference in the <literal>DEFINE</literal> clause must be
+ written without a qualifier. The SQL standard reserves the qualifier
+ slot for a pattern variable, so a table name or alias is rejected
+ there, and a pattern variable qualifier is rejected as not supported.
+ The column name therefore has to resolve on its own across the
+ whole <literal>FROM</literal> clause, and an ambiguous name is
+ rejected. Where the name is not unique, rename the column in
+ the <literal>FROM</literal> clause, for example with a column alias on
+ a subquery.
+ </para>
+
<para>
The purpose of a <literal>WINDOW</literal> clause is to specify the
behavior of <firstterm>window functions</firstterm> appearing in the
query's
diff --git a/src/backend/utils/adt/ruleutils.c
b/src/backend/utils/adt/ruleutils.c
index 756606f272f..ef03a3fdd70 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -390,6 +390,9 @@ static void set_simple_column_names(deparse_namespace
*dpns);
static bool has_dangerous_join_using(deparse_namespace *dpns, Node *jtnode);
static void set_using_names(deparse_namespace *dpns, Node *jtnode,
List *parentUsing);
+static void set_define_names(deparse_namespace *dpns, Query *query);
+static bool set_define_names_walker(Node *node, deparse_namespace *dpns);
+static void pin_define_colname(deparse_namespace *dpns, Var *var);
static void set_relation_column_names(deparse_namespace *dpns,
RangeTblEntry *rte,
deparse_columns *colinfo);
@@ -4433,6 +4436,12 @@ set_deparse_for_query(deparse_namespace *dpns, Query
*query,
* the query jointree.
*/
set_using_names(dpns, (Node *) query->jointree, NIL);
+
+ /*
+ * Pin the column names that DEFINE clauses reference, so that
they
+ * still resolve as written when the query is re-parsed.
+ */
+ set_define_names(dpns, query);
}
/*
@@ -4735,6 +4744,132 @@ set_using_names(deparse_namespace *dpns, Node *jtnode,
List *parentUsing)
(int) nodeTag(jtnode));
}
+/*
+ * set_define_names: pin the column names that DEFINE clauses reference
+ *
+ * Within a DEFINE clause a column can only be named without a qualifier,
+ * since the qualifier slot names a pattern variable; get_rule_define()
+ * deparses with varprefix off for that reason. So an unqualified reference
+ * there has to resolve exactly as printed, much like a column merged by
+ * USING. If another relation of the same query later acquires a column of
+ * that name, re-parsing the deparsed query would find the name ambiguous.
+ *
+ * We therefore treat such a name the way set_using_names() treats a merged
+ * USING name: register it in dpns->using_names, so that no other RTE can be
+ * given that name, and store it into the owning RTE's colnames entry, which
+ * exempts the column itself from being renamed. set_relation_column_names()
+ * then does the rest, uniquifying the intruding column to name_1 and printing
+ * a column alias list for its RTE.
+ */
+static void
+set_define_names(deparse_namespace *dpns, Query *query)
+{
+ ListCell *lc;
+
+ foreach(lc, query->windowClause)
+ {
+ WindowClause *wc = lfirst_node(WindowClause, lc);
+
+ if (wc->defineClause != NIL)
+ (void) set_define_names_walker((Node *)
wc->defineClause, dpns);
+ }
+}
+
+/*
+ * Walk a DEFINE clause, pinning the name of every column it references.
+ */
+static bool
+set_define_names_walker(Node *node, deparse_namespace *dpns)
+{
+ if (node == NULL)
+ return false;
+ if (IsA(node, Var))
+ {
+ pin_define_colname(dpns, (Var *) node);
+ return false;
+ }
+ /* Sub-selects are not allowed here, but be safe: they have own
namespace */
+ if (IsA(node, Query))
+ return false;
+ return expression_tree_walker(node, set_define_names_walker, dpns);
+}
+
+/*
+ * pin_define_colname: reserve the printed name of one DEFINE-referenced column
+ */
+static void
+pin_define_colname(deparse_namespace *dpns, Var *var)
+{
+ RangeTblEntry *rte;
+ deparse_columns *colinfo;
+ int varno;
+ AttrNumber attno;
+ char *colname;
+ ListCell *lc;
+
+ /*
+ * Resolve the reference the way get_variable() will when it prints this
+ * Var, or the name reserved here is not the name that reaches the
output.
+ * A Var that reads a join column carries the child relation in varno
and
+ * the join RTE in varnosyn, and it is the latter that gets printed.
+ */
+ if (var->varnosyn > 0 && dpns->plan == NULL)
+ {
+ varno = var->varnosyn;
+ attno = var->varattnosyn;
+ }
+ else
+ {
+ varno = var->varno;
+ attno = var->varattno;
+ }
+
+ /* Only ordinary columns of this query level have a name to pin */
+ if (var->varlevelsup != 0 || attno <= 0)
+ return;
+ if (varno < 1 || varno > list_length(dpns->rtable))
+ return;
+
+ rte = rt_fetch(varno, dpns->rtable);
+ colinfo = deparse_columns_fetch(varno, dpns);
+
+ /*
+ * Find the name this column is going to be printed with. If a name was
+ * assigned already, that is the one to protect; set_using_names() does
+ * that both for a join's merged column and for the input columns it was
+ * merged from.
+ */
+ if (attno <= colinfo->num_cols && colinfo->colnames[attno - 1] != NULL)
+ colname = colinfo->colnames[attno - 1];
+ else if (rte->rtekind == RTE_RELATION)
+ {
+ /* Consult the catalogs, as set_relation_column_names() will */
+ colname = get_attname(rte->relid, attno, true);
+ if (colname == NULL)
+ return; /* dropped column */
+ expand_colnames_array_to(colinfo, attno);
+ colinfo->colnames[attno - 1] = colname;
+ }
+ else if (attno <= list_length(rte->eref->colnames))
+ {
+ colname = strVal(list_nth(rte->eref->colnames, attno - 1));
+ if (colname[0] == '\0')
+ return; /* dropped column */
+ expand_colnames_array_to(colinfo, attno);
+ colinfo->colnames[attno - 1] = colname;
+ }
+ else
+ return;
+
+ /* Reserve the name query-wide, unless it is reserved already */
+ foreach(lc, dpns->using_names)
+ {
+ if (strcmp((char *) lfirst(lc), colname) == 0)
+ return;
+ }
+ dpns->using_names = lappend(dpns->using_names, colname);
+}
+
/*
* set_relation_column_names: select column aliases for a non-join RTE
*
diff --git a/src/test/regress/expected/rpr_base.out
b/src/test/regress/expected/rpr_base.out
index bd6904ecc66..8d173221511 100644
--- a/src/test/regress/expected/rpr_base.out
+++ b/src/test/regress/expected/rpr_base.out
@@ -3693,83 +3693,261 @@ SELECT pg_get_viewdef('rpr_serial_join'::regclass);
up AS (val > 0));
(1 row)
--- Ambiguity introduced after the view was created: ALTER TABLE adds a column
--- whose name already appears in the other side of the join, so the deparser
--- must qualify or alias it. sv3 shows the same text is rejected on a fresh
--- CREATE VIEW; sv4 shows the alias form that survives. These stay temporary
--- and are dropped at the end: sv is deliberately unrestorable, so leaving it
--- in place would hand pg_dump a view that cannot be restored.
-CREATE TEMP TABLE sa (id int, price int);
-CREATE TEMP TABLE sb (id int, qty int);
-INSERT INTO sa VALUES (1,10),(2,20);
-INSERT INTO sb VALUES (1,5),(2,7);
-CREATE TEMP VIEW sv AS
-SELECT a.id, count(*) OVER w AS cnt
-FROM sa a JOIN sb b ON a.id = b.id
-WINDOW w AS (ORDER BY a.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
- PATTERN (UP+) DEFINE UP AS price > 0);
-ALTER TABLE sb ADD COLUMN price int;
-SELECT pg_get_viewdef('sv'::regclass, true);
- pg_get_viewdef
--------------------------------------------------------------------------------
- SELECT a.id, +
- count(*) OVER w AS cnt +
- FROM sa a +
- JOIN sb b ON a.id = b.id +
- WINDOW w AS (ORDER BY a.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+
- AFTER MATCH SKIP PAST LAST ROW +
- INITIAL +
- PATTERN (up+) +
- DEFINE +
- up AS price > 0);
+-- A DEFINE clause can only name a column without a qualifier, so the name has
+-- to resolve exactly as printed. When another relation of the query acquires
+-- a column of that name, the deparser pushes the newcomer aside with a column
+-- alias list, the same way it protects a column merged by USING.
+CREATE TABLE rpr_pin (id INT, val INT);
+CREATE TABLE rpr_pin_other (id INT);
+INSERT INTO rpr_pin VALUES (1, 10), (2, 20), (3, 15);
+INSERT INTO rpr_pin_other VALUES (1), (2), (3);
+CREATE VIEW rpr_pin_v AS
+SELECT count(*) OVER w AS cnt
+FROM rpr_pin, rpr_pin_other
+WHERE rpr_pin.id = rpr_pin_other.id
+WINDOW w AS (ORDER BY rpr_pin.id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+)
+ DEFINE A AS val > 0);
+-- names reached through a navigation operation are pinned too
+CREATE VIEW rpr_pin_nav_v AS
+SELECT count(*) OVER w AS cnt
+FROM rpr_pin, rpr_pin_other
+WHERE rpr_pin.id = rpr_pin_other.id
+WINDOW w AS (ORDER BY rpr_pin.id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+)
+ DEFINE A AS PREV(val) < val);
+-- no collision yet, so no column alias list
+SELECT pg_get_viewdef('rpr_pin_v'::regclass, true);
+ pg_get_viewdef
+-------------------------------------------------------------------------------------
+ SELECT count(*) OVER w AS cnt
+
+ FROM rpr_pin,
+
+ rpr_pin_other
+
+ WHERE rpr_pin.id = rpr_pin_other.id
+
+ WINDOW w AS (ORDER BY rpr_pin.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED
FOLLOWING+
+ AFTER MATCH SKIP PAST LAST ROW
+
+ INITIAL
+
+ PATTERN (a+)
+
+ DEFINE
+
+ a AS val > 0);
(1 row)
--- ERROR: the deparsed text above no longer re-parses
-CREATE TEMP VIEW sv3 AS
-SELECT a.id, count(*) OVER w AS cnt
-FROM sa a JOIN sb b ON a.id = b.id
-WINDOW w AS (ORDER BY a.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
- PATTERN (UP+) DEFINE UP AS price > 0);
-ERROR: column reference "price" is ambiguous
-LINE 5: PATTERN (UP+) DEFINE UP AS price > 0);
- ^
-CREATE TEMP VIEW sv4 AS
-SELECT a.id, count(*) OVER w AS cnt
-FROM sa a (id, price) JOIN sb b (id, qty, price_1) ON a.id = b.id
-WINDOW w AS (ORDER BY a.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
- PATTERN (UP+) DEFINE UP AS price > 0);
-SELECT pg_get_viewdef('sv4'::regclass, true);
- pg_get_viewdef
--------------------------------------------------------------------------------
- SELECT a.id, +
- count(*) OVER w AS cnt +
- FROM sa a +
- JOIN sb b(id, qty, price_1) ON a.id = b.id +
- WINDOW w AS (ORDER BY a.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+
- AFTER MATCH SKIP PAST LAST ROW +
- INITIAL +
- PATTERN (up+) +
- DEFINE +
- up AS price > 0);
+ALTER TABLE rpr_pin_other ADD COLUMN val INT;
+SELECT pg_get_viewdef('rpr_pin_v'::regclass, true);
+ pg_get_viewdef
+-------------------------------------------------------------------------------------
+ SELECT count(*) OVER w AS cnt
+
+ FROM rpr_pin,
+
+ rpr_pin_other rpr_pin_other(id, val_1)
+
+ WHERE rpr_pin.id = rpr_pin_other.id
+
+ WINDOW w AS (ORDER BY rpr_pin.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED
FOLLOWING+
+ AFTER MATCH SKIP PAST LAST ROW
+
+ INITIAL
+
+ PATTERN (a+)
+
+ DEFINE
+
+ a AS val > 0);
(1 row)
-SELECT * FROM sv4;
- id | cnt
-----+-----
- 1 | 2
- 2 | 0
-(2 rows)
+SELECT pg_get_viewdef('rpr_pin_nav_v'::regclass, true);
+ pg_get_viewdef
+-------------------------------------------------------------------------------------
+ SELECT count(*) OVER w AS cnt
+
+ FROM rpr_pin,
+
+ rpr_pin_other rpr_pin_other(id, val_1)
+
+ WHERE rpr_pin.id = rpr_pin_other.id
+
+ WINDOW w AS (ORDER BY rpr_pin.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED
FOLLOWING+
+ AFTER MATCH SKIP PAST LAST ROW
+
+ INITIAL
+
+ PATTERN (a+)
+
+ DEFINE
+
+ a AS PREV(val) < val);
+(1 row)
-SELECT * FROM sv;
+-- and the deparsed text builds an identical view
+CREATE VIEW rpr_pin_v2 AS
+ SELECT count(*) OVER w AS cnt
+ FROM rpr_pin,
+ rpr_pin_other rpr_pin_other(id, val_1)
+ WHERE rpr_pin.id = rpr_pin_other.id
+ WINDOW w AS (ORDER BY rpr_pin.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED
FOLLOWING
+ AFTER MATCH SKIP PAST LAST ROW
+ INITIAL
+ PATTERN (a+)
+ DEFINE
+ a AS val > 0);
+SELECT pg_get_viewdef('rpr_pin_v'::regclass, true)
+ = pg_get_viewdef('rpr_pin_v2'::regclass, true) AS identical;
+ identical
+-----------
+ t
+(1 row)
+
+-- a column merged by USING is pinned the same way
+CREATE TABLE rpr_pin_l (x INT, y INT);
+CREATE TABLE rpr_pin_r (x INT, z INT);
+CREATE TABLE rpr_pin_x (id INT);
+CREATE VIEW rpr_pin_using_v AS
+SELECT count(*) OVER w AS cnt
+FROM rpr_pin_l JOIN rpr_pin_r USING (x), rpr_pin_x
+WINDOW w AS (ORDER BY rpr_pin_l.y
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+)
+ DEFINE A AS x > 0);
+ALTER TABLE rpr_pin_x ADD COLUMN x INT;
+SELECT pg_get_viewdef('rpr_pin_using_v'::regclass, true);
+ pg_get_viewdef
+--------------------------------------------------------------------------------------
+ SELECT count(*) OVER w AS cnt
+
+ FROM rpr_pin_l
+
+ JOIN rpr_pin_r USING (x),
+
+ rpr_pin_x rpr_pin_x(id, x_1)
+
+ WINDOW w AS (ORDER BY rpr_pin_l.y ROWS BETWEEN CURRENT ROW AND UNBOUNDED
FOLLOWING+
+ AFTER MATCH SKIP PAST LAST ROW
+
+ INITIAL
+
+ PATTERN (a+)
+
+ DEFINE
+
+ a AS x > 0);
+(1 row)
+
+-- a JOIN ... ON behaves the same, and the view keeps returning its rows
+CREATE TABLE rpr_pin_j1 (id INT, price INT);
+CREATE TABLE rpr_pin_j2 (id INT, qty INT);
+INSERT INTO rpr_pin_j1 VALUES (1, 10), (2, 20);
+INSERT INTO rpr_pin_j2 VALUES (1, 5), (2, 7);
+CREATE VIEW rpr_pin_on_v AS
+SELECT j1.id, count(*) OVER w AS cnt
+FROM rpr_pin_j1 j1 JOIN rpr_pin_j2 j2 ON j1.id = j2.id
+WINDOW w AS (ORDER BY j1.id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+)
+ DEFINE A AS price > 0);
+ALTER TABLE rpr_pin_j2 ADD COLUMN price INT;
+SELECT pg_get_viewdef('rpr_pin_on_v'::regclass, true);
+ pg_get_viewdef
+--------------------------------------------------------------------------------
+ SELECT j1.id,
+
+ count(*) OVER w AS cnt
+
+ FROM rpr_pin_j1 j1
+
+ JOIN rpr_pin_j2 j2(id, qty, price_1) ON j1.id = j2.id
+
+ WINDOW w AS (ORDER BY j1.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED
FOLLOWING+
+ AFTER MATCH SKIP PAST LAST ROW
+
+ INITIAL
+
+ PATTERN (a+)
+
+ DEFINE
+
+ a AS price > 0);
+(1 row)
+
+SELECT * FROM rpr_pin_on_v ORDER BY id;
id | cnt
----+-----
1 | 2
2 | 0
(2 rows)
-DROP VIEW sv4;
-DROP VIEW sv;
-DROP TABLE sa, sb;
+-- An aliased join hides its inputs, so the name that gets printed is the
join's
+-- own, taken from varnosyn, not the child column the Var carries in varno.
+-- Pinning the child instead would reserve a name that never reaches the output
+-- and leave the printed one free for a later column to collide with.
+CREATE TABLE rpr_pin_a (i INT, x INT);
+CREATE TABLE rpr_pin_b (k INT, y INT);
+CREATE TABLE rpr_pin_c (m INT);
+INSERT INTO rpr_pin_a VALUES (1, 10), (2, 20);
+INSERT INTO rpr_pin_b VALUES (1, 5), (2, 7);
+INSERT INTO rpr_pin_c VALUES (100), (200);
+CREATE VIEW rpr_pin_alias_v AS
+SELECT count(*) OVER w AS cnt
+FROM (rpr_pin_a JOIN rpr_pin_b ON rpr_pin_a.i = rpr_pin_b.k) j(p, q, r, s),
+ rpr_pin_c
+WINDOW w AS (ORDER BY rpr_pin_c.m
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A)
+ DEFINE A AS q > 0);
+ALTER TABLE rpr_pin_c ADD COLUMN q INT;
+SELECT pg_get_viewdef('rpr_pin_alias_v'::regclass, true);
+ pg_get_viewdef
+--------------------------------------------------------------------------------------
+ SELECT count(*) OVER w AS cnt
+
+ FROM (rpr_pin_a
+
+ JOIN rpr_pin_b ON rpr_pin_a.i = rpr_pin_b.k) j(p, q, r, s),
+
+ rpr_pin_c rpr_pin_c(m, q_1)
+
+ WINDOW w AS (ORDER BY rpr_pin_c.m ROWS BETWEEN CURRENT ROW AND UNBOUNDED
FOLLOWING+
+ AFTER MATCH SKIP PAST LAST ROW
+
+ INITIAL
+
+ PATTERN (a)
+
+ DEFINE
+
+ a AS q > 0);
+(1 row)
+
+SELECT * FROM rpr_pin_alias_v;
+ cnt
+-----
+ 1
+ 1
+ 1
+ 1
+(4 rows)
+
+-- and the deparsed text builds a view that returns the same rows
+CREATE VIEW rpr_pin_alias_v2 AS
+SELECT count(*) OVER w AS cnt
+FROM (rpr_pin_a JOIN rpr_pin_b ON rpr_pin_a.i = rpr_pin_b.k) j(p, q, r, s),
+ rpr_pin_c rpr_pin_c(m, q_1)
+WINDOW w AS (ORDER BY rpr_pin_c.m
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP PAST LAST ROW
+ INITIAL
+ PATTERN (a)
+ DEFINE a AS q > 0);
+SELECT * FROM rpr_pin_alias_v2;
+ cnt
+-----
+ 1
+ 1
+ 1
+ 1
+(4 rows)
+
+DROP VIEW rpr_pin_alias_v2;
+-- Without a user column alias list the join still keeps the printed name, and
+-- the input relation is the one that moves aside.
+CREATE VIEW rpr_pin_alias_v3 AS
+SELECT count(*) OVER w AS cnt
+FROM (rpr_pin_a JOIN rpr_pin_b ON rpr_pin_a.i = rpr_pin_b.k) j, rpr_pin_c
+WINDOW w AS (ORDER BY rpr_pin_c.m
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A)
+ DEFINE A AS x > 0);
+SELECT pg_get_viewdef('rpr_pin_alias_v3'::regclass, true);
+ pg_get_viewdef
+--------------------------------------------------------------------------------------
+ SELECT count(*) OVER w AS cnt
+
+ FROM (rpr_pin_a rpr_pin_a(i, x_1)
+
+ JOIN rpr_pin_b ON rpr_pin_a.i = rpr_pin_b.k) j(i, x, k, y),
+
+ rpr_pin_c
+
+ WINDOW w AS (ORDER BY rpr_pin_c.m ROWS BETWEEN CURRENT ROW AND UNBOUNDED
FOLLOWING+
+ AFTER MATCH SKIP PAST LAST ROW
+
+ INITIAL
+
+ PATTERN (a)
+
+ DEFINE
+
+ a AS x > 0);
+(1 row)
+
+DROP VIEW rpr_pin_alias_v3;
+DROP VIEW rpr_pin_alias_v;
+DROP TABLE rpr_pin_a, rpr_pin_b, rpr_pin_c;
+-- The same query written fresh is rejected, since nothing pins the name for
+-- it. Pinning is what lets the stored definition above still reparse.
+SELECT j1.id, count(*) OVER w AS cnt
+FROM rpr_pin_j1 j1 JOIN rpr_pin_j2 j2 ON j1.id = j2.id
+WINDOW w AS (ORDER BY j1.id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+)
+ DEFINE A AS price > 0);
+ERROR: column reference "price" is ambiguous
+LINE 6: DEFINE A AS price > 0);
+ ^
-- Materialized view (if supported)
CREATE TABLE rpr_mview (id INT, val INT);
INSERT INTO rpr_mview VALUES (1, 10), (2, 20), (3, 30);
diff --git a/src/test/regress/sql/rpr_base.sql
b/src/test/regress/sql/rpr_base.sql
index 361d0923c2a..06e2fda3bc0 100644
--- a/src/test/regress/sql/rpr_base.sql
+++ b/src/test/regress/sql/rpr_base.sql
@@ -2377,47 +2377,158 @@ WINDOW w AS (ORDER BY s.id ROWS BETWEEN CURRENT ROW
AND UNBOUNDED FOLLOWING
PATTERN (UP+) DEFINE UP AS val > 0);
SELECT pg_get_viewdef('rpr_serial_join'::regclass);
--- Ambiguity introduced after the view was created: ALTER TABLE adds a column
--- whose name already appears in the other side of the join, so the deparser
--- must qualify or alias it. sv3 shows the same text is rejected on a fresh
--- CREATE VIEW; sv4 shows the alias form that survives. These stay temporary
--- and are dropped at the end: sv is deliberately unrestorable, so leaving it
--- in place would hand pg_dump a view that cannot be restored.
-CREATE TEMP TABLE sa (id int, price int);
-CREATE TEMP TABLE sb (id int, qty int);
-INSERT INTO sa VALUES (1,10),(2,20);
-INSERT INTO sb VALUES (1,5),(2,7);
-
-CREATE TEMP VIEW sv AS
-SELECT a.id, count(*) OVER w AS cnt
-FROM sa a JOIN sb b ON a.id = b.id
-WINDOW w AS (ORDER BY a.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
- PATTERN (UP+) DEFINE UP AS price > 0);
-
-ALTER TABLE sb ADD COLUMN price int;
-
-SELECT pg_get_viewdef('sv'::regclass, true);
-
--- ERROR: the deparsed text above no longer re-parses
-CREATE TEMP VIEW sv3 AS
-SELECT a.id, count(*) OVER w AS cnt
-FROM sa a JOIN sb b ON a.id = b.id
-WINDOW w AS (ORDER BY a.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
- PATTERN (UP+) DEFINE UP AS price > 0);
-
-CREATE TEMP VIEW sv4 AS
-SELECT a.id, count(*) OVER w AS cnt
-FROM sa a (id, price) JOIN sb b (id, qty, price_1) ON a.id = b.id
-WINDOW w AS (ORDER BY a.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
- PATTERN (UP+) DEFINE UP AS price > 0);
-
-SELECT pg_get_viewdef('sv4'::regclass, true);
-SELECT * FROM sv4;
-SELECT * FROM sv;
-
-DROP VIEW sv4;
-DROP VIEW sv;
-DROP TABLE sa, sb;
+-- A DEFINE clause can only name a column without a qualifier, so the name has
+-- to resolve exactly as printed. When another relation of the query acquires
+-- a column of that name, the deparser pushes the newcomer aside with a column
+-- alias list, the same way it protects a column merged by USING.
+
+CREATE TABLE rpr_pin (id INT, val INT);
+CREATE TABLE rpr_pin_other (id INT);
+INSERT INTO rpr_pin VALUES (1, 10), (2, 20), (3, 15);
+INSERT INTO rpr_pin_other VALUES (1), (2), (3);
+
+CREATE VIEW rpr_pin_v AS
+SELECT count(*) OVER w AS cnt
+FROM rpr_pin, rpr_pin_other
+WHERE rpr_pin.id = rpr_pin_other.id
+WINDOW w AS (ORDER BY rpr_pin.id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+)
+ DEFINE A AS val > 0);
+
+-- names reached through a navigation operation are pinned too
+CREATE VIEW rpr_pin_nav_v AS
+SELECT count(*) OVER w AS cnt
+FROM rpr_pin, rpr_pin_other
+WHERE rpr_pin.id = rpr_pin_other.id
+WINDOW w AS (ORDER BY rpr_pin.id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+)
+ DEFINE A AS PREV(val) < val);
+
+-- no collision yet, so no column alias list
+SELECT pg_get_viewdef('rpr_pin_v'::regclass, true);
+
+ALTER TABLE rpr_pin_other ADD COLUMN val INT;
+
+SELECT pg_get_viewdef('rpr_pin_v'::regclass, true);
+SELECT pg_get_viewdef('rpr_pin_nav_v'::regclass, true);
+
+-- and the deparsed text builds an identical view
+CREATE VIEW rpr_pin_v2 AS
+ SELECT count(*) OVER w AS cnt
+ FROM rpr_pin,
+ rpr_pin_other rpr_pin_other(id, val_1)
+ WHERE rpr_pin.id = rpr_pin_other.id
+ WINDOW w AS (ORDER BY rpr_pin.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED
FOLLOWING
+ AFTER MATCH SKIP PAST LAST ROW
+ INITIAL
+ PATTERN (a+)
+ DEFINE
+ a AS val > 0);
+
+SELECT pg_get_viewdef('rpr_pin_v'::regclass, true)
+ = pg_get_viewdef('rpr_pin_v2'::regclass, true) AS identical;
+
+-- a column merged by USING is pinned the same way
+CREATE TABLE rpr_pin_l (x INT, y INT);
+CREATE TABLE rpr_pin_r (x INT, z INT);
+CREATE TABLE rpr_pin_x (id INT);
+
+CREATE VIEW rpr_pin_using_v AS
+SELECT count(*) OVER w AS cnt
+FROM rpr_pin_l JOIN rpr_pin_r USING (x), rpr_pin_x
+WINDOW w AS (ORDER BY rpr_pin_l.y
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+)
+ DEFINE A AS x > 0);
+
+ALTER TABLE rpr_pin_x ADD COLUMN x INT;
+
+SELECT pg_get_viewdef('rpr_pin_using_v'::regclass, true);
+
+-- a JOIN ... ON behaves the same, and the view keeps returning its rows
+CREATE TABLE rpr_pin_j1 (id INT, price INT);
+CREATE TABLE rpr_pin_j2 (id INT, qty INT);
+INSERT INTO rpr_pin_j1 VALUES (1, 10), (2, 20);
+INSERT INTO rpr_pin_j2 VALUES (1, 5), (2, 7);
+
+CREATE VIEW rpr_pin_on_v AS
+SELECT j1.id, count(*) OVER w AS cnt
+FROM rpr_pin_j1 j1 JOIN rpr_pin_j2 j2 ON j1.id = j2.id
+WINDOW w AS (ORDER BY j1.id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+)
+ DEFINE A AS price > 0);
+
+ALTER TABLE rpr_pin_j2 ADD COLUMN price INT;
+
+SELECT pg_get_viewdef('rpr_pin_on_v'::regclass, true);
+SELECT * FROM rpr_pin_on_v ORDER BY id;
+
+-- An aliased join hides its inputs, so the name that gets printed is the
join's
+-- own, taken from varnosyn, not the child column the Var carries in varno.
+-- Pinning the child instead would reserve a name that never reaches the output
+-- and leave the printed one free for a later column to collide with.
+CREATE TABLE rpr_pin_a (i INT, x INT);
+CREATE TABLE rpr_pin_b (k INT, y INT);
+CREATE TABLE rpr_pin_c (m INT);
+INSERT INTO rpr_pin_a VALUES (1, 10), (2, 20);
+INSERT INTO rpr_pin_b VALUES (1, 5), (2, 7);
+INSERT INTO rpr_pin_c VALUES (100), (200);
+
+CREATE VIEW rpr_pin_alias_v AS
+SELECT count(*) OVER w AS cnt
+FROM (rpr_pin_a JOIN rpr_pin_b ON rpr_pin_a.i = rpr_pin_b.k) j(p, q, r, s),
+ rpr_pin_c
+WINDOW w AS (ORDER BY rpr_pin_c.m
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A)
+ DEFINE A AS q > 0);
+
+ALTER TABLE rpr_pin_c ADD COLUMN q INT;
+
+SELECT pg_get_viewdef('rpr_pin_alias_v'::regclass, true);
+SELECT * FROM rpr_pin_alias_v;
+
+-- and the deparsed text builds a view that returns the same rows
+CREATE VIEW rpr_pin_alias_v2 AS
+SELECT count(*) OVER w AS cnt
+FROM (rpr_pin_a JOIN rpr_pin_b ON rpr_pin_a.i = rpr_pin_b.k) j(p, q, r, s),
+ rpr_pin_c rpr_pin_c(m, q_1)
+WINDOW w AS (ORDER BY rpr_pin_c.m
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP PAST LAST ROW
+ INITIAL
+ PATTERN (a)
+ DEFINE a AS q > 0);
+SELECT * FROM rpr_pin_alias_v2;
+DROP VIEW rpr_pin_alias_v2;
+
+-- Without a user column alias list the join still keeps the printed name, and
+-- the input relation is the one that moves aside.
+CREATE VIEW rpr_pin_alias_v3 AS
+SELECT count(*) OVER w AS cnt
+FROM (rpr_pin_a JOIN rpr_pin_b ON rpr_pin_a.i = rpr_pin_b.k) j, rpr_pin_c
+WINDOW w AS (ORDER BY rpr_pin_c.m
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A)
+ DEFINE A AS x > 0);
+SELECT pg_get_viewdef('rpr_pin_alias_v3'::regclass, true);
+
+DROP VIEW rpr_pin_alias_v3;
+DROP VIEW rpr_pin_alias_v;
+DROP TABLE rpr_pin_a, rpr_pin_b, rpr_pin_c;
+
+-- The same query written fresh is rejected, since nothing pins the name for
+-- it. Pinning is what lets the stored definition above still reparse.
+SELECT j1.id, count(*) OVER w AS cnt
+FROM rpr_pin_j1 j1 JOIN rpr_pin_j2 j2 ON j1.id = j2.id
+WINDOW w AS (ORDER BY j1.id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+)
+ DEFINE A AS price > 0);
+
-- Materialized view (if supported)
