Re: Row pattern recognition

Hi Tatsuo,

I reviewed the planner's Window clause optimizations and found
additional issues with RPR windows.  Thanks to SungJun's Oracle
cross-validation work, all converted regression tests now pass
on both systems.  With these fixes, I'd like to move on to
non-functional quality testing next.

Here are eight incremental patches on top of v44.


nocfbot-0001: Fix elog message to use lowercase per PostgreSQL convention

  Minor style fix: lowercase the elog error message in
  nodeWindowAgg.c to follow PostgreSQL coding convention.


nocfbot-0002: Fix RPR reluctant quantifier flag lost during VIEW
serialization

  The reluctant flag on quantifiers was lost when a VIEW containing
  RPR was serialized and restored via ruleutils.c.  This patch fixes
  gram.y, rpr.c, ruleutils.c, and parsenodes.h so the flag survives
  the round-trip.


nocfbot-0003: Expand RPR test coverage and improve test comments

  Reorganizes test cases across rpr.sql, rpr_base.sql, rpr_explain.sql,
  and rpr_nfa.sql.  Moves base functionality tests from rpr.sql to
  rpr_base.sql and NFA-specific tests to rpr_nfa.sql.  Adds better
  section comments throughout.  Duplicate tests in rpr.sql were
  removed.  No reduction in test coverage.


nocfbot-0004: Keep RPR test objects for pg_upgrade/pg_dump testing

  Adjusts rpr_base.sql and rpr_explain.sql so that test tables and
  views are not dropped at the end of the test.  This allows
  pg_upgrade and pg_dump regression testing to cover RPR objects.


nocfbot-0005: Disable run condition pushdown for RPR windows

  Revised version of your run condition fix [1].  Existing test
  expected output updated accordingly.


nocfbot-0006: Disable frame optimization for RPR windows

  Prevents the planner from optimizing the window frame for RPR
  windows.  The frame clause in RPR has different semantics (it
  bounds the pattern search space), so standard frame optimizations
  must be disabled.  Adds EXPLAIN tests to verify.  Please review
  this one -- it's a newly discovered issue.


nocfbot-0007: Add stock scenario tests for RPR pattern matching

  Adds stock trading scenario tests using realistic synthetic data
  (stock.data with 1632 rows).  Tests V-shape recovery, W-shape,
  consecutive rises, and other common pattern matching use cases.


nocfbot-0008: Fix zero-min reluctant quantifier to produce zero-length match

  Fixes the bug reported by SungJun [2]: reluctant quantifiers with
  min=0 (A*?, A??) were incorrectly consuming at least one row instead
  of producing a zero-length match.  The NFA can now internally
  distinguish between a zero-length match and no match, so it will
  be ready when additional infrastructure (e.g. MATCH_NUMBER) is
  added.  Now matches Oracle behavior.


[1]
https://www.postgresql.org/message-id/20260304.153822.445473532741409674.ishii@postgresql.org
[2]
https://www.postgresql.org/message-id/CAE+cgNiUbKeH1A0PoxV2QjpsoxJLe+pJcGz_gdxwOwu_9zqchw@mail.gmail.com


Best regards,
Henson