Re: Row pattern recognition

On Tue, Jul 28, 2026 at 10:48 AM Tatsuo Ishii <ishii(at)postgresql(dot)org> wrote:
>
> I have created v50 RPR patches from patches (nocfbot-000*.txt and
> nocfbot-100*.txt) attached to the following email:
> https://www.postgresql.org/message-id/CAAAe_zDF-jqo=m+pcuPB1h_sG1t+nY08acjBEcfD=1KoQN4JOQ@mail.gmail.com
>

> The series of patches are to implement the row pattern recognition
> (SQL/RPR) feature. Currently the implementation is a subset of SQL/RPR
> (ISO/IEC 19075-2:2016). Namely, implementation of some features of
> R020 (WINDOW clause). R010 (MATCH_RECOGNIZE) is out of the scope of
> the patches.
>

Hi.

I had an offline discussion with Henson about this.

CREATE TABLE t1 (id int, val int);
CREATE TABLE t2 (id int);
CREATE VIEW v1 AS
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 );

ALTER TABLE t2 add column val int;

The preceding ALTER TABLE ADD COLUMN succeeded,
After that, copying the view v1's SELECT definition and re-running the
SELECT statement will cause "ERROR: column reference "val" is
ambiguous".

To make the preceding ALTER TABLE ADD COLUMN fail, we must recursively
find all directly and indirectly associated views that contain an RPR
query,
and that view's RPR DEFINE clause already references this new column name.
Making this bulletproof doesn't seem easy. We also need to consider
ALTER TABLE RENAME COLUMN.

The implication of the above is that pg_dump | pg_restore will fail to
restore view v1.
This seems unsolvable; also see standard 6.5: Row pattern variables
and other range variables
So I added a warning in doc/src/sgml/ref/select.sgml.

Because
ERROR: cannot use CURRENT ROW as frame end with row pattern recognition

{ RANGE | ROWS | GROUPS } frame_start [ frame_exclusion ] [
row_pattern_common_syntax ]
is not supported. we can remove row_pattern_common_syntax from here.

The attached doc diff is based on
https://github.com/assam258-5892/postgres/commits/RPR

--
jian
https://www.enterprisedb.com/