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