Thread

Re: Row pattern recognition

Tatsuo Ishii <ishii@postgresql.org> — 2026-05-02T05:03:04Z

Attached is the v47 patches for Row pattern recognition (SQL/RPR).

Major changes from v46 include:

- Change implementation of row pattern navigation operations using
  "1-slot model", which allows to implement more standard compliant
  features such as an offset argument, more row pattern navigation
  operations (FIRST, LAST) and compound forms.

- Row pattern navigation operations now support FIRST, LAST and
  compound forms

- Add JIT compilation support for all row pattern navigation
  operations (including compound forms)

- Add tuplestore trim optimization for RPR PREV navigation

- Window function last_value() now allows to set mark in certain cases

- Change the implementation of reduced frame map. Now consumes less CPU and memory

- Add more optimization (absorption). e.g. (A B B)+

- Add planner integration tests (rpr_integration.sql)

- Add src/backend/executor/README.rpr (previously was in ExecRPR.c)

Current status:

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.

Currently following features are implemented in the patches.

- PATTERN
- PATTERN regular expressions (+, *, ?)
  alternation (|), grouping () , {n}, {n,}, {n,m}, {,m}
  reluctant quantifiers (*? etc.),
- DEFINE
- INITIAL
- AFTER MATCH SKIP TO PAST LAST ROW
- AFTER MATCH SKIP TO NEXT ROW
- Row pattern navigation (FIRST, LAST, PREV, NEXT and their compound forms)

Currently following features are not implemented in the patches.

- MEASURES
- Pattern variable name qualified column reference (e.g. A.price)
- SUBSET
- SEEK
- AFTER MATCH SKIP TO
- AFTER MATCH SKIP TO FIRST
- AFTER MATCH SKIP TO LAST
- PATTERN regular expression  {- and -}, () (empty pattern)
  Anchors (^, $) are not permitted with RPR in Window clause by the
  standard.
- PERMUTE
- CLASSIFIER

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