Thread

Re: Row pattern recognition

Henson Choi <assam258@gmail.com> — 2026-05-12T07:01:05Z

Hi Tatsuo,


Four more incremental patches on top of the 2026-05-09 set
(0001-0011).  0012-0013 close the previously deferred B7
recursive-CTE item, on the strength of your ISO/IEC 9075-2:2016
7.17 citation; 0014-0015 apply and round out the README.rpr
enhancement you sent on 2026-05-12.


Patch summary:


> So I think at least SQL:2016 explicitly prohibits using RPR within
> recursive CTE. I would appreciate if anybody confirms this in
> SQL:2023.

  - 0012 Add rpr_integration B7 cases for RPR in recursive
         query
      Test-only prelude to 0013.  Replaces the prior B7 case
      (RPR in a recursive CTE base leg, asserting it works)
      with two cases that the upcoming prohibition needs to
      cover: WITH RECURSIVE with RPR in the base leg, and
      CREATE RECURSIVE VIEW with an RPR window.  Cites
      ISO/IEC 19075-5 6.17.5 (R020) and 4.18.5 (R010), and
      the formal rule in ISO/IEC 9075-2:2016 7.17 Syntax
      Rule 3)e)f) you pointed me to.  Drops the deferred
      XXX comment that left this open to community input.
      Expected output still matches pre-rejection behavior;
      0013 flips both queries to ERROR.


  - 0013 Reject row pattern recognition in recursive queries
      Per ISO/IEC 9075-2:2016 7.17 Syntax Rule 3)e)f), every
      <with list element> in a WITH RECURSIVE clause is
      "potentially recursive" and shall not contain a
      <row pattern common syntax>.  ISO/IEC 19075-5 6.17.5
      (R020) and 4.18.5 (R010) restate the prohibition for
      CREATE RECURSIVE VIEW, which makeRecursiveViewSelect()
      already rewrites to WITH RECURSIVE so the same path
      catches both forms.  The rejection runs in
      transformWithClause() against the raw parse tree,
      before per-CTE analysis, and reports the PATTERN keyword
      position via a new RPCommonSyntax.location field
      captured in gram.y.  Flips both B7 cases from result
      rows to the new error.


> Attached is the v47 patches for Row pattern recognition (SQL/RPR).
>
> > - Add src/backend/executor/README.rpr (previously was in ExecRPR.c)
>
> README.rpr is extremely useful for those who want to review the RPR
> patches.  I found a room to enhance the document.  Attached is a small
> patch tries to enhance README.rpr, on top of v47.
>
> - Make "target audience" and "scope" of the README more descriptive.
> - Add References (currently the SQL standards only)
> - Add explanation of some abbreviations (NFA, AST)
> - Add reference sections for absorption. Readers might not be familiar
> with "absorption"
> - Add more fields to WindowAggState
> - Add window framing rules with RPR
>

  - 0014 Enhance README.rpr per Tatsuo Ishii's review
      Doc-only.  Applies your off-list enhancement patch
      (2026-05-12) on top of v47 with no modification:
        * Make "target audience" and "scope" more
          descriptive, pointing readers to the SQL standard
          (with Oracle / Trino manuals as alternatives)
        * Spell out NFA and AST on first use
        * Cross-reference "IV-5. Absorbability Analysis" and
          "VIII-2. Solution: Context Absorption" from the
          RPR_ELEM_ABSORBABLE_BRANCH flag description
        * List nfaVisitedNWords, defineMatchStartDependent,
          and nfaLastProcessedRow in V-3
        * State the window framing rules that apply with RPR
        * Add a References section (SQL standards)


  - 0015 Round out README.rpr WindowAggState field coverage
      Doc-only follow-up to 0014.  Completes the V-3 field
      list (adds nfaVisitedMinWord / nfaVisitedMaxWord; notes
      that EXPLAIN ANALYZE instrumentation counters are
      intentionally omitted, with a pointer to execnodes.h)
      and mirrors the new fields in the Appendix B diagram,
      which still reflected the pre-review field list.


Previously deferred items:
  - B7 Recursive CTE XXX: addressed in 0012 (tests) + 0013
    (rejection) per the standards citations you supplied.


Best regards,
Henson