Re: Row pattern recognition

Hi Henson and Jian,

I confirmed following issue is resolved with RPR53 + 10 incremental
patches (to be RPR54). Before, the last line of pg_get_viewdef was "a
AS ttt.val < 0);"

SELECT pg_get_viewdef('v1'::regclass, true);
pg_get_viewdef
-----------------------------------------------------------------
SELECT tt.id1, +
tt.val1, +
ttt.id, +
ttt.val, +
count(*) OVER w AS count +
FROM ( SELECT t1.id AS id1, +
t1.val AS val1 +
FROM t1) tt, +
t2 ttt +
WHERE tt.id1 = ttt.id +
WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING+
AFTER MATCH SKIP PAST LAST ROW +
INITIAL +
PATTERN (a) +
DEFINE +
a AS val < 0); <-- "val" is not qualified as expected
(1 row)

Regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

From: Tatsuo Ishii <ishii(at)postgresql(dot)org>
Subject: Re: Row pattern recognition
Date: Sat, 15 Aug 2026 08:10:54 +0900 (JST)
Message-ID: <20260815(dot)081054(dot)1707950877428677644(dot)ishii(at)postgresql(dot)org>

> Hi Henson,
>
>> Your case looks like the same shape. It currently deparses to
>>
>> SELECT
>> FROM t1,
>> t2
>> WHERE t1.id = t2.id
>> WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
>> AFTER MATCH SKIP PAST LAST ROW
>> INITIAL
>> PATTERN (a)
>> DEFINE
>> a AS val > 0);
>>
>> and that text does not reparse, exactly as you say
>> (ERROR: column reference "val" is ambiguous). But with one column
>> alias list on t2,
>>
>> t2 t2(id, val_1)
>>
>> the same text creates the view, a second deparse gives back the same
>> text, and DEFINE resolves to t1.val. So what the deparser is missing
>> here is a single token.
>
> I played with following SQL to check if the technique using subquery
> to resolve an ambiguity of column names in DEFINE clause explained in
> ISO/IEC 19075-5 section 6.5 "Row pattern variables and other range
> variables".
>
> First create an ambiguous column "val" in DEFINE clause.
>
> CREATE TABLE t1 (id int, val int);
> CREATE TABLE
> CREATE TABLE t2 (id int, val int);
> CREATE TABLE
> INSERT INTO t1 VALUES(1,1),(2,2);
> INSERT 0 2
> INSERT INTO t2 VALUES(1,-1),(2,-2);
> INSERT 0 2
> SELECT FROM t1, t2 WHERE t1.id = t2.id
> WINDOW w AS (
> ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
> PATTERN(A)
> DEFINE A AS val > 0
> );
> psql:ambiguous.sql:13: ERROR: column reference "val" is ambiguous
> LINE 5: DEFINE A AS val > 0
> ^
> Next, use the subquery workaround:
>
> SELECT tt.id1, tt.val1, ttt.id, ttt.val, count(*) OVER w
> FROM (SELECT id AS id1, val AS val1 FROM t1) AS tt, t2 AS ttt
> WHERE tt.id1 = ttt.id
> WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
> PATTERN(A)
> DEFINE A AS val < 0
> );
> id1 | val1 | id | val | count
> -----+------+----+-----+-------
> 1 | 1 | 1 | -1 | 1
> 2 | 2 | 2 | -2 | 1
> (2 rows)
>
> Seems work. Now, create a view from the query.
>
> CREATE VIEW v1 AS
> SELECT tt.id1, tt.val1, ttt.id, ttt.val, count(*) OVER w
> FROM (SELECT id AS id1, val AS val1 FROM t1) AS tt, t2 AS ttt
> WHERE tt.id1 = ttt.id
> WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
> PATTERN(A)
> DEFINE A AS val < 0
> );
> CREATE VIEW
> SELECT * FROM v1;
> id1 | val1 | id | val | count
> -----+------+----+-----+-------
> 1 | 1 | 1 | -1 | 1
> 2 | 2 | 2 | -2 | 1
> (2 rows)
>
> Again, it works. Let's check the view definition.
>
> SELECT pg_get_viewdef('v1'::regclass, true);
> pg_get_viewdef
> ------------------------------------------------------------------
> SELECT tt.id1, +
> tt.val1, +
> ttt.id, +
> ttt.val, +
> count(*) OVER w AS count +
> FROM ( SELECT t1.id AS id1, +
> t1.val AS val1 +
> FROM t1) tt, +
> t2 ttt +
> WHERE tt.id1 = ttt.id +
> WINDOW w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING +
> AFTER MATCH SKIP PAST LAST ROW +
> INITIAL +
> PATTERN (a) +
> DEFINE +
> a AS ttt.val < 0 );
> (1 row)
>
> Not good. The DEFINE clause uses a range variable declared in the FROM
> clause (ttt.val), which is not valid. Of course if we dump/restore
> this, restore will fail.
>
> So, even if we do not use ALTER TABLE ADD COLULN/RENAME COLUMN, we
> have a problem with views using RPR. I have not checked how hard to
> fix this yet. If it's hard, probably we should add this as a
> limitation of RPR to the document.
>
> Regards,
> --
> Tatsuo Ishii
> SRA OSS K.K.
> English: http://www.sraoss.co.jp/index_en/
> Japanese:http://www.sraoss.co.jp
>
>