Re: Row pattern recognition

Hi hackers,

This follows the ten-patch increment on top of v53 that I posted for
row pattern recognition (RPR, SQL:2016 R020, CF 4460) in the thread
"Row pattern recognition" [1]. Patch 0005 changes ruleutils.c. Most
of it is about RPR, but three of its changes look like upstream
patches that have nothing to do with RPR: they also alter
pg_get_viewdef() and pg_get_ruledef() output for queries without RPR.
I will submit them upstream as a separate patch. RPR may need further
changes on top of it, so I will send another patch that reconciles
0005 with the submitted one. This mail explains 0005, those three
included, for readers who know the deparser but not RPR.

What the deparser needs to know about RPR is small. A window clause
can say

WINDOW w AS (ORDER BY id
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
PATTERN (A+ B)
DEFINE A AS price > PREV(price), B AS price < PREV(price))

A DEFINE clause can name a column only without a qualifier, since the
qualifier slot belongs to the pattern variable. So get_rule_define()
prints it with varprefix off, and the bare name has to resolve exactly
as printed when a view or rule is re-parsed.

The patch is the nocfbot-0005 file attached to [1]. The same commit
is in https://github.com/assam258-5892/postgres/tree/RPR-20260930, on
top of v53.

The problems

Four things can break the trip from a view to its text and back. The
first needs no RPR. The other three concern the bare name a DEFINE
clause is printed with, which has to mean the same column when the
text is read back. I ran the examples below on v53 and on the patched
series. The COALESCE one was also run with 0004 applied and 0005 not.

- A column alias list written on a TABLEFUNC (JSON_TABLE, XMLTABLE)
is dropped. This is the simplest failure, and it reproduces on
released branches, with JSON_TABLE on 17 and later and with
XMLTABLE since 10:

CREATE VIEW v AS
SELECT p, q
FROM JSON_TABLE(jsonb '[1,2]', '$[*]'
COLUMNS (a int PATH '$', b int PATH '$')) AS jt(p, q);

v53 prints the select list as p, q and the JSON_TABLE as jt, with no
alias list, and re-parsing that text fails with "column "p" does not
exist". The reason is in the section below.

- A column added or renamed later. Once another column of the same
query level came to carry the name, the pg_get_viewdef() output
failed to re-parse, and the view could not be dumped and restored.
ALTER TABLE ... ADD COLUMN or RENAME COLUMN can do it after the
view is made. For example:

CREATE TABLE t1 (id int, price int);
CREATE TABLE t2 (id int);
CREATE VIEW v AS
SELECT t1.id, count(*) OVER w AS c
FROM t1 JOIN t2 ON t2.id = t1.id
WINDOW w AS (ORDER BY t1.id
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
PATTERN (A+) DEFINE A AS price > 10);
ALTER TABLE t2 ADD COLUMN price int;

v53 prints JOIN t2 ON t2.id = t1.id and the DEFINE clause as price >
10, and re-parsing that text fails with "column reference "price" is
ambiguous".

- A column merged by USING. The merged column keeps its natural
name, and that name can collide with one a DEFINE clause reads,
for example after a RENAME COLUMN. For example:

CREATE TABLE a (j int, p int);
CREATE TABLE b (j int, q int);
CREATE TABLE c (r int, s int);
CREATE VIEW v AS
SELECT count(*) OVER w AS cnt
FROM a JOIN b USING (j) CROSS JOIN c
WINDOW w AS (ORDER BY c.s
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
INITIAL PATTERN (X Y+) DEFINE X AS true, Y AS s > PREV(s));
ALTER TABLE c RENAME COLUMN s TO j;

v53 prints JOIN b USING (j) and the DEFINE clause as y AS j > PREV(j).
The merged j and the renamed c.j now collide, and re-parsing fails
with "column reference "j" is ambiguous".

- A column merged by FULL JOIN USING in a grouped query. In the
query below, the id in DEFINE is the merged column of l FULL JOIN
r USING (id). To match it with the GROUP BY item, the parser
expands that column into the expression that defines it,
COALESCE(l.id, r.id), so id + 1 becomes COALESCE(l.id, r.id) + 1
and is replaced by a reference to the grouping column. When the
view is deparsed, that reference is expanded back into the
grouping expression. A DEFINE clause prints Vars without a
qualifier, so both arms of the COALESCE come out as id. For
example:

CREATE TABLE l (id int PRIMARY KEY, val int);
CREATE TABLE r (id int, val int);
CREATE VIEW v AS
SELECT COALESCE(l.id, r.id) + 1 AS idp1, count(*) OVER w AS cnt
FROM l FULL JOIN r USING (id)
GROUP BY COALESCE(l.id, r.id) + 1
WINDOW w AS (ORDER BY COALESCE(l.id, r.id) + 1
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
PATTERN (A+) DEFINE A AS id + 1 > 0);

Deparsed with 0004 applied and 0005 not, the DEFINE clause comes out
as a AS (COALESCE(id, id) + 1) > 0, which loses which input each arm
came from, and re-parsing fails with "column "l.id" must appear in the
GROUP BY clause or be used in an aggregate function". With 0005 it is
printed as a AS (id + 1) > 0 and re-parses. (v53 itself rejects the
original query; 0004 is what accepts it.)

