Re: Row pattern recognition

Hi Tatsuo,

> Done. Please see the attached patch.
>
> Looks good to me.
>

Thank you for reviewing.


> I changed
>    DEFINE A AS v > COALESCE(PREV(v), 0)
> to
>    DEFINE A AS v > PREV(v) OR PREV(v) IS NULL
>  and get following result.
>
> EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
> SELECT count(*) OVER w
> FROM generate_series(1, 50) AS s(v)
> WINDOW w AS (
>     ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
>     AFTER MATCH SKIP PAST LAST ROW
>     PATTERN (A{3,})
>     DEFINE A AS v > PREV(v) OR PREV(v) IS NULL
> );
>                               QUERY PLAN
> ----------------------------------------------------------------------
>  WindowAgg (actual rows=50.00 loops=1)
>    Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
>    Pattern: a{3,}"
>    Storage: Memory  Maximum Storage: 18kB
>    NFA States: 3 peak, 99 total, 0 merged
>    NFA Contexts: 2 peak, 51 total, 0 pruned
>    NFA: 1 matched (len 50/50/50.0), 0 mismatched
>    NFA: 49 absorbed (len 1/1/1.0), 0 skipped
>    ->  Function Scan on generate_series s (actual rows=50.00 loops=1)
> (9 rows)
>
> Probably we can restore 11.3 test in v43?
>


Yes, I've already restored Test 11.3 with the original pattern:

  DEFINE A AS v > PREV(v) OR PREV(v) IS NULL

Additionally, I've implemented cross-platform normalization for Storage
memory values
to address the platform differences (18kB vs 19kB, 23kB vs 24kB, etc.)
observed between
macOS and Rocky Linux 10.

The attached patch includes:

1. Test 11.3 restoration with original PREV(v) IS NULL pattern
2. Cross-platform Storage normalization using rpr_explain_filter() PL/pgSQL
function
   - Normalizes text format: "Maximum Storage: 18kB" -> "Maximum Storage:
NkB"
   - Normalizes JSON format: "Maximum Storage": 17 -> "Maximum Storage": 0
   - Normalizes XML format: <Maximum-Storage>17</Maximum-Storage> ->
<Maximum-Storage>0</Maximum-Storage>
   - Preserves NFA statistics unchanged (they are test assertions)
3. All EXPLAIN statements wrapped with rpr_explain_filter()
4. Removed rpr_explain_1.out (no longer needed)
5. Added rpr_explain to parallel_schedule test suite

Patch statistics:
 src/test/regress/expected/rpr_explain.out   |  559 +++---
 src/test/regress/expected/rpr_explain_1.out | 1803 -------------------
 src/test/regress/parallel_schedule          |    2 +-
 src/test/regress/sql/rpr_explain.sql        |  294 ++-
 4 files changed, 521 insertions(+), 2137 deletions(-)
 delete mode 100644 src/test/regress/expected/rpr_explain_1.out

Best regards,
Henson