Horton
Menu

Re: Row pattern recognition

Tatsuo Ishii <ishii@postgresql.org>

View thread
From: Tatsuo Ishii <ishii@postgresql.org>
To: assam258@gmail.com
Cc: jacob.champion@enterprisedb.com, david.g.johnston@gmail.com, vik@postgresfriends.org, er@xs4all.nl, peter@eisentraut.org, pgsql-hackers@postgresql.org
Date: 2026-02-02T07:20:00Z
Lists: pgsql-hackers

Attachments

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.

Attached are the v42 patches.
The difference from v41 include:

- Add Peter as a reviewer to commit message. He gave a good valuable
  suggestions to the gram.y patch. I accidentally forgot to credit his
  name.

- Tons of enhancements from Henson.

-- Fully rewrite absorption logic (optimizer/plan/rpr.c).
   Example: (a+ b* c?){2,} -> (a+" b* c?){2,}

-- Add safety check to tryMultiplyQuantifiers (optimizer/plan/rpr.c)
   Example: (A{2}){2,3} cannot be rewritten to A{4,6} because it allows
   5 times reputation which is not correct. Perform multiplication
   optimization only in safe case.

-- Fix infinite loop/segfault when unlimited quantifiers (e.g. (A*)*,
   (A+)+) are used.

-- Optimize unlimited quantifiers if possible (e.g. (A*)* -> A*, (A+)+ -> A+)

-- Fix segfault case (SELECT id, flag ...WINDOW w AS (... DEFINE T AS flag)

-- More accurate error position report

-- Some code refactoring in parser/planner patches

-- Split regression test file into rpr.sql and rpr_base.sql

-- Fix outfuncs.c and readfuncs.c

-- Fix some corner cases bug of regular expression optimization

-- Remove unused PATTERN variable to enhance performance

-- Change treatment of undefined DEFINE variables. Previously if
   variable "A" is used in PATTERN but not defined in DEFINE, A is
   automatically defined as "A AS TRUE". Now A is evaluated as TRUE in
   executor and the generated entry for A is removed. This improves
   memory usage.

-- EXPLAIN now shows the absorbable points (a+")

- Bug fix from me.

-- Assertion failure when two or more window clauses are used.
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Adjust cross-version upgrade tests for seg_out() fix

  2. Rationalize error comments in partition split/merge tests

  3. Add fast path for foreign key constraint checks

  4. Fix assorted pretty-trivial memory leaks in the backend.

  5. Add temporal FOREIGN KEY contraints

  6. Add trailing commas to enum definitions

  7. Remove obsolete executor cleanup code