Why the TABLEFUNC exception matters

The deparser has never printed a column alias list for a TABLEFUNC RTE
(JSON_TABLE, XMLTABLE). The exception, printaliases = false, came
with XMLTABLE (fcec6caafa2, 2017), on the ground that the clause names
the columns itself, and JSON_TABLE reuses the RTE kind. The names are
still changed like for any other RTE: a user-written alias wins, and
USING pushes names down. The new names are used where the columns are
referenced, but the list that introduces them is not printed.

That stops being true once the user writes an alias list. Create a
view with one on the JSON_TABLE:

CREATE VIEW v AS
SELECT p, q FROM JSON_TABLE( ... ) AS jt(p, q);

pg_get_viewdef('v') on v53 returns

SELECT p, q
FROM JSON_TABLE( ... ) jt;

The list is gone, so the text uses p and q, which nothing introduces.
The point is that the names the deparser settles on are not printed,
neither the ones it picks to avoid a collision nor the user's own.

What the patch does

The problems have three causes, and the patch answers each. The
deparser chose column names without knowing which ones a DEFINE clause
reads, so the patch settles those names first and protects them. Some
RTEs could not print a rename at all, so the patch lets an RTE that
needs a rename print it as a column alias list. And in a grouped
query a merged column came back as the expression that defines it,
whose arms print alike without a qualifier, so the patch folds
expanded merge expressions back into the merged column. In more
detail:

- mark_define_columns() runs ahead of set_using_names(). It settles
the names of the columns a DEFINE clause reads before any other
column is named, and reserves them: those columns are exempt from
renaming, and no other column can take their names. This is what
keeps the added-column problem away.

- The reserved names are kept in using_names for the whole query
level, so no other column can take them. A colliding column is
renamed to name_N and its RTE prints a column alias list. In the
added-column example that is the t2(id, price_1) the patch prints:

JOIN t2 t2(id, price_1) ON t2.id = t1.id
...
DEFINE
a AS price > 10

In the USING example the merged column steps aside the same way, to
j_1 on both inputs, and the DEFINE clause keeps reading j:

FROM a a(j_1, p)
JOIN b b(j_1, q) USING (j_1)
CROSS JOIN c
...
y AS j > PREV(j)

- When a USING clause merges a column a DEFINE clause reads,
set_using_names() uses the name already settled instead of
inventing one, and gives it to both inputs.

- collapse_define_join_vars() folds the expanded merge expression
back into the merged column, so that it is printed as the user
wrote it. This is for the FULL JOIN problem.

- A TABLEFUNC RTE now prints a column alias list under the same rule
as other non-relation RTEs (a function RTE always prints one):
when the user wrote an alias list, or when one of its columns had
to be renamed to avoid a name collision. This reverses a decision
made with XMLTABLE, and I would like to hear from anyone who knows
whether there was a reason beyond the comment that the clause
names its columns.

A query without a DEFINE clause is not affected by the above, except
through the three changes in the next section. The reservation
itself adds no alias list unless a collision actually occurs.

Queries without RPR change too

Three changes to column naming are needed for the above to hold. They
also change pg_get_viewdef() and pg_get_ruledef() output for queries
without RPR. Each fixes output that failed to re-parse or re-parsed
to a different query.

- A column of a relation RTE outside the FROM clause (a rule's NEW
or OLD, or the target of an UPDATE or DELETE) is no longer
renamed. It has nowhere to print a column alias list, so a rename
printed a reference such as new.x_1 to a column that does not
exist. For example:

CREATE TABLE g (x int, y int);
CREATE TABLE h (x int, z int);
CREATE TABLE k (x int, w int);
CREATE TABLE lg (x int);
CREATE RULE r AS ON UPDATE TO g DO ALSO
INSERT INTO lg
SELECT g1.y FROM g g1, h FULL JOIN k USING (x)
WHERE g1.x <> new.x;

Without 0005, pg_get_ruledef() prints g g1(x_1, y) and WHERE (g1.x_1
<> new.x_1), and re-running that text fails with:

ERROR: column new.x_1 does not exist
LINE 6: WHERE (g1.x_1 <> new.x_1);
^
HINT: Perhaps you meant to reference the column "g1.x_1".

With 0005 it prints new.x.

- A TABLEFUNC RTE prints a column alias list, as explained above.

- For a function RTE with a single function and no WITH ORDINALITY
or column definition list, the column alias list now includes the
columns its composite result type has gained since the query was
parsed. Alias lists are positional, so leaving them out made the
list of an aliased join above apply to the wrong columns.

The tests add round trips for these cases. In create_view and rules,
0005 only adds test cases; no existing expected output there changes.
In rpr_base, the tests that recorded the unrestorable deparse of added
join columns are replaced by round trips of the collision cases.

These cover views and rules. A DEFINE clause in a SQL-standard
function body that reads a named parameter is not covered; a separate
mail reports it.

[1]
https://postgr.es/m/CAAAe_zDsYugq506ou49PU+Ok+4Umn5n59Qs5wYofvKyfEpvZJQ@mail.gmail.com

Best regards,
Henson