From f9c80e37bbc493737ed04d237abdd4115d5a412e Mon Sep 17 00:00:00 2001 From: Henson Choi Date: Tue, 3 Mar 2026 19:32:24 +0900 Subject: [PATCH 4/8] Keep RPR test objects for pg_upgrade/pg_dump testing diff --git a/src/test/regress/expected/rpr_base.out b/src/test/regress/expected/rpr_base.out index 3383b242ef0..ae6d9c9b937 100644 --- a/src/test/regress/expected/rpr_base.out +++ b/src/test/regress/expected/rpr_base.out @@ -12,7 +12,7 @@ -- Quantifiers Tests -- Navigation Functions Tests -- SKIP TO / INITIAL Tests --- Serialization/Deserialization Tests +-- Serialization/Deserialization Tests (objects kept for pg_upgrade/pg_dump) -- Error Cases Tests -- -- Planner Layer: @@ -1930,7 +1930,6 @@ SELECT pg_get_viewdef('rpr_serial_v1'::regclass); a AS (val > 0) ); (1 row) -DROP VIEW rpr_serial_v1; -- Complex pattern with alternation CREATE VIEW rpr_serial_v2 AS SELECT id, val, COUNT(*) OVER w as cnt @@ -1967,7 +1966,6 @@ SELECT pg_get_viewdef('rpr_serial_v2'::regclass); b AS (val <= 20) ); (1 row) -DROP VIEW rpr_serial_v2; -- Pattern with grouping and quantifiers CREATE VIEW rpr_serial_v3 AS SELECT id, val, COUNT(*) OVER w as cnt @@ -2008,7 +2006,6 @@ SELECT pg_get_viewdef('rpr_serial_v3'::regclass); c AS (val <= 10) ); (1 row) -DROP VIEW rpr_serial_v3; -- All features combined CREATE VIEW rpr_serial_v4 AS SELECT id, val, COUNT(*) OVER w as cnt @@ -2053,7 +2050,6 @@ SELECT pg_get_viewdef('rpr_serial_v4'::regclass); finish AS (val > 15) ); (1 row) -DROP VIEW rpr_serial_v4; -- Additional quantifiers for deparsing coverage -- ? quantifier (zero or one) CREATE VIEW rpr_serial_v5 AS @@ -2091,7 +2087,6 @@ SELECT pg_get_viewdef('rpr_serial_v5'::regclass); b AS (val > 20) ); (1 row) -DROP VIEW rpr_serial_v5; -- {n,} quantifier (n or more) CREATE VIEW rpr_serial_v6 AS SELECT id, val, COUNT(*) OVER w as cnt @@ -2127,7 +2122,6 @@ SELECT pg_get_viewdef('rpr_serial_v6'::regclass); a AS (val > 15) ); (1 row) -DROP VIEW rpr_serial_v6; -- {n} quantifier (exactly n) CREATE VIEW rpr_serial_v7 AS SELECT id, val, COUNT(*) OVER w as cnt @@ -2163,7 +2157,6 @@ SELECT pg_get_viewdef('rpr_serial_v7'::regclass); a AS (val > 0) ); (1 row) -DROP VIEW rpr_serial_v7; -- Nested ALT pattern (tests deparse of complex nested structure) CREATE VIEW rpr_serial_v8 AS SELECT id, val, COUNT(*) OVER w as cnt @@ -2202,7 +2195,6 @@ SELECT pg_get_viewdef('rpr_serial_v8'::regclass); d AS (val > 30) ); (1 row) -DROP VIEW rpr_serial_v8; -- Reluctant {1}? quantifier deparse through ruleutils CREATE VIEW rpr_quant_reluctant_v AS SELECT id, val, count(*) OVER w @@ -2228,7 +2220,6 @@ SELECT pg_get_viewdef('rpr_quant_reluctant_v'::regclass); b AS (val > 0) ); (1 row) -DROP VIEW rpr_quant_reluctant_v; -- Materialized view (if supported) CREATE TABLE rpr_mview (id INT, val INT); INSERT INTO rpr_mview VALUES (1, 10), (2, 20), (3, 30); @@ -2274,8 +2265,6 @@ SELECT * FROM rpr_mview_v1 ORDER BY id; 3 | 30 | 0 (3 rows) -DROP MATERIALIZED VIEW rpr_mview_v1; -DROP TABLE rpr_mview; -- CREATE TABLE AS SELECT with RPR CREATE TABLE rpr_ctas (id INT, val INT); INSERT INTO rpr_ctas VALUES (1, 10), (2, 20), (3, 15), (4, 25); @@ -2677,8 +2666,6 @@ SELECT pg_get_viewdef('rpr_multiwin_v'::regclass); b AS (val <= 15) ); (1 row) -DROP VIEW rpr_multiwin_v; -DROP TABLE rpr_multiwin; -- {n} quantifier display in view CREATE VIEW rpr_quant_n_v AS SELECT id, val, count(*) OVER w @@ -2703,7 +2690,6 @@ SELECT pg_get_viewdef('rpr_quant_n_v'::regclass); a AS (val > 0) ); (1 row) -DROP VIEW rpr_quant_n_v; -- {n,} quantifier display in view CREATE VIEW rpr_quant_n_plus_v AS SELECT id, val, count(*) OVER w @@ -2728,8 +2714,6 @@ SELECT pg_get_viewdef('rpr_quant_n_plus_v'::regclass); a AS (val > 0) ); (1 row) -DROP VIEW rpr_quant_n_plus_v; -DROP TABLE rpr_serial; -- ============================================================ -- Error Cases Tests -- ============================================================ diff --git a/src/test/regress/expected/rpr_explain.out b/src/test/regress/expected/rpr_explain.out index 817269021f4..3c70a12874a 100644 --- a/src/test/regress/expected/rpr_explain.out +++ b/src/test/regress/expected/rpr_explain.out @@ -3,6 +3,9 @@ -- Tests for Row Pattern Recognition EXPLAIN output -- ============================================================ -- +-- Views and tables in this file are intentionally not dropped, +-- so that pg_upgrade/pg_dump can test RPR syntax serialization. +-- -- This test suite validates EXPLAIN output for RPR queries, -- including NFA statistics shown in EXPLAIN ANALYZE: -- - NFA States: peak, total, merged @@ -74,13 +77,13 @@ begin end; $$; -- Setup: Create test tables -CREATE TEMP TABLE nfa_test ( +CREATE TABLE rpr_nfa_test ( id serial, v int, cat char(1) ); -- Insert test data: 100 rows with predictable pattern -INSERT INTO nfa_test (v, cat) +INSERT INTO rpr_nfa_test (v, cat) SELECT i, CASE WHEN i % 5 = 1 THEN 'A' @@ -91,12 +94,12 @@ SELECT i, END FROM generate_series(1, 100) i; -- Additional test table with more complex patterns -CREATE TEMP TABLE nfa_complex ( +CREATE TABLE rpr_nfa_complex ( id serial, price int, trend char(1) -- U=up, D=down, S=stable ); -INSERT INTO nfa_complex (price, trend) +INSERT INTO rpr_nfa_complex (price, trend) VALUES (100, 'S'), (105, 'U'), (110, 'U'), (108, 'D'), (112, 'U'), (115, 'U'), (113, 'D'), (111, 'D'), (109, 'D'), (110, 'U'), @@ -108,16 +111,16 @@ VALUES -- Basic NFA Statistics Tests -- ============================================================ -- Simple pattern - should show basic statistics -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev01 AS SELECT count(*) OVER w -FROM nfa_test +FROM rpr_nfa_test WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING AFTER MATCH SKIP PAST LAST ROW PATTERN (A B) DEFINE A AS cat = 'A', B AS cat = 'B' ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev01'), E'\n')) AS line WHERE line ~ 'PATTERN'; line ------------------ PATTERN (a b) @@ -126,7 +129,7 @@ SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w -FROM nfa_test +FROM rpr_nfa_test WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING AFTER MATCH SKIP PAST LAST ROW @@ -143,21 +146,20 @@ WINDOW w AS ( NFA Contexts: 2 peak, 101 total, 60 pruned NFA: 20 matched (len 2/2/2.0), 0 mismatched NFA: 0 absorbed, 20 skipped (len 1/1/1.0) - -> Seq Scan on nfa_test (actual rows=100.00 loops=1) + -> Seq Scan on rpr_nfa_test (actual rows=100.00 loops=1) (9 rows) -DROP VIEW rpr_v; -- Pattern with no matches - 0 matched -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev02 AS SELECT count(*) OVER w -FROM nfa_test +FROM rpr_nfa_test WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING AFTER MATCH SKIP PAST LAST ROW PATTERN (X Y Z) DEFINE X AS cat = 'X', Y AS cat = 'Y', Z AS cat = 'Z' ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev02'), E'\n')) AS line WHERE line ~ 'PATTERN'; line -------------------- PATTERN (x y z) @@ -166,7 +168,7 @@ SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w -FROM nfa_test +FROM rpr_nfa_test WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING AFTER MATCH SKIP PAST LAST ROW @@ -182,21 +184,20 @@ WINDOW w AS ( NFA States: 1 peak, 101 total, 0 merged NFA Contexts: 2 peak, 101 total, 100 pruned NFA: 0 matched, 0 mismatched - -> Seq Scan on nfa_test (actual rows=100.00 loops=1) + -> Seq Scan on rpr_nfa_test (actual rows=100.00 loops=1) (8 rows) -DROP VIEW rpr_v; -- Pattern matching every row - high match count -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev03 AS SELECT count(*) OVER w -FROM nfa_test +FROM rpr_nfa_test WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING AFTER MATCH SKIP PAST LAST ROW PATTERN (R) DEFINE R AS TRUE ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev03'), E'\n')) AS line WHERE line ~ 'PATTERN'; line ---------------- PATTERN (r) @@ -205,7 +206,7 @@ SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w -FROM nfa_test +FROM rpr_nfa_test WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING AFTER MATCH SKIP PAST LAST ROW @@ -221,13 +222,12 @@ WINDOW w AS ( NFA States: 2 peak, 101 total, 0 merged NFA Contexts: 2 peak, 101 total, 0 pruned NFA: 100 matched (len 1/1/1.0), 0 mismatched - -> Seq Scan on nfa_test (actual rows=100.00 loops=1) + -> Seq Scan on rpr_nfa_test (actual rows=100.00 loops=1) (8 rows) -DROP VIEW rpr_v; -- Regression test: Space before parenthesis in pattern deparse -- Verifies that "A (B | C)" correctly outputs as "a (b | c)" with space -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev04 AS SELECT count(*) OVER w FROM generate_series(1, 20) AS s(v) WINDOW w AS ( @@ -235,7 +235,7 @@ WINDOW w AS ( PATTERN (A (B | C)) DEFINE A AS v % 3 = 1, B AS v % 3 = 2, C AS v % 3 = 0 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev04'), E'\n')) AS line WHERE line ~ 'PATTERN'; line ------------------------ PATTERN (a (b | c)) @@ -263,11 +263,10 @@ WINDOW w AS ( -> Function Scan on generate_series s (actual rows=20.00 loops=1) (9 rows) -DROP VIEW rpr_v; -- Regression test: Sequential alternations at same depth -- Verifies that "((B | C) (D | E))" correctly outputs as "(b | c) (d | e)" -- Previously failed due to missing parentheses on ALT depth decrease -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev05 AS SELECT count(*) OVER w FROM generate_series(1, 30) AS s(v) WINDOW w AS ( @@ -275,7 +274,7 @@ WINDOW w AS ( PATTERN (A ((B | C) (D | E))*) DEFINE A AS v % 5 = 1, B AS v % 5 = 2, C AS v % 5 = 3, D AS v % 5 = 4, E AS v % 5 = 0 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev05'), E'\n')) AS line WHERE line ~ 'PATTERN'; line ----------------------------------- PATTERN (a ((b | c) (d | e))*) @@ -302,12 +301,11 @@ WINDOW w AS ( -> Function Scan on generate_series s (actual rows=30.00 loops=1) (8 rows) -DROP VIEW rpr_v; -- ============================================================ -- State Statistics Tests (peak, total, merged) -- ============================================================ -- Simple quantifier pattern - A+ with short matches (no merging) -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev06 AS SELECT count(*) OVER w FROM generate_series(1, 50) AS s(v) WINDOW w AS ( @@ -316,7 +314,7 @@ WINDOW w AS ( PATTERN (A+) DEFINE A AS v % 2 = 1 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev06'), E'\n')) AS line WHERE line ~ 'PATTERN'; line ----------------- PATTERN (a+) @@ -344,11 +342,10 @@ WINDOW w AS ( -> Function Scan on generate_series s (actual rows=50.00 loops=1) (8 rows) -DROP VIEW rpr_v; -- Alternation pattern - multiple state branches -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev07 AS SELECT count(*) OVER w -FROM nfa_test +FROM rpr_nfa_test WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING AFTER MATCH SKIP PAST LAST ROW @@ -357,7 +354,7 @@ WINDOW w AS ( A AS cat = 'A', B AS cat = 'B', C AS cat = 'C', D AS cat = 'D', E AS cat = 'E' ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev07'), E'\n')) AS line WHERE line ~ 'PATTERN'; line ---------------------------------- PATTERN ((a | b | c) (d | e)) @@ -366,7 +363,7 @@ SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w -FROM nfa_test +FROM rpr_nfa_test WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING AFTER MATCH SKIP PAST LAST ROW @@ -385,12 +382,11 @@ WINDOW w AS ( NFA Contexts: 3 peak, 101 total, 20 pruned NFA: 20 matched (len 2/2/2.0), 40 mismatched (len 2/2/2.0) NFA: 0 absorbed, 20 skipped (len 1/1/1.0) - -> Seq Scan on nfa_test (actual rows=100.00 loops=1) + -> Seq Scan on rpr_nfa_test (actual rows=100.00 loops=1) (9 rows) -DROP VIEW rpr_v; -- Complex pattern with high state count -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev08 AS SELECT count(*) OVER w FROM generate_series(1, 100) AS s(v) WINDOW w AS ( @@ -402,7 +398,7 @@ WINDOW w AS ( B AS v % 3 = 2, C AS v % 3 = 0 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev08'), E'\n')) AS line WHERE line ~ 'PATTERN'; line ----------------------- PATTERN (a+ b* c+) @@ -434,9 +430,8 @@ WINDOW w AS ( -> Function Scan on generate_series s (actual rows=100.00 loops=1) (9 rows) -DROP VIEW rpr_v; -- Grouped pattern with quantifier - state count with grouping -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev09 AS SELECT count(*) OVER w FROM generate_series(1, 60) AS s(v) WINDOW w AS ( @@ -445,7 +440,7 @@ WINDOW w AS ( PATTERN ((A B)+) DEFINE A AS v % 2 = 1, B AS v % 2 = 0 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev09'), E'\n')) AS line WHERE line ~ 'PATTERN'; line --------------------- PATTERN ((a b)+) @@ -474,10 +469,9 @@ WINDOW w AS ( -> Function Scan on generate_series s (actual rows=60.00 loops=1) (9 rows) -DROP VIEW rpr_v; -- State explosion pattern - many alternations -- Pattern (A|B)(A|B)(A|B)(A|B) can create many parallel states -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev10 AS SELECT count(*) OVER w FROM generate_series(1, 100) AS s(v) WINDOW w AS ( @@ -486,7 +480,7 @@ WINDOW w AS ( PATTERN ((A | B) (A | B) (A | B) (A | B) (A | B) (A | B) (A | B) (A | B)) DEFINE A AS v % 2 = 1, B AS v % 2 = 0 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev10'), E'\n')) AS line WHERE line ~ 'PATTERN'; line ------------------------------------------------------------------------------ PATTERN ((a | b) (a | b) (a | b) (a | b) (a | b) (a | b) (a | b) (a | b)) @@ -515,10 +509,9 @@ WINDOW w AS ( -> Function Scan on generate_series s (actual rows=100.00 loops=1) (9 rows) -DROP VIEW rpr_v; -- Consecutive ALT merge followed by different ALT -- Tests mergeConsecutiveAlts flush on ALT change: (A|B){2} (C|D) -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev11 AS SELECT count(*) OVER w FROM generate_series(1, 40) AS s(v) WINDOW w AS ( @@ -527,7 +520,7 @@ WINDOW w AS ( PATTERN ((A | B) (A | B) (C | D)) DEFINE A AS v % 4 = 0, B AS v % 4 = 1, C AS v % 4 = 2, D AS v % 4 = 3 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev11'), E'\n')) AS line WHERE line ~ 'PATTERN'; line -------------------------------------- PATTERN ((a | b) (a | b) (c | d)) @@ -556,10 +549,9 @@ WINDOW w AS ( -> Function Scan on generate_series s (actual rows=40.00 loops=1) (9 rows) -DROP VIEW rpr_v; -- Consecutive ALT merge followed by non-ALT element -- Tests mergeConsecutiveAlts flush on non-ALT: (A|B){2} c -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev12 AS SELECT count(*) OVER w FROM generate_series(1, 40) AS s(v) WINDOW w AS ( @@ -568,7 +560,7 @@ WINDOW w AS ( PATTERN ((A | B) (A | B) C) DEFINE A AS v % 3 = 0, B AS v % 3 = 1, C AS v % 3 = 2 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev12'), E'\n')) AS line WHERE line ~ 'PATTERN'; line -------------------------------- PATTERN ((a | b) (a | b) c) @@ -597,9 +589,8 @@ WINDOW w AS ( -> Function Scan on generate_series s (actual rows=40.00 loops=1) (9 rows) -DROP VIEW rpr_v; -- ALT prefix/suffix absorbed into GROUP: (A|B) (A|B)+ (A|B) -> (A|B){3,} -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev13 AS SELECT count(*) OVER w FROM generate_series(1, 40) AS s(v) WINDOW w AS ( @@ -608,7 +599,7 @@ WINDOW w AS ( PATTERN ((A | B) (A | B)+ (A | B)) DEFINE A AS v % 2 = 0, B AS v % 2 = 1 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev13'), E'\n')) AS line WHERE line ~ 'PATTERN'; line --------------------------------------- PATTERN ((a | b) (a | b)+ (a | b)) @@ -637,9 +628,8 @@ WINDOW w AS ( -> Function Scan on generate_series s (actual rows=40.00 loops=1) (9 rows) -DROP VIEW rpr_v; -- High state count - alternation with plus quantifier -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev14 AS SELECT count(*) OVER w FROM generate_series(1, 100) AS s(v) WINDOW w AS ( @@ -648,7 +638,7 @@ WINDOW w AS ( PATTERN ((A | B | C)+ D) DEFINE A AS v % 4 = 1, B AS v % 4 = 2, C AS v % 4 = 3, D AS v % 4 = 0 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev14'), E'\n')) AS line WHERE line ~ 'PATTERN'; line ----------------------------- PATTERN ((a | b | c)+ d) @@ -677,10 +667,9 @@ WINDOW w AS ( -> Function Scan on generate_series s (actual rows=100.00 loops=1) (9 rows) -DROP VIEW rpr_v; -- Early termination: first ALT branch (A) reaches FIN immediately, -- pruning second branch (A B+) before it can accumulate B repetitions. -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev15 AS SELECT count(*) OVER w FROM generate_series(1, 100) AS s(v) WINDOW w AS ( @@ -689,7 +678,7 @@ WINDOW w AS ( PATTERN ((A | A B)+) DEFINE A AS v = 1, B AS v > 1 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev15'), E'\n')) AS line WHERE line ~ 'PATTERN'; line ------------------------- PATTERN ((a | a b)+) @@ -717,9 +706,8 @@ WINDOW w AS ( -> Function Scan on generate_series s (actual rows=100.00 loops=1) (8 rows) -DROP VIEW rpr_v; -- Nested quantifiers causing state growth -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev16 AS SELECT count(*) OVER w FROM generate_series(1, 1000) AS s(v) WINDOW w AS ( @@ -728,7 +716,7 @@ WINDOW w AS ( PATTERN (((A | B)+)+) DEFINE A AS v % 3 = 1, B AS v % 3 = 2 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev16'), E'\n')) AS line WHERE line ~ 'PATTERN'; line -------------------------- PATTERN (((a | b)+)+) @@ -757,12 +745,11 @@ WINDOW w AS ( -> Function Scan on generate_series s (actual rows=1000.00 loops=1) (9 rows) -DROP VIEW rpr_v; -- ============================================================ -- Context Statistics Tests (peak, total, pruned + absorbed/skipped) -- ============================================================ -- Context absorption with unbounded quantifier at start -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev17 AS SELECT count(*) OVER w FROM generate_series(1, 50) AS s(v) WINDOW w AS ( @@ -771,7 +758,7 @@ WINDOW w AS ( PATTERN (A+ B) DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev17'), E'\n')) AS line WHERE line ~ 'PATTERN'; line ------------------- PATTERN (a+ b) @@ -800,9 +787,8 @@ WINDOW w AS ( -> Function Scan on generate_series s (actual rows=50.00 loops=1) (9 rows) -DROP VIEW rpr_v; -- No absorption - bounded quantifier -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev18 AS SELECT count(*) OVER w FROM generate_series(1, 50) AS s(v) WINDOW w AS ( @@ -811,7 +797,7 @@ WINDOW w AS ( PATTERN (A{2,4} B) DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev18'), E'\n')) AS line WHERE line ~ 'PATTERN'; line ----------------------- PATTERN (a{2,4} b) @@ -840,9 +826,8 @@ WINDOW w AS ( -> Function Scan on generate_series s (actual rows=50.00 loops=1) (9 rows) -DROP VIEW rpr_v; -- Contexts skipped by SKIP PAST LAST ROW -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev19 AS SELECT count(*) OVER w FROM generate_series(1, 100) AS s(v) WINDOW w AS ( @@ -851,7 +836,7 @@ WINDOW w AS ( PATTERN (A B C) DEFINE A AS v % 10 = 1, B AS v % 10 = 2, C AS v % 10 = 3 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev19'), E'\n')) AS line WHERE line ~ 'PATTERN'; line -------------------- PATTERN (a b c) @@ -880,9 +865,8 @@ WINDOW w AS ( -> Function Scan on generate_series s (actual rows=100.00 loops=1) (9 rows) -DROP VIEW rpr_v; -- High context absorption - unbounded group -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev20 AS SELECT count(*) OVER w FROM generate_series(1, 100) AS s(v) WINDOW w AS ( @@ -891,7 +875,7 @@ WINDOW w AS ( PATTERN ((A B)+ C) DEFINE A AS v % 3 = 1, B AS v % 3 = 2, C AS v % 3 = 0 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev20'), E'\n')) AS line WHERE line ~ 'PATTERN'; line ----------------------- PATTERN ((a b)+ c) @@ -920,14 +904,13 @@ WINDOW w AS ( -> Function Scan on generate_series s (actual rows=100.00 loops=1) (9 rows) -DROP VIEW rpr_v; -- ============================================================ -- Match Length Statistics Tests -- ============================================================ -- Fixed length matches - all same length -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev21 AS SELECT count(*) OVER w -FROM nfa_test +FROM rpr_nfa_test WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING AFTER MATCH SKIP PAST LAST ROW @@ -936,7 +919,7 @@ WINDOW w AS ( A AS cat = 'A', B AS cat = 'B', C AS cat = 'C', D AS cat = 'D', E AS cat = 'E' ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev21'), E'\n')) AS line WHERE line ~ 'PATTERN'; line ------------------------ PATTERN (a b c d e) @@ -945,7 +928,7 @@ SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w -FROM nfa_test +FROM rpr_nfa_test WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING AFTER MATCH SKIP PAST LAST ROW @@ -964,12 +947,11 @@ WINDOW w AS ( NFA Contexts: 3 peak, 101 total, 60 pruned NFA: 20 matched (len 5/5/5.0), 0 mismatched NFA: 0 absorbed, 20 skipped (len 1/1/1.0) - -> Seq Scan on nfa_test (actual rows=100.00 loops=1) + -> Seq Scan on rpr_nfa_test (actual rows=100.00 loops=1) (9 rows) -DROP VIEW rpr_v; -- Variable length matches - min/max/avg differ -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev22 AS SELECT count(*) OVER w FROM generate_series(1, 100) AS s(v) WINDOW w AS ( @@ -978,7 +960,7 @@ WINDOW w AS ( PATTERN (A+ B) DEFINE A AS v % 10 <> 0, B AS v % 10 = 0 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev22'), E'\n')) AS line WHERE line ~ 'PATTERN'; line ------------------- PATTERN (a+ b) @@ -1007,9 +989,8 @@ WINDOW w AS ( -> Function Scan on generate_series s (actual rows=100.00 loops=1) (9 rows) -DROP VIEW rpr_v; -- Very long matches -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev23 AS SELECT count(*) OVER w FROM generate_series(1, 200) AS s(v) WINDOW w AS ( @@ -1018,7 +999,7 @@ WINDOW w AS ( PATTERN (A+ B) DEFINE A AS v <= 195, B AS v > 195 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev23'), E'\n')) AS line WHERE line ~ 'PATTERN'; line ------------------- PATTERN (a+ b) @@ -1047,9 +1028,8 @@ WINDOW w AS ( -> Function Scan on generate_series s (actual rows=200.00 loops=1) (9 rows) -DROP VIEW rpr_v; -- Uniform match length with mismatches from gap rows (v%20 = 11..15) -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev24 AS SELECT count(*) OVER w FROM generate_series(1, 100) AS s(v) WINDOW w AS ( @@ -1060,7 +1040,7 @@ WINDOW w AS ( A AS (v % 20 <> 0) AND (v % 20 <= 10 OR v % 20 > 15), B AS v % 20 = 0 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev24'), E'\n')) AS line WHERE line ~ 'PATTERN'; line ------------------- PATTERN (a+ b) @@ -1091,13 +1071,12 @@ WINDOW w AS ( -> Function Scan on generate_series s (actual rows=100.00 loops=1) (9 rows) -DROP VIEW rpr_v; -- ============================================================ -- Mismatch Length Statistics Tests -- ============================================================ -- Pattern with complete match every cycle: 0 mismatched -- A(1,2,3) B(4,5) C(6) repeats perfectly; X rows are pruned, not mismatched -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev25 AS SELECT count(*) OVER w FROM ( SELECT v, @@ -1113,7 +1092,7 @@ WINDOW w AS ( PATTERN (A+ B+ C) DEFINE A AS cat = 'A', B AS cat = 'B', C AS cat = 'C' ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev25'), E'\n')) AS line WHERE line ~ 'PATTERN'; line ---------------------- PATTERN (a+ b+ c) @@ -1149,9 +1128,8 @@ WINDOW w AS ( -> Function Scan on generate_series s (actual rows=100.00 loops=1) (9 rows) -DROP VIEW rpr_v; -- Long partial matches that fail -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev26 AS SELECT count(*) OVER w FROM ( SELECT i AS v, @@ -1172,7 +1150,7 @@ WINDOW w AS ( PATTERN (A+ B+ C) DEFINE A AS cat = 'A', B AS cat = 'B', C AS cat = 'C' ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev26'), E'\n')) AS line WHERE line ~ 'PATTERN'; line ---------------------- PATTERN (a+ b+ c) @@ -1213,12 +1191,11 @@ WINDOW w AS ( -> Function Scan on generate_series i (actual rows=60.00 loops=1) (9 rows) -DROP VIEW rpr_v; -- ============================================================ -- JSON Format Tests -- ============================================================ -- JSON format output with all statistics -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev27 AS SELECT count(*) OVER w FROM generate_series(1, 50) AS s(v) WINDOW w AS ( @@ -1227,7 +1204,7 @@ WINDOW w AS ( PATTERN (A+ B+) DEFINE A AS v % 3 = 1, B AS v % 3 = 2 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev27'), E'\n')) AS line WHERE line ~ 'PATTERN'; line -------------------- PATTERN (a+ b+) @@ -1294,9 +1271,8 @@ WINDOW w AS ( ] (1 row) -DROP VIEW rpr_v; -- JSON format with match length statistics -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev28 AS SELECT count(*) OVER w FROM generate_series(1, 100) AS s(v) WINDOW w AS ( @@ -1305,7 +1281,7 @@ WINDOW w AS ( PATTERN (A+ B) DEFINE A AS v % 10 <> 0, B AS v % 10 = 0 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev28'), E'\n')) AS line WHERE line ~ 'PATTERN'; line ------------------- PATTERN (a+ b) @@ -1375,10 +1351,9 @@ WINDOW w AS ( ] (1 row) -DROP VIEW rpr_v; -- JSON format with mismatch statistics -- Pattern A B C expects 1,2,3 but gets 1,2,4 twice causing mismatches -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev29 AS SELECT count(*) OVER w FROM (VALUES (1),(2),(4), (1),(2),(4), (1),(2),(3)) AS t(v) WINDOW w AS ( @@ -1387,7 +1362,7 @@ WINDOW w AS ( PATTERN (A B C) DEFINE A AS v = 1, B AS v = 2, C AS v = 3 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev29'), E'\n')) AS line WHERE line ~ 'PATTERN'; line -------------------- PATTERN (a b c) @@ -1456,10 +1431,9 @@ WINDOW w AS ( ] (1 row) -DROP VIEW rpr_v; -- JSON format with skipped context statistics -- Alternation pattern with SKIP PAST LAST ROW causes many contexts to be skipped -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev30 AS SELECT count(*) OVER w FROM generate_series(1, 100) AS s(v) WINDOW w AS ( @@ -1468,7 +1442,7 @@ WINDOW w AS ( PATTERN ((A | B) (A | B) (A | B) (A | B) (A | B) (A | B) (A | B) (A | B)) DEFINE A AS v % 2 = 1, B AS v % 2 = 0 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev30'), E'\n')) AS line WHERE line ~ 'PATTERN'; line ------------------------------------------------------------------------------ PATTERN ((a | b) (a | b) (a | b) (a | b) (a | b) (a | b) (a | b) (a | b)) @@ -1538,12 +1512,11 @@ WINDOW w AS ( ] (1 row) -DROP VIEW rpr_v; -- ============================================================ -- XML Format Tests -- ============================================================ -- XML format output -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev31 AS SELECT count(*) OVER w FROM generate_series(1, 30) AS s(v) WINDOW w AS ( @@ -1552,7 +1525,7 @@ WINDOW w AS ( PATTERN (A B) DEFINE A AS v % 2 = 1, B AS v % 2 = 0 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev31'), E'\n')) AS line WHERE line ~ 'PATTERN'; line ------------------ PATTERN (a b) @@ -1619,12 +1592,11 @@ WINDOW w AS ( (1 row) -DROP VIEW rpr_v; -- ============================================================ -- Multiple Partitions Tests -- ============================================================ -- Statistics across multiple partitions -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev32 AS SELECT count(*) OVER w FROM ( SELECT p, v @@ -1638,7 +1610,7 @@ WINDOW w AS ( PATTERN (A+ B) DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev32'), E'\n')) AS line WHERE line ~ 'PATTERN'; line ------------------- PATTERN (a+ b) @@ -1677,9 +1649,8 @@ WINDOW w AS ( -> Function Scan on generate_series v (actual rows=30.00 loops=3) (14 rows) -DROP VIEW rpr_v; -- Different pattern behavior per partition -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev33 AS SELECT count(*) OVER w FROM ( SELECT @@ -1694,7 +1665,7 @@ WINDOW w AS ( PATTERN (A+ B) DEFINE A AS val < 5, B AS val >= 5 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev33'), E'\n')) AS line WHERE line ~ 'PATTERN'; line ------------------- PATTERN (a+ b) @@ -1732,12 +1703,11 @@ WINDOW w AS ( -> Function Scan on generate_series v (actual rows=50.00 loops=1) (12 rows) -DROP VIEW rpr_v; -- ============================================================ -- Edge Cases -- ============================================================ -- Empty result set -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev34 AS SELECT count(*) OVER w FROM generate_series(1, 0) AS s(v) WINDOW w AS ( @@ -1746,7 +1716,7 @@ WINDOW w AS ( PATTERN (A B) DEFINE A AS v = 1, B AS v = 2 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev34'), E'\n')) AS line WHERE line ~ 'PATTERN'; line ------------------ PATTERN (a b) @@ -1770,9 +1740,8 @@ WINDOW w AS ( -> Function Scan on generate_series s (actual rows=0.00 loops=1) (4 rows) -DROP VIEW rpr_v; -- Single row -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev35 AS SELECT count(*) OVER w FROM generate_series(1, 1) AS s(v) WINDOW w AS ( @@ -1781,7 +1750,7 @@ WINDOW w AS ( PATTERN (A) DEFINE A AS TRUE ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev35'), E'\n')) AS line WHERE line ~ 'PATTERN'; line ---------------- PATTERN (a) @@ -1809,9 +1778,8 @@ WINDOW w AS ( -> Function Scan on generate_series s (actual rows=1.00 loops=1) (8 rows) -DROP VIEW rpr_v; -- Pattern longer than data -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev36 AS SELECT count(*) OVER w FROM generate_series(1, 5) AS s(v) WINDOW w AS ( @@ -1822,7 +1790,7 @@ WINDOW w AS ( A AS v = 1, B AS v = 2, C AS v = 3, D AS v = 4, E AS v = 5, F AS v = 6, G AS v = 7, H AS v = 8, I AS v = 9, J AS v = 10 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev36'), E'\n')) AS line WHERE line ~ 'PATTERN'; line ---------------------------------- PATTERN (a b c d e f g h i j) @@ -1852,9 +1820,8 @@ WINDOW w AS ( -> Function Scan on generate_series s (actual rows=5.00 loops=1) (8 rows) -DROP VIEW rpr_v; -- All rows match as single match -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev37 AS SELECT count(*) OVER w FROM generate_series(1, 50) AS s(v) WINDOW w AS ( @@ -1863,7 +1830,7 @@ WINDOW w AS ( PATTERN (A+) DEFINE A AS TRUE ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev37'), E'\n')) AS line WHERE line ~ 'PATTERN'; line ----------------- PATTERN (a+) @@ -1892,12 +1859,11 @@ WINDOW w AS ( -> Function Scan on generate_series s (actual rows=50.00 loops=1) (9 rows) -DROP VIEW rpr_v; -- ============================================================ -- Complex Pattern Tests -- ============================================================ -- Nested groups -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev38 AS SELECT count(*) OVER w FROM generate_series(1, 60) AS s(v) WINDOW w AS ( @@ -1906,7 +1872,7 @@ WINDOW w AS ( PATTERN (((A B) C)+) DEFINE A AS v % 3 = 1, B AS v % 3 = 2, C AS v % 3 = 0 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev38'), E'\n')) AS line WHERE line ~ 'PATTERN'; line ------------------------- PATTERN (((a b) c)+) @@ -1935,11 +1901,10 @@ WINDOW w AS ( -> Function Scan on generate_series s (actual rows=60.00 loops=1) (9 rows) -DROP VIEW rpr_v; -- Multiple alternations -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev39 AS SELECT count(*) OVER w -FROM nfa_test +FROM rpr_nfa_test WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING AFTER MATCH SKIP PAST LAST ROW @@ -1948,7 +1913,7 @@ WINDOW w AS ( A AS cat = 'A', B AS cat = 'B', C AS cat = 'C', D AS cat = 'D', E AS cat = 'E' ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev39'), E'\n')) AS line WHERE line ~ 'PATTERN'; line ---------------------------------- PATTERN ((a | b) (c | d | e)) @@ -1957,7 +1922,7 @@ SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w -FROM nfa_test +FROM rpr_nfa_test WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING AFTER MATCH SKIP PAST LAST ROW @@ -1976,12 +1941,11 @@ WINDOW w AS ( NFA Contexts: 3 peak, 101 total, 40 pruned NFA: 20 matched (len 2/2/2.0), 20 mismatched (len 2/2/2.0) NFA: 0 absorbed, 20 skipped (len 1/1/1.0) - -> Seq Scan on nfa_test (actual rows=100.00 loops=1) + -> Seq Scan on rpr_nfa_test (actual rows=100.00 loops=1) (9 rows) -DROP VIEW rpr_v; -- Optional elements -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev40 AS SELECT count(*) OVER w FROM generate_series(1, 50) AS s(v) WINDOW w AS ( @@ -1990,7 +1954,7 @@ WINDOW w AS ( PATTERN (A B? C) DEFINE A AS v % 4 = 1, B AS v % 4 = 2, C AS v % 4 = 3 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev40'), E'\n')) AS line WHERE line ~ 'PATTERN'; line --------------------- PATTERN (a b? c) @@ -2019,9 +1983,8 @@ WINDOW w AS ( -> Function Scan on generate_series s (actual rows=50.00 loops=1) (9 rows) -DROP VIEW rpr_v; -- Bounded quantifiers -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev41 AS SELECT count(*) OVER w FROM generate_series(1, 100) AS s(v) WINDOW w AS ( @@ -2030,7 +1993,7 @@ WINDOW w AS ( PATTERN (A{2,5} B) DEFINE A AS v % 10 <> 0, B AS v % 10 = 0 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev41'), E'\n')) AS line WHERE line ~ 'PATTERN'; line ----------------------- PATTERN (a{2,5} b) @@ -2059,9 +2022,8 @@ WINDOW w AS ( -> Function Scan on generate_series s (actual rows=100.00 loops=1) (9 rows) -DROP VIEW rpr_v; -- Star quantifier -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev42 AS SELECT count(*) OVER w FROM generate_series(1, 50) AS s(v) WINDOW w AS ( @@ -2070,7 +2032,7 @@ WINDOW w AS ( PATTERN (A B* C) DEFINE A AS v % 10 = 1, B AS v % 10 IN (2,3,4,5,6,7,8), C AS v % 10 = 9 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev42'), E'\n')) AS line WHERE line ~ 'PATTERN'; line --------------------- PATTERN (a b* c) @@ -2099,21 +2061,20 @@ WINDOW w AS ( -> Function Scan on generate_series s (actual rows=50.00 loops=1) (9 rows) -DROP VIEW rpr_v; -- ============================================================ -- Real-world Pattern Examples -- ============================================================ -- Stock price pattern - V-shape (down then up) -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev43 AS SELECT count(*) OVER w -FROM nfa_complex +FROM rpr_nfa_complex WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING AFTER MATCH SKIP PAST LAST ROW PATTERN (D+ U+) DEFINE D AS trend = 'D', U AS trend = 'U' ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev43'), E'\n')) AS line WHERE line ~ 'PATTERN'; line -------------------- PATTERN (d+ u+) @@ -2122,7 +2083,7 @@ SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w -FROM nfa_complex +FROM rpr_nfa_complex WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING AFTER MATCH SKIP PAST LAST ROW @@ -2139,21 +2100,20 @@ WINDOW w AS ( NFA Contexts: 3 peak, 31 total, 3 pruned NFA: 3 matched (len 3/14/8.0), 1 mismatched (len 3/3/3.0) NFA: 9 absorbed (len 1/1/1.0), 14 skipped (len 1/1/1.0) - -> Seq Scan on nfa_complex (actual rows=30.00 loops=1) + -> Seq Scan on rpr_nfa_complex (actual rows=30.00 loops=1) (9 rows) -DROP VIEW rpr_v; -- Stock price pattern - peak (up, stable, down) -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev44 AS SELECT count(*) OVER w -FROM nfa_complex +FROM rpr_nfa_complex WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING AFTER MATCH SKIP PAST LAST ROW PATTERN (U+ S* D+) DEFINE U AS trend = 'U', S AS trend = 'S', D AS trend = 'D' ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev44'), E'\n')) AS line WHERE line ~ 'PATTERN'; line ----------------------- PATTERN (u+ s* d+) @@ -2162,7 +2122,7 @@ SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w -FROM nfa_complex +FROM rpr_nfa_complex WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING AFTER MATCH SKIP PAST LAST ROW @@ -2179,12 +2139,11 @@ WINDOW w AS ( NFA Contexts: 3 peak, 31 total, 1 pruned NFA: 4 matched (len 3/11/7.2), 0 mismatched NFA: 12 absorbed (len 1/1/1.0), 13 skipped (len 1/1/1.0) - -> Seq Scan on nfa_complex (actual rows=30.00 loops=1) + -> Seq Scan on rpr_nfa_complex (actual rows=30.00 loops=1) (9 rows) -DROP VIEW rpr_v; -- Consecutive increasing values (using PREV) -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev45 AS SELECT count(*) OVER w FROM generate_series(1, 50) AS s(v) WINDOW w AS ( @@ -2193,7 +2152,7 @@ WINDOW w AS ( PATTERN (A{3,}) DEFINE A AS v > PREV(v) OR PREV(v) IS NULL ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev45'), E'\n')) AS line WHERE line ~ 'PATTERN'; line -------------------- PATTERN (a{3,}) @@ -2222,12 +2181,11 @@ WINDOW w AS ( -> Function Scan on generate_series s (actual rows=50.00 loops=1) (9 rows) -DROP VIEW rpr_v; -- ============================================================ -- Performance-oriented Tests -- ============================================================ -- Large dataset with simple pattern -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev46 AS SELECT count(*) OVER w FROM generate_series(1, 1000) AS s(v) WINDOW w AS ( @@ -2236,7 +2194,7 @@ WINDOW w AS ( PATTERN (A B) DEFINE A AS v % 2 = 1, B AS v % 2 = 0 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev46'), E'\n')) AS line WHERE line ~ 'PATTERN'; line ------------------ PATTERN (a b) @@ -2265,9 +2223,8 @@ WINDOW w AS ( -> Function Scan on generate_series s (actual rows=1000.00 loops=1) (9 rows) -DROP VIEW rpr_v; -- Large dataset with absorption -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev47 AS SELECT count(*) OVER w FROM generate_series(1, 1000) AS s(v) WINDOW w AS ( @@ -2276,7 +2233,7 @@ WINDOW w AS ( PATTERN (A+ B) DEFINE A AS v % 100 <> 0, B AS v % 100 = 0 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev47'), E'\n')) AS line WHERE line ~ 'PATTERN'; line ------------------- PATTERN (a+ b) @@ -2305,9 +2262,8 @@ WINDOW w AS ( -> Function Scan on generate_series s (actual rows=1000.00 loops=1) (9 rows) -DROP VIEW rpr_v; -- High state merge ratio -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev48 AS SELECT count(*) OVER w FROM generate_series(1, 500) AS s(v) WINDOW w AS ( @@ -2316,7 +2272,7 @@ WINDOW w AS ( PATTERN ((A | B)+ C) DEFINE A AS v % 3 = 1, B AS v % 3 = 2, C AS v % 3 = 0 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev48'), E'\n')) AS line WHERE line ~ 'PATTERN'; line ------------------------- PATTERN ((a | b)+ c) @@ -2345,12 +2301,11 @@ WINDOW w AS ( -> Function Scan on generate_series s (actual rows=500.00 loops=1) (9 rows) -DROP VIEW rpr_v; -- ============================================================ -- INITIAL vs no INITIAL comparison -- ============================================================ -- With INITIAL keyword -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev49 AS SELECT count(*) OVER w FROM generate_series(1, 50) AS s(v) WINDOW w AS ( @@ -2360,7 +2315,7 @@ WINDOW w AS ( PATTERN (A+ B) DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev49'), E'\n')) AS line WHERE line ~ 'PATTERN'; line ------------------- PATTERN (a+ b) @@ -2390,9 +2345,8 @@ WINDOW w AS ( -> Function Scan on generate_series s (actual rows=50.00 loops=1) (9 rows) -DROP VIEW rpr_v; -- Without INITIAL keyword (same behavior currently) -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev50 AS SELECT count(*) OVER w FROM generate_series(1, 50) AS s(v) WINDOW w AS ( @@ -2401,7 +2355,7 @@ WINDOW w AS ( PATTERN (A+ B) DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev50'), E'\n')) AS line WHERE line ~ 'PATTERN'; line ------------------- PATTERN (a+ b) @@ -2430,12 +2384,11 @@ WINDOW w AS ( -> Function Scan on generate_series s (actual rows=50.00 loops=1) (9 rows) -DROP VIEW rpr_v; -- ============================================================ -- Quantifier Variations -- ============================================================ -- Plus quantifier -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev51 AS SELECT count(*) OVER w FROM generate_series(1, 40) AS s(v) WINDOW w AS ( @@ -2444,7 +2397,7 @@ WINDOW w AS ( PATTERN (A+) DEFINE A AS v % 4 <> 0 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev51'), E'\n')) AS line WHERE line ~ 'PATTERN'; line ----------------- PATTERN (a+) @@ -2473,9 +2426,8 @@ WINDOW w AS ( -> Function Scan on generate_series s (actual rows=40.00 loops=1) (9 rows) -DROP VIEW rpr_v; -- Star quantifier (zero or more) -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev52 AS SELECT count(*) OVER w FROM generate_series(1, 40) AS s(v) WINDOW w AS ( @@ -2484,7 +2436,7 @@ WINDOW w AS ( PATTERN (A* B) DEFINE A AS v % 4 IN (1, 2), B AS v % 4 = 3 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev52'), E'\n')) AS line WHERE line ~ 'PATTERN'; line ------------------- PATTERN (a* b) @@ -2513,9 +2465,8 @@ WINDOW w AS ( -> Function Scan on generate_series s (actual rows=40.00 loops=1) (9 rows) -DROP VIEW rpr_v; -- Question mark (zero or one) -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev53 AS SELECT count(*) OVER w FROM generate_series(1, 40) AS s(v) WINDOW w AS ( @@ -2524,7 +2475,7 @@ WINDOW w AS ( PATTERN (A? B C) DEFINE A AS v % 4 = 1, B AS v % 4 = 2, C AS v % 4 = 3 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev53'), E'\n')) AS line WHERE line ~ 'PATTERN'; line --------------------- PATTERN (a? b c) @@ -2553,9 +2504,8 @@ WINDOW w AS ( -> Function Scan on generate_series s (actual rows=40.00 loops=1) (9 rows) -DROP VIEW rpr_v; -- Exact count {n} -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev54 AS SELECT count(*) OVER w FROM generate_series(1, 50) AS s(v) WINDOW w AS ( @@ -2564,7 +2514,7 @@ WINDOW w AS ( PATTERN (A{3} B) DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev54'), E'\n')) AS line WHERE line ~ 'PATTERN'; line --------------------- PATTERN (a{3} b) @@ -2593,9 +2543,8 @@ WINDOW w AS ( -> Function Scan on generate_series s (actual rows=50.00 loops=1) (9 rows) -DROP VIEW rpr_v; -- Range {n,m} -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev55 AS SELECT count(*) OVER w FROM generate_series(1, 50) AS s(v) WINDOW w AS ( @@ -2604,7 +2553,7 @@ WINDOW w AS ( PATTERN (A{2,4} B) DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev55'), E'\n')) AS line WHERE line ~ 'PATTERN'; line ----------------------- PATTERN (a{2,4} b) @@ -2633,9 +2582,8 @@ WINDOW w AS ( -> Function Scan on generate_series s (actual rows=50.00 loops=1) (9 rows) -DROP VIEW rpr_v; -- At least {n,} -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev56 AS SELECT count(*) OVER w FROM generate_series(1, 50) AS s(v) WINDOW w AS ( @@ -2644,7 +2592,7 @@ WINDOW w AS ( PATTERN (A{3,} B) DEFINE A AS v % 10 <> 0, B AS v % 10 = 0 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev56'), E'\n')) AS line WHERE line ~ 'PATTERN'; line ---------------------- PATTERN (a{3,} b) @@ -2673,13 +2621,12 @@ WINDOW w AS ( -> Function Scan on generate_series s (actual rows=50.00 loops=1) (9 rows) -DROP VIEW rpr_v; -- ============================================================ -- Regression Tests for Statistics Accuracy -- ============================================================ -- Verify state count accuracy -- Pattern A+ B with 20 rows should show predictable state behavior -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev57 AS SELECT count(*) OVER w FROM generate_series(1, 20) AS s(v) WINDOW w AS ( @@ -2688,7 +2635,7 @@ WINDOW w AS ( PATTERN (A+ B) DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev57'), E'\n')) AS line WHERE line ~ 'PATTERN'; line ------------------- PATTERN (a+ b) @@ -2717,9 +2664,8 @@ WINDOW w AS ( -> Function Scan on generate_series s (actual rows=20.00 loops=1) (9 rows) -DROP VIEW rpr_v; -- Verify context count with known absorption -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev58 AS SELECT count(*) OVER w FROM generate_series(1, 30) AS s(v) WINDOW w AS ( @@ -2728,7 +2674,7 @@ WINDOW w AS ( PATTERN (A+ B C) DEFINE A AS v % 10 IN (1,2,3,4,5,6,7), B AS v % 10 = 8, C AS v % 10 = 9 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev58'), E'\n')) AS line WHERE line ~ 'PATTERN'; line --------------------- PATTERN (a+ b c) @@ -2757,9 +2703,8 @@ WINDOW w AS ( -> Function Scan on generate_series s (actual rows=30.00 loops=1) (9 rows) -DROP VIEW rpr_v; -- Verify match length with fixed-length pattern -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev59 AS SELECT count(*) OVER w FROM generate_series(1, 30) AS s(v) WINDOW w AS ( @@ -2768,7 +2713,7 @@ WINDOW w AS ( PATTERN (A B C) DEFINE A AS v % 3 = 1, B AS v % 3 = 2, C AS v % 3 = 0 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev59'), E'\n')) AS line WHERE line ~ 'PATTERN'; line -------------------- PATTERN (a b c) @@ -2797,21 +2742,20 @@ WINDOW w AS ( -> Function Scan on generate_series s (actual rows=30.00 loops=1) (9 rows) -DROP VIEW rpr_v; -- ============================================================ -- Alternation Pattern Tests -- ============================================================ -- Simple alternation -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev60 AS SELECT count(*) OVER w -FROM nfa_test +FROM rpr_nfa_test WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING AFTER MATCH SKIP PAST LAST ROW PATTERN ((A | B) C) DEFINE A AS cat = 'A', B AS cat = 'B', C AS cat = 'C' ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev60'), E'\n')) AS line WHERE line ~ 'PATTERN'; line ------------------------ PATTERN ((a | b) c) @@ -2820,7 +2764,7 @@ SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w -FROM nfa_test +FROM rpr_nfa_test WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING AFTER MATCH SKIP PAST LAST ROW @@ -2837,14 +2781,13 @@ WINDOW w AS ( NFA Contexts: 3 peak, 101 total, 40 pruned NFA: 20 matched (len 2/2/2.0), 20 mismatched (len 2/2/2.0) NFA: 0 absorbed, 20 skipped (len 1/1/1.0) - -> Seq Scan on nfa_test (actual rows=100.00 loops=1) + -> Seq Scan on rpr_nfa_test (actual rows=100.00 loops=1) (9 rows) -DROP VIEW rpr_v; -- Multiple items in alternation -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev61 AS SELECT count(*) OVER w -FROM nfa_test +FROM rpr_nfa_test WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING AFTER MATCH SKIP PAST LAST ROW @@ -2853,7 +2796,7 @@ WINDOW w AS ( A AS cat = 'A', B AS cat = 'B', C AS cat = 'C', D AS cat = 'D', E AS cat = 'E' ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev61'), E'\n')) AS line WHERE line ~ 'PATTERN'; line -------------------------------- PATTERN ((a | b | c | d) e) @@ -2862,7 +2805,7 @@ SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w -FROM nfa_test +FROM rpr_nfa_test WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING AFTER MATCH SKIP PAST LAST ROW @@ -2881,12 +2824,11 @@ WINDOW w AS ( NFA Contexts: 3 peak, 101 total, 0 pruned NFA: 20 matched (len 2/2/2.0), 60 mismatched (len 2/2/2.0) NFA: 0 absorbed, 20 skipped (len 1/1/1.0) - -> Seq Scan on nfa_test (actual rows=100.00 loops=1) + -> Seq Scan on rpr_nfa_test (actual rows=100.00 loops=1) (9 rows) -DROP VIEW rpr_v; -- Alternation with quantifiers -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev62 AS SELECT count(*) OVER w FROM generate_series(1, 50) AS s(v) WINDOW w AS ( @@ -2895,7 +2837,7 @@ WINDOW w AS ( PATTERN ((A | B)+ C) DEFINE A AS v % 3 = 1, B AS v % 3 = 2, C AS v % 3 = 0 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev62'), E'\n')) AS line WHERE line ~ 'PATTERN'; line ------------------------- PATTERN ((a | b)+ c) @@ -2924,9 +2866,8 @@ WINDOW w AS ( -> Function Scan on generate_series s (actual rows=50.00 loops=1) (9 rows) -DROP VIEW rpr_v; -- Multiple alternatives (4+) -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev63 AS SELECT count(*) OVER w FROM generate_series(1, 100) AS s(v) WINDOW w AS ( @@ -2934,7 +2875,7 @@ WINDOW w AS ( PATTERN (A | B | C | D | E) DEFINE A AS v % 5 = 0, B AS v % 5 = 1, C AS v % 5 = 2, D AS v % 5 = 3, E AS v % 5 = 4 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev63'), E'\n')) AS line WHERE line ~ 'PATTERN'; line -------------------------------- PATTERN (a | b | c | d | e) @@ -2961,9 +2902,8 @@ WINDOW w AS ( -> Function Scan on generate_series s (actual rows=100.00 loops=1) (8 rows) -DROP VIEW rpr_v; -- Alternation at start -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev64 AS SELECT count(*) OVER w FROM generate_series(1, 60) AS s(v) WINDOW w AS ( @@ -2971,7 +2911,7 @@ WINDOW w AS ( PATTERN ((A | B) C D) DEFINE A AS v % 4 = 0, B AS v % 4 = 1, C AS v % 4 = 2, D AS v % 4 = 3 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev64'), E'\n')) AS line WHERE line ~ 'PATTERN'; line -------------------------- PATTERN ((a | b) c d) @@ -2999,9 +2939,8 @@ WINDOW w AS ( -> Function Scan on generate_series s (actual rows=60.00 loops=1) (9 rows) -DROP VIEW rpr_v; -- Multiple sequential alternations -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev65 AS SELECT count(*) OVER w FROM generate_series(1, 100) AS s(v) WINDOW w AS ( @@ -3009,7 +2948,7 @@ WINDOW w AS ( PATTERN ((A | B) C (D | E) F) DEFINE A AS v % 6 = 0, B AS v % 6 = 1, C AS v % 6 = 2, D AS v % 6 = 3, E AS v % 6 = 4, F AS v % 6 = 5 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev65'), E'\n')) AS line WHERE line ~ 'PATTERN'; line ---------------------------------- PATTERN ((a | b) c (d | e) f) @@ -3036,9 +2975,8 @@ WINDOW w AS ( -> Function Scan on generate_series s (actual rows=100.00 loops=1) (8 rows) -DROP VIEW rpr_v; -- Quantified alternatives -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev66 AS SELECT count(*) OVER w FROM generate_series(1, 60) AS s(v) WINDOW w AS ( @@ -3046,7 +2984,7 @@ WINDOW w AS ( PATTERN ((A+ | B+) C) DEFINE A AS v % 3 = 0, B AS v % 3 = 1, C AS v % 3 = 2 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev66'), E'\n')) AS line WHERE line ~ 'PATTERN'; line -------------------------- PATTERN ((a+ | b+) c) @@ -3074,9 +3012,8 @@ WINDOW w AS ( -> Function Scan on generate_series s (actual rows=60.00 loops=1) (9 rows) -DROP VIEW rpr_v; -- Alternation at end -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev67 AS SELECT count(*) OVER w FROM generate_series(1, 60) AS s(v) WINDOW w AS ( @@ -3084,7 +3021,7 @@ WINDOW w AS ( PATTERN (A B (C | D)) DEFINE A AS v % 4 = 0, B AS v % 4 = 1, C AS v % 4 = 2, D AS v % 4 = 3 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev67'), E'\n')) AS line WHERE line ~ 'PATTERN'; line -------------------------- PATTERN (a b (c | d)) @@ -3112,10 +3049,9 @@ WINDOW w AS ( -> Function Scan on generate_series s (actual rows=60.00 loops=1) (9 rows) -DROP VIEW rpr_v; -- Nested ALT at start of branch inside outer ALT -- Pattern: (A ((B | C) D | E)) - preceding VAR + inner ALT as first branch element -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev68 AS SELECT count(*) OVER w FROM generate_series(1, 20) AS s(v) WINDOW w AS ( @@ -3123,7 +3059,7 @@ WINDOW w AS ( PATTERN (A ((B | C) D | E)) DEFINE A AS v % 5 = 0, B AS v % 5 = 1, C AS v % 5 = 2, D AS v % 5 = 3, E AS v % 5 = 4 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev68'), E'\n')) AS line WHERE line ~ 'PATTERN'; line -------------------------------- PATTERN (a ((b | c) d | e)) @@ -3150,10 +3086,9 @@ WINDOW w AS ( -> Function Scan on generate_series s (actual rows=20.00 loops=1) (8 rows) -DROP VIEW rpr_v; -- Nested ALT at end of branch inside outer ALT -- Pattern: (C (A | B) | D) - inner ALT is last element in outer branch -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev69 AS SELECT count(*) OVER w FROM generate_series(1, 20) AS s(v) WINDOW w AS ( @@ -3161,7 +3096,7 @@ WINDOW w AS ( PATTERN (C (A | B) | D) DEFINE A AS v % 4 = 0, B AS v % 4 = 1, C AS v % 4 = 2, D AS v % 4 = 3 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev69'), E'\n')) AS line WHERE line ~ 'PATTERN'; line ---------------------------- PATTERN (c (a | b) | d) @@ -3188,12 +3123,11 @@ WINDOW w AS ( -> Function Scan on generate_series s (actual rows=20.00 loops=1) (8 rows) -DROP VIEW rpr_v; -- ============================================================ -- Group Pattern Tests -- ============================================================ -- Simple group -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev70 AS SELECT count(*) OVER w FROM generate_series(1, 40) AS s(v) WINDOW w AS ( @@ -3202,7 +3136,7 @@ WINDOW w AS ( PATTERN ((A B)+) DEFINE A AS v % 2 = 1, B AS v % 2 = 0 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev70'), E'\n')) AS line WHERE line ~ 'PATTERN'; line --------------------- PATTERN ((a b)+) @@ -3231,9 +3165,8 @@ WINDOW w AS ( -> Function Scan on generate_series s (actual rows=40.00 loops=1) (9 rows) -DROP VIEW rpr_v; -- Group with bounded quantifier -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev71 AS SELECT count(*) OVER w FROM generate_series(1, 40) AS s(v) WINDOW w AS ( @@ -3242,7 +3175,7 @@ WINDOW w AS ( PATTERN ((A B){2,4}) DEFINE A AS v % 2 = 1, B AS v % 2 = 0 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev71'), E'\n')) AS line WHERE line ~ 'PATTERN'; line ------------------------- PATTERN ((a b){2,4}) @@ -3271,9 +3204,8 @@ WINDOW w AS ( -> Function Scan on generate_series s (actual rows=40.00 loops=1) (9 rows) -DROP VIEW rpr_v; -- Nested groups -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev72 AS SELECT count(*) OVER w FROM generate_series(1, 60) AS s(v) WINDOW w AS ( @@ -3282,7 +3214,7 @@ WINDOW w AS ( PATTERN (((A B){2})+) DEFINE A AS v % 2 = 1, B AS v % 2 = 0 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev72'), E'\n')) AS line WHERE line ~ 'PATTERN'; line -------------------------- PATTERN (((a b){2})+) @@ -3311,9 +3243,8 @@ WINDOW w AS ( -> Function Scan on generate_series s (actual rows=60.00 loops=1) (9 rows) -DROP VIEW rpr_v; -- Deep nesting (3+ levels) -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev73 AS SELECT count(*) OVER w FROM generate_series(1, 40) AS s(v) WINDOW w AS ( @@ -3321,7 +3252,7 @@ WINDOW w AS ( PATTERN ((((A | B)+)+)+) DEFINE A AS v % 2 = 0, B AS v % 2 = 1 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev73'), E'\n')) AS line WHERE line ~ 'PATTERN'; line ----------------------------- PATTERN ((((a | b)+)+)+) @@ -3349,9 +3280,8 @@ WINDOW w AS ( -> Function Scan on generate_series s (actual rows=40.00 loops=1) (9 rows) -DROP VIEW rpr_v; -- Bounded quantifier on alternation -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev74 AS SELECT count(*) OVER w FROM generate_series(1, 60) AS s(v) WINDOW w AS ( @@ -3359,7 +3289,7 @@ WINDOW w AS ( PATTERN ((A | B){2,3} C) DEFINE A AS v % 3 = 0, B AS v % 3 = 1, C AS v % 3 = 2 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev74'), E'\n')) AS line WHERE line ~ 'PATTERN'; line ----------------------------- PATTERN ((a | b){2,3} c) @@ -3387,9 +3317,8 @@ WINDOW w AS ( -> Function Scan on generate_series s (actual rows=60.00 loops=1) (9 rows) -DROP VIEW rpr_v; -- Nested groups with quantifiers -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev75 AS SELECT count(*) OVER w FROM generate_series(1, 60) AS s(v) WINDOW w AS ( @@ -3397,7 +3326,7 @@ WINDOW w AS ( PATTERN (((A B)+ C)*) DEFINE A AS v % 3 = 0, B AS v % 3 = 1, C AS v % 3 = 2 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev75'), E'\n')) AS line WHERE line ~ 'PATTERN'; line -------------------------- PATTERN (((a b)+ c)*) @@ -3425,9 +3354,8 @@ WINDOW w AS ( -> Function Scan on generate_series s (actual rows=60.00 loops=1) (9 rows) -DROP VIEW rpr_v; -- Partial nested quantification -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev76 AS SELECT count(*) OVER w FROM generate_series(1, 60) AS s(v) WINDOW w AS ( @@ -3435,7 +3363,7 @@ WINDOW w AS ( PATTERN ((A (B C)+)*) DEFINE A AS v % 3 = 0, B AS v % 3 = 1, C AS v % 3 = 2 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev76'), E'\n')) AS line WHERE line ~ 'PATTERN'; line -------------------------- PATTERN ((a (b c)+)*) @@ -3463,12 +3391,11 @@ WINDOW w AS ( -> Function Scan on generate_series s (actual rows=60.00 loops=1) (9 rows) -DROP VIEW rpr_v; -- ============================================================ -- Window Function Combinations -- ============================================================ -- count(*) with pattern -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev77 AS SELECT count(*) OVER w FROM generate_series(1, 30) AS s(v) WINDOW w AS ( @@ -3477,7 +3404,7 @@ WINDOW w AS ( PATTERN (A+ B) DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev77'), E'\n')) AS line WHERE line ~ 'PATTERN'; line ------------------- PATTERN (a+ b) @@ -3506,9 +3433,8 @@ WINDOW w AS ( -> Function Scan on generate_series s (actual rows=30.00 loops=1) (9 rows) -DROP VIEW rpr_v; -- first_value with pattern -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev78 AS SELECT first_value(v) OVER w FROM generate_series(1, 30) AS s(v) WINDOW w AS ( @@ -3517,7 +3443,7 @@ WINDOW w AS ( PATTERN (A+ B) DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev78'), E'\n')) AS line WHERE line ~ 'PATTERN'; line ------------------- PATTERN (a+ b) @@ -3546,9 +3472,8 @@ WINDOW w AS ( -> Function Scan on generate_series s (actual rows=30.00 loops=1) (9 rows) -DROP VIEW rpr_v; -- last_value with pattern -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev79 AS SELECT last_value(v) OVER w FROM generate_series(1, 30) AS s(v) WINDOW w AS ( @@ -3557,7 +3482,7 @@ WINDOW w AS ( PATTERN (A+ B) DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev79'), E'\n')) AS line WHERE line ~ 'PATTERN'; line ------------------- PATTERN (a+ b) @@ -3586,9 +3511,8 @@ WINDOW w AS ( -> Function Scan on generate_series s (actual rows=30.00 loops=1) (9 rows) -DROP VIEW rpr_v; -- Multiple window functions -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev80 AS SELECT count(*) OVER w, first_value(v) OVER w, @@ -3600,7 +3524,7 @@ WINDOW w AS ( PATTERN (A+ B) DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev80'), E'\n')) AS line WHERE line ~ 'PATTERN'; line ------------------- PATTERN (a+ b) @@ -3632,12 +3556,11 @@ WINDOW w AS ( -> Function Scan on generate_series s (actual rows=30.00 loops=1) (9 rows) -DROP VIEW rpr_v; -- ============================================================ -- DEFINE Expression Variations -- ============================================================ -- Complex boolean expressions -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev81 AS SELECT count(*) OVER w FROM generate_series(1, 50) AS s(v) WINDOW w AS ( @@ -3648,7 +3571,7 @@ WINDOW w AS ( A AS (v % 5 <> 0) AND (v % 3 <> 0), B AS (v % 5 = 0) OR (v % 3 = 0) ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev81'), E'\n')) AS line WHERE line ~ 'PATTERN'; line ------------------- PATTERN (a+ b) @@ -3679,9 +3602,8 @@ WINDOW w AS ( -> Function Scan on generate_series s (actual rows=50.00 loops=1) (9 rows) -DROP VIEW rpr_v; -- Using PREV function -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev82 AS SELECT count(*) OVER w FROM generate_series(1, 30) AS s(v) WINDOW w AS ( @@ -3693,7 +3615,7 @@ WINDOW w AS ( U AS v > PREV(v), D AS v < PREV(v) ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev82'), E'\n')) AS line WHERE line ~ 'PATTERN'; line ---------------------- PATTERN (s u+ d+) @@ -3724,9 +3646,8 @@ WINDOW w AS ( -> Function Scan on generate_series s (actual rows=30.00 loops=1) (8 rows) -DROP VIEW rpr_v; -- Using NULL comparisons -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev83 AS SELECT count(*) OVER w FROM ( SELECT CASE WHEN v % 5 = 0 THEN NULL ELSE v END AS v @@ -3738,7 +3659,7 @@ WINDOW w AS ( PATTERN (A+ B) DEFINE A AS v IS NOT NULL, B AS v IS NULL ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev83'), E'\n')) AS line WHERE line ~ 'PATTERN'; line ------------------- PATTERN (a+ b) @@ -3770,12 +3691,11 @@ WINDOW w AS ( -> Function Scan on generate_series v (actual rows=30.00 loops=1) (9 rows) -DROP VIEW rpr_v; -- ============================================================ -- Large Scale Statistics Verification -- ============================================================ -- 500 rows - verify statistics scale correctly -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev84 AS SELECT count(*) OVER w FROM generate_series(1, 500) AS s(v) WINDOW w AS ( @@ -3784,7 +3704,7 @@ WINDOW w AS ( PATTERN (A+ B C) DEFINE A AS v % 10 < 7, B AS v % 10 = 7, C AS v % 10 = 8 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev84'), E'\n')) AS line WHERE line ~ 'PATTERN'; line --------------------- PATTERN (a+ b c) @@ -3813,9 +3733,8 @@ WINDOW w AS ( -> Function Scan on generate_series s (actual rows=500.00 loops=1) (9 rows) -DROP VIEW rpr_v; -- High match count scenario -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev85 AS SELECT count(*) OVER w FROM generate_series(1, 500) AS s(v) WINDOW w AS ( @@ -3824,7 +3743,7 @@ WINDOW w AS ( PATTERN (A B) DEFINE A AS v % 2 = 1, B AS v % 2 = 0 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev85'), E'\n')) AS line WHERE line ~ 'PATTERN'; line ------------------ PATTERN (a b) @@ -3853,9 +3772,8 @@ WINDOW w AS ( -> Function Scan on generate_series s (actual rows=500.00 loops=1) (9 rows) -DROP VIEW rpr_v; -- High skip count scenario -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev86 AS SELECT count(*) OVER w FROM generate_series(1, 500) AS s(v) WINDOW w AS ( @@ -3869,7 +3787,7 @@ WINDOW w AS ( D AS v % 100 = 4, E AS v % 100 = 5 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev86'), E'\n')) AS line WHERE line ~ 'PATTERN'; line ------------------------ PATTERN (a b c d e) @@ -3903,7 +3821,3 @@ WINDOW w AS ( -> Function Scan on generate_series s (actual rows=500.00 loops=1) (9 rows) -DROP VIEW rpr_v; --- Cleanup -DROP TABLE nfa_test; -DROP TABLE nfa_complex; diff --git a/src/test/regress/sql/rpr_base.sql b/src/test/regress/sql/rpr_base.sql index da28de1a8f9..453860c1499 100644 --- a/src/test/regress/sql/rpr_base.sql +++ b/src/test/regress/sql/rpr_base.sql @@ -12,7 +12,7 @@ -- Quantifiers Tests -- Navigation Functions Tests -- SKIP TO / INITIAL Tests --- Serialization/Deserialization Tests +-- Serialization/Deserialization Tests (objects kept for pg_upgrade/pg_dump) -- Error Cases Tests -- -- Planner Layer: @@ -1408,8 +1408,6 @@ SELECT * FROM rpr_serial_v1 ORDER BY id; -- Verify deparsing SELECT pg_get_viewdef('rpr_serial_v1'::regclass); -DROP VIEW rpr_serial_v1; - -- Complex pattern with alternation CREATE VIEW rpr_serial_v2 AS SELECT id, val, COUNT(*) OVER w as cnt @@ -1424,8 +1422,6 @@ WINDOW w AS ( SELECT * FROM rpr_serial_v2 ORDER BY id; SELECT pg_get_viewdef('rpr_serial_v2'::regclass); -DROP VIEW rpr_serial_v2; - -- Pattern with grouping and quantifiers CREATE VIEW rpr_serial_v3 AS SELECT id, val, COUNT(*) OVER w as cnt @@ -1443,8 +1439,6 @@ WINDOW w AS ( SELECT * FROM rpr_serial_v3 ORDER BY id; SELECT pg_get_viewdef('rpr_serial_v3'::regclass); -DROP VIEW rpr_serial_v3; - -- All features combined CREATE VIEW rpr_serial_v4 AS SELECT id, val, COUNT(*) OVER w as cnt @@ -1465,8 +1459,6 @@ WINDOW w AS ( SELECT * FROM rpr_serial_v4 ORDER BY id; SELECT pg_get_viewdef('rpr_serial_v4'::regclass); -DROP VIEW rpr_serial_v4; - -- Additional quantifiers for deparsing coverage -- ? quantifier (zero or one) @@ -1483,8 +1475,6 @@ WINDOW w AS ( SELECT * FROM rpr_serial_v5 ORDER BY id; SELECT pg_get_viewdef('rpr_serial_v5'::regclass); -DROP VIEW rpr_serial_v5; - -- {n,} quantifier (n or more) CREATE VIEW rpr_serial_v6 AS SELECT id, val, COUNT(*) OVER w as cnt @@ -1499,8 +1489,6 @@ WINDOW w AS ( SELECT * FROM rpr_serial_v6 ORDER BY id; SELECT pg_get_viewdef('rpr_serial_v6'::regclass); -DROP VIEW rpr_serial_v6; - -- {n} quantifier (exactly n) CREATE VIEW rpr_serial_v7 AS SELECT id, val, COUNT(*) OVER w as cnt @@ -1515,8 +1503,6 @@ WINDOW w AS ( SELECT * FROM rpr_serial_v7 ORDER BY id; SELECT pg_get_viewdef('rpr_serial_v7'::regclass); -DROP VIEW rpr_serial_v7; - -- Nested ALT pattern (tests deparse of complex nested structure) CREATE VIEW rpr_serial_v8 AS SELECT id, val, COUNT(*) OVER w as cnt @@ -1531,8 +1517,6 @@ WINDOW w AS ( SELECT * FROM rpr_serial_v8 ORDER BY id; SELECT pg_get_viewdef('rpr_serial_v8'::regclass); -DROP VIEW rpr_serial_v8; - -- Reluctant {1}? quantifier deparse through ruleutils CREATE VIEW rpr_quant_reluctant_v AS SELECT id, val, count(*) OVER w @@ -1543,7 +1527,6 @@ WINDOW w AS (ORDER BY id PATTERN (A{1}? B) DEFINE A AS val > 0, B AS val > 0); SELECT pg_get_viewdef('rpr_quant_reluctant_v'::regclass); -DROP VIEW rpr_quant_reluctant_v; -- Materialized view (if supported) @@ -1567,9 +1550,6 @@ SELECT pg_get_viewdef('rpr_mview_v1'::regclass); REFRESH MATERIALIZED VIEW rpr_mview_v1; SELECT * FROM rpr_mview_v1 ORDER BY id; -DROP MATERIALIZED VIEW rpr_mview_v1; -DROP TABLE rpr_mview; - -- CREATE TABLE AS SELECT with RPR CREATE TABLE rpr_ctas (id INT, val INT); INSERT INTO rpr_ctas VALUES (1, 10), (2, 20), (3, 15), (4, 25); @@ -1863,9 +1843,6 @@ WINDOW SELECT * FROM rpr_multiwin_v ORDER BY id; SELECT pg_get_viewdef('rpr_multiwin_v'::regclass); -DROP VIEW rpr_multiwin_v; -DROP TABLE rpr_multiwin; - -- {n} quantifier display in view CREATE VIEW rpr_quant_n_v AS SELECT id, val, count(*) OVER w @@ -1876,7 +1853,6 @@ WINDOW w AS (ORDER BY id PATTERN (A{3}) DEFINE A AS val > 0); SELECT pg_get_viewdef('rpr_quant_n_v'::regclass); -DROP VIEW rpr_quant_n_v; -- {n,} quantifier display in view CREATE VIEW rpr_quant_n_plus_v AS @@ -1888,9 +1864,6 @@ WINDOW w AS (ORDER BY id PATTERN (A{2,}) DEFINE A AS val > 0); SELECT pg_get_viewdef('rpr_quant_n_plus_v'::regclass); -DROP VIEW rpr_quant_n_plus_v; - -DROP TABLE rpr_serial; -- ============================================================ -- Error Cases Tests diff --git a/src/test/regress/sql/rpr_explain.sql b/src/test/regress/sql/rpr_explain.sql index 4bb49650bb7..8e22382a68e 100644 --- a/src/test/regress/sql/rpr_explain.sql +++ b/src/test/regress/sql/rpr_explain.sql @@ -3,6 +3,9 @@ -- Tests for Row Pattern Recognition EXPLAIN output -- ============================================================ -- +-- Views and tables in this file are intentionally not dropped, +-- so that pg_upgrade/pg_dump can test RPR syntax serialization. +-- -- This test suite validates EXPLAIN output for RPR queries, -- including NFA statistics shown in EXPLAIN ANALYZE: -- - NFA States: peak, total, merged @@ -76,14 +79,14 @@ end; $$; -- Setup: Create test tables -CREATE TEMP TABLE nfa_test ( +CREATE TABLE rpr_nfa_test ( id serial, v int, cat char(1) ); -- Insert test data: 100 rows with predictable pattern -INSERT INTO nfa_test (v, cat) +INSERT INTO rpr_nfa_test (v, cat) SELECT i, CASE WHEN i % 5 = 1 THEN 'A' @@ -95,13 +98,13 @@ SELECT i, FROM generate_series(1, 100) i; -- Additional test table with more complex patterns -CREATE TEMP TABLE nfa_complex ( +CREATE TABLE rpr_nfa_complex ( id serial, price int, trend char(1) -- U=up, D=down, S=stable ); -INSERT INTO nfa_complex (price, trend) +INSERT INTO rpr_nfa_complex (price, trend) VALUES (100, 'S'), (105, 'U'), (110, 'U'), (108, 'D'), (112, 'U'), (115, 'U'), (113, 'D'), (111, 'D'), (109, 'D'), (110, 'U'), @@ -115,77 +118,74 @@ VALUES -- ============================================================ -- Simple pattern - should show basic statistics -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev01 AS SELECT count(*) OVER w -FROM nfa_test +FROM rpr_nfa_test WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING AFTER MATCH SKIP PAST LAST ROW PATTERN (A B) DEFINE A AS cat = 'A', B AS cat = 'B' ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev01'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w -FROM nfa_test +FROM rpr_nfa_test WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING AFTER MATCH SKIP PAST LAST ROW PATTERN (A B) DEFINE A AS cat = ''A'', B AS cat = ''B'' )'); -DROP VIEW rpr_v; -- Pattern with no matches - 0 matched -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev02 AS SELECT count(*) OVER w -FROM nfa_test +FROM rpr_nfa_test WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING AFTER MATCH SKIP PAST LAST ROW PATTERN (X Y Z) DEFINE X AS cat = 'X', Y AS cat = 'Y', Z AS cat = 'Z' ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev02'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w -FROM nfa_test +FROM rpr_nfa_test WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING AFTER MATCH SKIP PAST LAST ROW PATTERN (X Y Z) DEFINE X AS cat = ''X'', Y AS cat = ''Y'', Z AS cat = ''Z'' );'); -DROP VIEW rpr_v; -- Pattern matching every row - high match count -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev03 AS SELECT count(*) OVER w -FROM nfa_test +FROM rpr_nfa_test WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING AFTER MATCH SKIP PAST LAST ROW PATTERN (R) DEFINE R AS TRUE ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev03'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w -FROM nfa_test +FROM rpr_nfa_test WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING AFTER MATCH SKIP PAST LAST ROW PATTERN (R) DEFINE R AS TRUE );'); -DROP VIEW rpr_v; -- Regression test: Space before parenthesis in pattern deparse -- Verifies that "A (B | C)" correctly outputs as "a (b | c)" with space -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev04 AS SELECT count(*) OVER w FROM generate_series(1, 20) AS s(v) WINDOW w AS ( @@ -193,7 +193,7 @@ WINDOW w AS ( PATTERN (A (B | C)) DEFINE A AS v % 3 = 1, B AS v % 3 = 2, C AS v % 3 = 0 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev04'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w @@ -203,12 +203,11 @@ WINDOW w AS ( PATTERN (A (B | C)) DEFINE A AS v % 3 = 1, B AS v % 3 = 2, C AS v % 3 = 0 );'); -DROP VIEW rpr_v; -- Regression test: Sequential alternations at same depth -- Verifies that "((B | C) (D | E))" correctly outputs as "(b | c) (d | e)" -- Previously failed due to missing parentheses on ALT depth decrease -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev05 AS SELECT count(*) OVER w FROM generate_series(1, 30) AS s(v) WINDOW w AS ( @@ -216,7 +215,7 @@ WINDOW w AS ( PATTERN (A ((B | C) (D | E))*) DEFINE A AS v % 5 = 1, B AS v % 5 = 2, C AS v % 5 = 3, D AS v % 5 = 4, E AS v % 5 = 0 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev05'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w @@ -226,14 +225,13 @@ WINDOW w AS ( PATTERN (A ((B | C) (D | E))*) DEFINE A AS v % 5 = 1, B AS v % 5 = 2, C AS v % 5 = 3, D AS v % 5 = 4, E AS v % 5 = 0 );'); -DROP VIEW rpr_v; -- ============================================================ -- State Statistics Tests (peak, total, merged) -- ============================================================ -- Simple quantifier pattern - A+ with short matches (no merging) -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev06 AS SELECT count(*) OVER w FROM generate_series(1, 50) AS s(v) WINDOW w AS ( @@ -242,7 +240,7 @@ WINDOW w AS ( PATTERN (A+) DEFINE A AS v % 2 = 1 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev06'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w @@ -253,12 +251,11 @@ WINDOW w AS ( PATTERN (A+) DEFINE A AS v % 2 = 1 );'); -DROP VIEW rpr_v; -- Alternation pattern - multiple state branches -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev07 AS SELECT count(*) OVER w -FROM nfa_test +FROM rpr_nfa_test WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING AFTER MATCH SKIP PAST LAST ROW @@ -267,11 +264,11 @@ WINDOW w AS ( A AS cat = 'A', B AS cat = 'B', C AS cat = 'C', D AS cat = 'D', E AS cat = 'E' ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev07'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w -FROM nfa_test +FROM rpr_nfa_test WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING AFTER MATCH SKIP PAST LAST ROW @@ -280,10 +277,9 @@ WINDOW w AS ( A AS cat = ''A'', B AS cat = ''B'', C AS cat = ''C'', D AS cat = ''D'', E AS cat = ''E'' );'); -DROP VIEW rpr_v; -- Complex pattern with high state count -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev08 AS SELECT count(*) OVER w FROM generate_series(1, 100) AS s(v) WINDOW w AS ( @@ -295,7 +291,7 @@ WINDOW w AS ( B AS v % 3 = 2, C AS v % 3 = 0 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev08'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w @@ -309,10 +305,9 @@ WINDOW w AS ( B AS v % 3 = 2, C AS v % 3 = 0 );'); -DROP VIEW rpr_v; -- Grouped pattern with quantifier - state count with grouping -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev09 AS SELECT count(*) OVER w FROM generate_series(1, 60) AS s(v) WINDOW w AS ( @@ -321,7 +316,7 @@ WINDOW w AS ( PATTERN ((A B)+) DEFINE A AS v % 2 = 1, B AS v % 2 = 0 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev09'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w @@ -332,11 +327,10 @@ WINDOW w AS ( PATTERN ((A B)+) DEFINE A AS v % 2 = 1, B AS v % 2 = 0 );'); -DROP VIEW rpr_v; -- State explosion pattern - many alternations -- Pattern (A|B)(A|B)(A|B)(A|B) can create many parallel states -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev10 AS SELECT count(*) OVER w FROM generate_series(1, 100) AS s(v) WINDOW w AS ( @@ -345,7 +339,7 @@ WINDOW w AS ( PATTERN ((A | B) (A | B) (A | B) (A | B) (A | B) (A | B) (A | B) (A | B)) DEFINE A AS v % 2 = 1, B AS v % 2 = 0 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev10'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w @@ -356,11 +350,10 @@ WINDOW w AS ( PATTERN ((A | B) (A | B) (A | B) (A | B) (A | B) (A | B) (A | B) (A | B)) DEFINE A AS v % 2 = 1, B AS v % 2 = 0 );'); -DROP VIEW rpr_v; -- Consecutive ALT merge followed by different ALT -- Tests mergeConsecutiveAlts flush on ALT change: (A|B){2} (C|D) -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev11 AS SELECT count(*) OVER w FROM generate_series(1, 40) AS s(v) WINDOW w AS ( @@ -369,7 +362,7 @@ WINDOW w AS ( PATTERN ((A | B) (A | B) (C | D)) DEFINE A AS v % 4 = 0, B AS v % 4 = 1, C AS v % 4 = 2, D AS v % 4 = 3 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev11'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w @@ -380,11 +373,10 @@ WINDOW w AS ( PATTERN ((A | B) (A | B) (C | D)) DEFINE A AS v % 4 = 0, B AS v % 4 = 1, C AS v % 4 = 2, D AS v % 4 = 3 );'); -DROP VIEW rpr_v; -- Consecutive ALT merge followed by non-ALT element -- Tests mergeConsecutiveAlts flush on non-ALT: (A|B){2} c -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev12 AS SELECT count(*) OVER w FROM generate_series(1, 40) AS s(v) WINDOW w AS ( @@ -393,7 +385,7 @@ WINDOW w AS ( PATTERN ((A | B) (A | B) C) DEFINE A AS v % 3 = 0, B AS v % 3 = 1, C AS v % 3 = 2 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev12'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w @@ -404,10 +396,9 @@ WINDOW w AS ( PATTERN ((A | B) (A | B) C) DEFINE A AS v % 3 = 0, B AS v % 3 = 1, C AS v % 3 = 2 );'); -DROP VIEW rpr_v; -- ALT prefix/suffix absorbed into GROUP: (A|B) (A|B)+ (A|B) -> (A|B){3,} -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev13 AS SELECT count(*) OVER w FROM generate_series(1, 40) AS s(v) WINDOW w AS ( @@ -416,7 +407,7 @@ WINDOW w AS ( PATTERN ((A | B) (A | B)+ (A | B)) DEFINE A AS v % 2 = 0, B AS v % 2 = 1 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev13'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w @@ -427,10 +418,9 @@ WINDOW w AS ( PATTERN ((A | B) (A | B)+ (A | B)) DEFINE A AS v % 2 = 0, B AS v % 2 = 1 );'); -DROP VIEW rpr_v; -- High state count - alternation with plus quantifier -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev14 AS SELECT count(*) OVER w FROM generate_series(1, 100) AS s(v) WINDOW w AS ( @@ -439,7 +429,7 @@ WINDOW w AS ( PATTERN ((A | B | C)+ D) DEFINE A AS v % 4 = 1, B AS v % 4 = 2, C AS v % 4 = 3, D AS v % 4 = 0 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev14'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w @@ -450,11 +440,10 @@ WINDOW w AS ( PATTERN ((A | B | C)+ D) DEFINE A AS v % 4 = 1, B AS v % 4 = 2, C AS v % 4 = 3, D AS v % 4 = 0 );'); -DROP VIEW rpr_v; -- Early termination: first ALT branch (A) reaches FIN immediately, -- pruning second branch (A B+) before it can accumulate B repetitions. -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev15 AS SELECT count(*) OVER w FROM generate_series(1, 100) AS s(v) WINDOW w AS ( @@ -463,7 +452,7 @@ WINDOW w AS ( PATTERN ((A | A B)+) DEFINE A AS v = 1, B AS v > 1 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev15'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w @@ -474,10 +463,9 @@ WINDOW w AS ( PATTERN ((A | A B)+) DEFINE A AS v = 1, B AS v > 1 );'); -DROP VIEW rpr_v; -- Nested quantifiers causing state growth -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev16 AS SELECT count(*) OVER w FROM generate_series(1, 1000) AS s(v) WINDOW w AS ( @@ -486,7 +474,7 @@ WINDOW w AS ( PATTERN (((A | B)+)+) DEFINE A AS v % 3 = 1, B AS v % 3 = 2 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev16'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w @@ -497,14 +485,13 @@ WINDOW w AS ( PATTERN (((A | B)+)+) DEFINE A AS v % 3 = 1, B AS v % 3 = 2 );'); -DROP VIEW rpr_v; -- ============================================================ -- Context Statistics Tests (peak, total, pruned + absorbed/skipped) -- ============================================================ -- Context absorption with unbounded quantifier at start -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev17 AS SELECT count(*) OVER w FROM generate_series(1, 50) AS s(v) WINDOW w AS ( @@ -513,7 +500,7 @@ WINDOW w AS ( PATTERN (A+ B) DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev17'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w @@ -524,10 +511,9 @@ WINDOW w AS ( PATTERN (A+ B) DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 );'); -DROP VIEW rpr_v; -- No absorption - bounded quantifier -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev18 AS SELECT count(*) OVER w FROM generate_series(1, 50) AS s(v) WINDOW w AS ( @@ -536,7 +522,7 @@ WINDOW w AS ( PATTERN (A{2,4} B) DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev18'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w @@ -547,10 +533,9 @@ WINDOW w AS ( PATTERN (A{2,4} B) DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 );'); -DROP VIEW rpr_v; -- Contexts skipped by SKIP PAST LAST ROW -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev19 AS SELECT count(*) OVER w FROM generate_series(1, 100) AS s(v) WINDOW w AS ( @@ -559,7 +544,7 @@ WINDOW w AS ( PATTERN (A B C) DEFINE A AS v % 10 = 1, B AS v % 10 = 2, C AS v % 10 = 3 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev19'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w @@ -570,10 +555,9 @@ WINDOW w AS ( PATTERN (A B C) DEFINE A AS v % 10 = 1, B AS v % 10 = 2, C AS v % 10 = 3 );'); -DROP VIEW rpr_v; -- High context absorption - unbounded group -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev20 AS SELECT count(*) OVER w FROM generate_series(1, 100) AS s(v) WINDOW w AS ( @@ -582,7 +566,7 @@ WINDOW w AS ( PATTERN ((A B)+ C) DEFINE A AS v % 3 = 1, B AS v % 3 = 2, C AS v % 3 = 0 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev20'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w @@ -593,16 +577,15 @@ WINDOW w AS ( PATTERN ((A B)+ C) DEFINE A AS v % 3 = 1, B AS v % 3 = 2, C AS v % 3 = 0 );'); -DROP VIEW rpr_v; -- ============================================================ -- Match Length Statistics Tests -- ============================================================ -- Fixed length matches - all same length -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev21 AS SELECT count(*) OVER w -FROM nfa_test +FROM rpr_nfa_test WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING AFTER MATCH SKIP PAST LAST ROW @@ -611,11 +594,11 @@ WINDOW w AS ( A AS cat = 'A', B AS cat = 'B', C AS cat = 'C', D AS cat = 'D', E AS cat = 'E' ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev21'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w -FROM nfa_test +FROM rpr_nfa_test WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING AFTER MATCH SKIP PAST LAST ROW @@ -624,10 +607,9 @@ WINDOW w AS ( A AS cat = ''A'', B AS cat = ''B'', C AS cat = ''C'', D AS cat = ''D'', E AS cat = ''E'' );'); -DROP VIEW rpr_v; -- Variable length matches - min/max/avg differ -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev22 AS SELECT count(*) OVER w FROM generate_series(1, 100) AS s(v) WINDOW w AS ( @@ -636,7 +618,7 @@ WINDOW w AS ( PATTERN (A+ B) DEFINE A AS v % 10 <> 0, B AS v % 10 = 0 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev22'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w @@ -647,10 +629,9 @@ WINDOW w AS ( PATTERN (A+ B) DEFINE A AS v % 10 <> 0, B AS v % 10 = 0 );'); -DROP VIEW rpr_v; -- Very long matches -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev23 AS SELECT count(*) OVER w FROM generate_series(1, 200) AS s(v) WINDOW w AS ( @@ -659,7 +640,7 @@ WINDOW w AS ( PATTERN (A+ B) DEFINE A AS v <= 195, B AS v > 195 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev23'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w @@ -670,10 +651,9 @@ WINDOW w AS ( PATTERN (A+ B) DEFINE A AS v <= 195, B AS v > 195 );'); -DROP VIEW rpr_v; -- Uniform match length with mismatches from gap rows (v%20 = 11..15) -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev24 AS SELECT count(*) OVER w FROM generate_series(1, 100) AS s(v) WINDOW w AS ( @@ -684,7 +664,7 @@ WINDOW w AS ( A AS (v % 20 <> 0) AND (v % 20 <= 10 OR v % 20 > 15), B AS v % 20 = 0 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev24'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w @@ -697,7 +677,6 @@ WINDOW w AS ( A AS (v % 20 <> 0) AND (v % 20 <= 10 OR v % 20 > 15), B AS v % 20 = 0 );'); -DROP VIEW rpr_v; -- ============================================================ -- Mismatch Length Statistics Tests @@ -705,7 +684,7 @@ DROP VIEW rpr_v; -- Pattern with complete match every cycle: 0 mismatched -- A(1,2,3) B(4,5) C(6) repeats perfectly; X rows are pruned, not mismatched -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev25 AS SELECT count(*) OVER w FROM ( SELECT v, @@ -721,7 +700,7 @@ WINDOW w AS ( PATTERN (A+ B+ C) DEFINE A AS cat = 'A', B AS cat = 'B', C AS cat = 'C' ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev25'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w @@ -739,10 +718,9 @@ WINDOW w AS ( PATTERN (A+ B+ C) DEFINE A AS cat = ''A'', B AS cat = ''B'', C AS cat = ''C'' );'); -DROP VIEW rpr_v; -- Long partial matches that fail -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev26 AS SELECT count(*) OVER w FROM ( SELECT i AS v, @@ -763,7 +741,7 @@ WINDOW w AS ( PATTERN (A+ B+ C) DEFINE A AS cat = 'A', B AS cat = 'B', C AS cat = 'C' ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev26'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w @@ -786,14 +764,13 @@ WINDOW w AS ( PATTERN (A+ B+ C) DEFINE A AS cat = ''A'', B AS cat = ''B'', C AS cat = ''C'' );'); -DROP VIEW rpr_v; -- ============================================================ -- JSON Format Tests -- ============================================================ -- JSON format output with all statistics -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev27 AS SELECT count(*) OVER w FROM generate_series(1, 50) AS s(v) WINDOW w AS ( @@ -802,7 +779,7 @@ WINDOW w AS ( PATTERN (A+ B+) DEFINE A AS v % 3 = 1, B AS v % 3 = 2 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev27'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF, FORMAT JSON) SELECT count(*) OVER w @@ -813,10 +790,9 @@ WINDOW w AS ( PATTERN (A+ B+) DEFINE A AS v % 3 = 1, B AS v % 3 = 2 )'); -DROP VIEW rpr_v; -- JSON format with match length statistics -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev28 AS SELECT count(*) OVER w FROM generate_series(1, 100) AS s(v) WINDOW w AS ( @@ -825,7 +801,7 @@ WINDOW w AS ( PATTERN (A+ B) DEFINE A AS v % 10 <> 0, B AS v % 10 = 0 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev28'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF, FORMAT JSON) SELECT count(*) OVER w @@ -836,11 +812,10 @@ WINDOW w AS ( PATTERN (A+ B) DEFINE A AS v % 10 <> 0, B AS v % 10 = 0 )'); -DROP VIEW rpr_v; -- JSON format with mismatch statistics -- Pattern A B C expects 1,2,3 but gets 1,2,4 twice causing mismatches -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev29 AS SELECT count(*) OVER w FROM (VALUES (1),(2),(4), (1),(2),(4), (1),(2),(3)) AS t(v) WINDOW w AS ( @@ -849,7 +824,7 @@ WINDOW w AS ( PATTERN (A B C) DEFINE A AS v = 1, B AS v = 2, C AS v = 3 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev29'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF, FORMAT JSON) SELECT count(*) OVER w @@ -860,11 +835,10 @@ WINDOW w AS ( PATTERN (A B C) DEFINE A AS v = 1, B AS v = 2, C AS v = 3 )'); -DROP VIEW rpr_v; -- JSON format with skipped context statistics -- Alternation pattern with SKIP PAST LAST ROW causes many contexts to be skipped -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev30 AS SELECT count(*) OVER w FROM generate_series(1, 100) AS s(v) WINDOW w AS ( @@ -873,7 +847,7 @@ WINDOW w AS ( PATTERN ((A | B) (A | B) (A | B) (A | B) (A | B) (A | B) (A | B) (A | B)) DEFINE A AS v % 2 = 1, B AS v % 2 = 0 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev30'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF, FORMAT JSON) SELECT count(*) OVER w @@ -884,14 +858,13 @@ WINDOW w AS ( PATTERN ((A | B) (A | B) (A | B) (A | B) (A | B) (A | B) (A | B) (A | B)) DEFINE A AS v % 2 = 1, B AS v % 2 = 0 )'); -DROP VIEW rpr_v; -- ============================================================ -- XML Format Tests -- ============================================================ -- XML format output -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev31 AS SELECT count(*) OVER w FROM generate_series(1, 30) AS s(v) WINDOW w AS ( @@ -900,7 +873,7 @@ WINDOW w AS ( PATTERN (A B) DEFINE A AS v % 2 = 1, B AS v % 2 = 0 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev31'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF, FORMAT XML) SELECT count(*) OVER w @@ -911,14 +884,13 @@ WINDOW w AS ( PATTERN (A B) DEFINE A AS v % 2 = 1, B AS v % 2 = 0 )'); -DROP VIEW rpr_v; -- ============================================================ -- Multiple Partitions Tests -- ============================================================ -- Statistics across multiple partitions -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev32 AS SELECT count(*) OVER w FROM ( SELECT p, v @@ -932,7 +904,7 @@ WINDOW w AS ( PATTERN (A+ B) DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev32'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w @@ -948,10 +920,9 @@ WINDOW w AS ( PATTERN (A+ B) DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 );'); -DROP VIEW rpr_v; -- Different pattern behavior per partition -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev33 AS SELECT count(*) OVER w FROM ( SELECT @@ -966,7 +937,7 @@ WINDOW w AS ( PATTERN (A+ B) DEFINE A AS val < 5, B AS val >= 5 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev33'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w @@ -983,14 +954,13 @@ WINDOW w AS ( PATTERN (A+ B) DEFINE A AS val < 5, B AS val >= 5 );'); -DROP VIEW rpr_v; -- ============================================================ -- Edge Cases -- ============================================================ -- Empty result set -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev34 AS SELECT count(*) OVER w FROM generate_series(1, 0) AS s(v) WINDOW w AS ( @@ -999,7 +969,7 @@ WINDOW w AS ( PATTERN (A B) DEFINE A AS v = 1, B AS v = 2 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev34'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w @@ -1010,10 +980,9 @@ WINDOW w AS ( PATTERN (A B) DEFINE A AS v = 1, B AS v = 2 );'); -DROP VIEW rpr_v; -- Single row -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev35 AS SELECT count(*) OVER w FROM generate_series(1, 1) AS s(v) WINDOW w AS ( @@ -1022,7 +991,7 @@ WINDOW w AS ( PATTERN (A) DEFINE A AS TRUE ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev35'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w @@ -1033,10 +1002,9 @@ WINDOW w AS ( PATTERN (A) DEFINE A AS TRUE );'); -DROP VIEW rpr_v; -- Pattern longer than data -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev36 AS SELECT count(*) OVER w FROM generate_series(1, 5) AS s(v) WINDOW w AS ( @@ -1047,7 +1015,7 @@ WINDOW w AS ( A AS v = 1, B AS v = 2, C AS v = 3, D AS v = 4, E AS v = 5, F AS v = 6, G AS v = 7, H AS v = 8, I AS v = 9, J AS v = 10 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev36'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w @@ -1060,10 +1028,9 @@ WINDOW w AS ( A AS v = 1, B AS v = 2, C AS v = 3, D AS v = 4, E AS v = 5, F AS v = 6, G AS v = 7, H AS v = 8, I AS v = 9, J AS v = 10 );'); -DROP VIEW rpr_v; -- All rows match as single match -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev37 AS SELECT count(*) OVER w FROM generate_series(1, 50) AS s(v) WINDOW w AS ( @@ -1072,7 +1039,7 @@ WINDOW w AS ( PATTERN (A+) DEFINE A AS TRUE ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev37'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w @@ -1083,14 +1050,13 @@ WINDOW w AS ( PATTERN (A+) DEFINE A AS TRUE );'); -DROP VIEW rpr_v; -- ============================================================ -- Complex Pattern Tests -- ============================================================ -- Nested groups -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev38 AS SELECT count(*) OVER w FROM generate_series(1, 60) AS s(v) WINDOW w AS ( @@ -1099,7 +1065,7 @@ WINDOW w AS ( PATTERN (((A B) C)+) DEFINE A AS v % 3 = 1, B AS v % 3 = 2, C AS v % 3 = 0 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev38'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w @@ -1110,12 +1076,11 @@ WINDOW w AS ( PATTERN (((A B) C)+) DEFINE A AS v % 3 = 1, B AS v % 3 = 2, C AS v % 3 = 0 );'); -DROP VIEW rpr_v; -- Multiple alternations -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev39 AS SELECT count(*) OVER w -FROM nfa_test +FROM rpr_nfa_test WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING AFTER MATCH SKIP PAST LAST ROW @@ -1124,11 +1089,11 @@ WINDOW w AS ( A AS cat = 'A', B AS cat = 'B', C AS cat = 'C', D AS cat = 'D', E AS cat = 'E' ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev39'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w -FROM nfa_test +FROM rpr_nfa_test WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING AFTER MATCH SKIP PAST LAST ROW @@ -1137,10 +1102,9 @@ WINDOW w AS ( A AS cat = ''A'', B AS cat = ''B'', C AS cat = ''C'', D AS cat = ''D'', E AS cat = ''E'' );'); -DROP VIEW rpr_v; -- Optional elements -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev40 AS SELECT count(*) OVER w FROM generate_series(1, 50) AS s(v) WINDOW w AS ( @@ -1149,7 +1113,7 @@ WINDOW w AS ( PATTERN (A B? C) DEFINE A AS v % 4 = 1, B AS v % 4 = 2, C AS v % 4 = 3 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev40'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w @@ -1160,10 +1124,9 @@ WINDOW w AS ( PATTERN (A B? C) DEFINE A AS v % 4 = 1, B AS v % 4 = 2, C AS v % 4 = 3 );'); -DROP VIEW rpr_v; -- Bounded quantifiers -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev41 AS SELECT count(*) OVER w FROM generate_series(1, 100) AS s(v) WINDOW w AS ( @@ -1172,7 +1135,7 @@ WINDOW w AS ( PATTERN (A{2,5} B) DEFINE A AS v % 10 <> 0, B AS v % 10 = 0 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev41'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w @@ -1183,10 +1146,9 @@ WINDOW w AS ( PATTERN (A{2,5} B) DEFINE A AS v % 10 <> 0, B AS v % 10 = 0 );'); -DROP VIEW rpr_v; -- Star quantifier -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev42 AS SELECT count(*) OVER w FROM generate_series(1, 50) AS s(v) WINDOW w AS ( @@ -1195,7 +1157,7 @@ WINDOW w AS ( PATTERN (A B* C) DEFINE A AS v % 10 = 1, B AS v % 10 IN (2,3,4,5,6,7,8), C AS v % 10 = 9 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev42'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w @@ -1206,60 +1168,57 @@ WINDOW w AS ( PATTERN (A B* C) DEFINE A AS v % 10 = 1, B AS v % 10 IN (2,3,4,5,6,7,8), C AS v % 10 = 9 );'); -DROP VIEW rpr_v; -- ============================================================ -- Real-world Pattern Examples -- ============================================================ -- Stock price pattern - V-shape (down then up) -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev43 AS SELECT count(*) OVER w -FROM nfa_complex +FROM rpr_nfa_complex WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING AFTER MATCH SKIP PAST LAST ROW PATTERN (D+ U+) DEFINE D AS trend = 'D', U AS trend = 'U' ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev43'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w -FROM nfa_complex +FROM rpr_nfa_complex WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING AFTER MATCH SKIP PAST LAST ROW PATTERN (D+ U+) DEFINE D AS trend = ''D'', U AS trend = ''U'' );'); -DROP VIEW rpr_v; -- Stock price pattern - peak (up, stable, down) -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev44 AS SELECT count(*) OVER w -FROM nfa_complex +FROM rpr_nfa_complex WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING AFTER MATCH SKIP PAST LAST ROW PATTERN (U+ S* D+) DEFINE U AS trend = 'U', S AS trend = 'S', D AS trend = 'D' ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev44'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w -FROM nfa_complex +FROM rpr_nfa_complex WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING AFTER MATCH SKIP PAST LAST ROW PATTERN (U+ S* D+) DEFINE U AS trend = ''U'', S AS trend = ''S'', D AS trend = ''D'' );'); -DROP VIEW rpr_v; -- Consecutive increasing values (using PREV) -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev45 AS SELECT count(*) OVER w FROM generate_series(1, 50) AS s(v) WINDOW w AS ( @@ -1268,7 +1227,7 @@ WINDOW w AS ( PATTERN (A{3,}) DEFINE A AS v > PREV(v) OR PREV(v) IS NULL ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev45'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w @@ -1279,14 +1238,13 @@ WINDOW w AS ( PATTERN (A{3,}) DEFINE A AS v > PREV(v) OR PREV(v) IS NULL );'); -DROP VIEW rpr_v; -- ============================================================ -- Performance-oriented Tests -- ============================================================ -- Large dataset with simple pattern -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev46 AS SELECT count(*) OVER w FROM generate_series(1, 1000) AS s(v) WINDOW w AS ( @@ -1295,7 +1253,7 @@ WINDOW w AS ( PATTERN (A B) DEFINE A AS v % 2 = 1, B AS v % 2 = 0 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev46'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w @@ -1306,10 +1264,9 @@ WINDOW w AS ( PATTERN (A B) DEFINE A AS v % 2 = 1, B AS v % 2 = 0 );'); -DROP VIEW rpr_v; -- Large dataset with absorption -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev47 AS SELECT count(*) OVER w FROM generate_series(1, 1000) AS s(v) WINDOW w AS ( @@ -1318,7 +1275,7 @@ WINDOW w AS ( PATTERN (A+ B) DEFINE A AS v % 100 <> 0, B AS v % 100 = 0 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev47'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w @@ -1329,10 +1286,9 @@ WINDOW w AS ( PATTERN (A+ B) DEFINE A AS v % 100 <> 0, B AS v % 100 = 0 );'); -DROP VIEW rpr_v; -- High state merge ratio -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev48 AS SELECT count(*) OVER w FROM generate_series(1, 500) AS s(v) WINDOW w AS ( @@ -1341,7 +1297,7 @@ WINDOW w AS ( PATTERN ((A | B)+ C) DEFINE A AS v % 3 = 1, B AS v % 3 = 2, C AS v % 3 = 0 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev48'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w @@ -1352,14 +1308,13 @@ WINDOW w AS ( PATTERN ((A | B)+ C) DEFINE A AS v % 3 = 1, B AS v % 3 = 2, C AS v % 3 = 0 );'); -DROP VIEW rpr_v; -- ============================================================ -- INITIAL vs no INITIAL comparison -- ============================================================ -- With INITIAL keyword -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev49 AS SELECT count(*) OVER w FROM generate_series(1, 50) AS s(v) WINDOW w AS ( @@ -1369,7 +1324,7 @@ WINDOW w AS ( PATTERN (A+ B) DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev49'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w @@ -1381,10 +1336,9 @@ WINDOW w AS ( PATTERN (A+ B) DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 );'); -DROP VIEW rpr_v; -- Without INITIAL keyword (same behavior currently) -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev50 AS SELECT count(*) OVER w FROM generate_series(1, 50) AS s(v) WINDOW w AS ( @@ -1393,7 +1347,7 @@ WINDOW w AS ( PATTERN (A+ B) DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev50'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w @@ -1404,14 +1358,13 @@ WINDOW w AS ( PATTERN (A+ B) DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 );'); -DROP VIEW rpr_v; -- ============================================================ -- Quantifier Variations -- ============================================================ -- Plus quantifier -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev51 AS SELECT count(*) OVER w FROM generate_series(1, 40) AS s(v) WINDOW w AS ( @@ -1420,7 +1373,7 @@ WINDOW w AS ( PATTERN (A+) DEFINE A AS v % 4 <> 0 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev51'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w @@ -1431,10 +1384,9 @@ WINDOW w AS ( PATTERN (A+) DEFINE A AS v % 4 <> 0 );'); -DROP VIEW rpr_v; -- Star quantifier (zero or more) -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev52 AS SELECT count(*) OVER w FROM generate_series(1, 40) AS s(v) WINDOW w AS ( @@ -1443,7 +1395,7 @@ WINDOW w AS ( PATTERN (A* B) DEFINE A AS v % 4 IN (1, 2), B AS v % 4 = 3 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev52'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w @@ -1454,10 +1406,9 @@ WINDOW w AS ( PATTERN (A* B) DEFINE A AS v % 4 IN (1, 2), B AS v % 4 = 3 );'); -DROP VIEW rpr_v; -- Question mark (zero or one) -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev53 AS SELECT count(*) OVER w FROM generate_series(1, 40) AS s(v) WINDOW w AS ( @@ -1466,7 +1417,7 @@ WINDOW w AS ( PATTERN (A? B C) DEFINE A AS v % 4 = 1, B AS v % 4 = 2, C AS v % 4 = 3 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev53'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w @@ -1477,10 +1428,9 @@ WINDOW w AS ( PATTERN (A? B C) DEFINE A AS v % 4 = 1, B AS v % 4 = 2, C AS v % 4 = 3 );'); -DROP VIEW rpr_v; -- Exact count {n} -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev54 AS SELECT count(*) OVER w FROM generate_series(1, 50) AS s(v) WINDOW w AS ( @@ -1489,7 +1439,7 @@ WINDOW w AS ( PATTERN (A{3} B) DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev54'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w @@ -1500,10 +1450,9 @@ WINDOW w AS ( PATTERN (A{3} B) DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 );'); -DROP VIEW rpr_v; -- Range {n,m} -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev55 AS SELECT count(*) OVER w FROM generate_series(1, 50) AS s(v) WINDOW w AS ( @@ -1512,7 +1461,7 @@ WINDOW w AS ( PATTERN (A{2,4} B) DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev55'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w @@ -1523,10 +1472,9 @@ WINDOW w AS ( PATTERN (A{2,4} B) DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 );'); -DROP VIEW rpr_v; -- At least {n,} -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev56 AS SELECT count(*) OVER w FROM generate_series(1, 50) AS s(v) WINDOW w AS ( @@ -1535,7 +1483,7 @@ WINDOW w AS ( PATTERN (A{3,} B) DEFINE A AS v % 10 <> 0, B AS v % 10 = 0 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev56'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w @@ -1546,7 +1494,6 @@ WINDOW w AS ( PATTERN (A{3,} B) DEFINE A AS v % 10 <> 0, B AS v % 10 = 0 );'); -DROP VIEW rpr_v; -- ============================================================ -- Regression Tests for Statistics Accuracy @@ -1554,7 +1501,7 @@ DROP VIEW rpr_v; -- Verify state count accuracy -- Pattern A+ B with 20 rows should show predictable state behavior -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev57 AS SELECT count(*) OVER w FROM generate_series(1, 20) AS s(v) WINDOW w AS ( @@ -1563,7 +1510,7 @@ WINDOW w AS ( PATTERN (A+ B) DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev57'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w @@ -1574,10 +1521,9 @@ WINDOW w AS ( PATTERN (A+ B) DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 );'); -DROP VIEW rpr_v; -- Verify context count with known absorption -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev58 AS SELECT count(*) OVER w FROM generate_series(1, 30) AS s(v) WINDOW w AS ( @@ -1586,7 +1532,7 @@ WINDOW w AS ( PATTERN (A+ B C) DEFINE A AS v % 10 IN (1,2,3,4,5,6,7), B AS v % 10 = 8, C AS v % 10 = 9 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev58'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w @@ -1597,10 +1543,9 @@ WINDOW w AS ( PATTERN (A+ B C) DEFINE A AS v % 10 IN (1,2,3,4,5,6,7), B AS v % 10 = 8, C AS v % 10 = 9 );'); -DROP VIEW rpr_v; -- Verify match length with fixed-length pattern -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev59 AS SELECT count(*) OVER w FROM generate_series(1, 30) AS s(v) WINDOW w AS ( @@ -1609,7 +1554,7 @@ WINDOW w AS ( PATTERN (A B C) DEFINE A AS v % 3 = 1, B AS v % 3 = 2, C AS v % 3 = 0 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev59'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w @@ -1620,39 +1565,37 @@ WINDOW w AS ( PATTERN (A B C) DEFINE A AS v % 3 = 1, B AS v % 3 = 2, C AS v % 3 = 0 );'); -DROP VIEW rpr_v; -- ============================================================ -- Alternation Pattern Tests -- ============================================================ -- Simple alternation -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev60 AS SELECT count(*) OVER w -FROM nfa_test +FROM rpr_nfa_test WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING AFTER MATCH SKIP PAST LAST ROW PATTERN ((A | B) C) DEFINE A AS cat = 'A', B AS cat = 'B', C AS cat = 'C' ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev60'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w -FROM nfa_test +FROM rpr_nfa_test WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING AFTER MATCH SKIP PAST LAST ROW PATTERN ((A | B) C) DEFINE A AS cat = ''A'', B AS cat = ''B'', C AS cat = ''C'' );'); -DROP VIEW rpr_v; -- Multiple items in alternation -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev61 AS SELECT count(*) OVER w -FROM nfa_test +FROM rpr_nfa_test WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING AFTER MATCH SKIP PAST LAST ROW @@ -1661,11 +1604,11 @@ WINDOW w AS ( A AS cat = 'A', B AS cat = 'B', C AS cat = 'C', D AS cat = 'D', E AS cat = 'E' ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev61'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w -FROM nfa_test +FROM rpr_nfa_test WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING AFTER MATCH SKIP PAST LAST ROW @@ -1674,10 +1617,9 @@ WINDOW w AS ( A AS cat = ''A'', B AS cat = ''B'', C AS cat = ''C'', D AS cat = ''D'', E AS cat = ''E'' );'); -DROP VIEW rpr_v; -- Alternation with quantifiers -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev62 AS SELECT count(*) OVER w FROM generate_series(1, 50) AS s(v) WINDOW w AS ( @@ -1686,7 +1628,7 @@ WINDOW w AS ( PATTERN ((A | B)+ C) DEFINE A AS v % 3 = 1, B AS v % 3 = 2, C AS v % 3 = 0 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev62'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w @@ -1697,10 +1639,9 @@ WINDOW w AS ( PATTERN ((A | B)+ C) DEFINE A AS v % 3 = 1, B AS v % 3 = 2, C AS v % 3 = 0 );'); -DROP VIEW rpr_v; -- Multiple alternatives (4+) -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev63 AS SELECT count(*) OVER w FROM generate_series(1, 100) AS s(v) WINDOW w AS ( @@ -1708,7 +1649,7 @@ WINDOW w AS ( PATTERN (A | B | C | D | E) DEFINE A AS v % 5 = 0, B AS v % 5 = 1, C AS v % 5 = 2, D AS v % 5 = 3, E AS v % 5 = 4 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev63'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w @@ -1718,10 +1659,9 @@ WINDOW w AS ( PATTERN (A | B | C | D | E) DEFINE A AS v % 5 = 0, B AS v % 5 = 1, C AS v % 5 = 2, D AS v % 5 = 3, E AS v % 5 = 4 );'); -DROP VIEW rpr_v; -- Alternation at start -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev64 AS SELECT count(*) OVER w FROM generate_series(1, 60) AS s(v) WINDOW w AS ( @@ -1729,7 +1669,7 @@ WINDOW w AS ( PATTERN ((A | B) C D) DEFINE A AS v % 4 = 0, B AS v % 4 = 1, C AS v % 4 = 2, D AS v % 4 = 3 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev64'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w @@ -1739,10 +1679,9 @@ WINDOW w AS ( PATTERN ((A | B) C D) DEFINE A AS v % 4 = 0, B AS v % 4 = 1, C AS v % 4 = 2, D AS v % 4 = 3 );'); -DROP VIEW rpr_v; -- Multiple sequential alternations -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev65 AS SELECT count(*) OVER w FROM generate_series(1, 100) AS s(v) WINDOW w AS ( @@ -1750,7 +1689,7 @@ WINDOW w AS ( PATTERN ((A | B) C (D | E) F) DEFINE A AS v % 6 = 0, B AS v % 6 = 1, C AS v % 6 = 2, D AS v % 6 = 3, E AS v % 6 = 4, F AS v % 6 = 5 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev65'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w @@ -1760,10 +1699,9 @@ WINDOW w AS ( PATTERN ((A | B) C (D | E) F) DEFINE A AS v % 6 = 0, B AS v % 6 = 1, C AS v % 6 = 2, D AS v % 6 = 3, E AS v % 6 = 4, F AS v % 6 = 5 );'); -DROP VIEW rpr_v; -- Quantified alternatives -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev66 AS SELECT count(*) OVER w FROM generate_series(1, 60) AS s(v) WINDOW w AS ( @@ -1771,7 +1709,7 @@ WINDOW w AS ( PATTERN ((A+ | B+) C) DEFINE A AS v % 3 = 0, B AS v % 3 = 1, C AS v % 3 = 2 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev66'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w @@ -1781,10 +1719,9 @@ WINDOW w AS ( PATTERN ((A+ | B+) C) DEFINE A AS v % 3 = 0, B AS v % 3 = 1, C AS v % 3 = 2 );'); -DROP VIEW rpr_v; -- Alternation at end -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev67 AS SELECT count(*) OVER w FROM generate_series(1, 60) AS s(v) WINDOW w AS ( @@ -1792,7 +1729,7 @@ WINDOW w AS ( PATTERN (A B (C | D)) DEFINE A AS v % 4 = 0, B AS v % 4 = 1, C AS v % 4 = 2, D AS v % 4 = 3 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev67'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w @@ -1802,11 +1739,10 @@ WINDOW w AS ( PATTERN (A B (C | D)) DEFINE A AS v % 4 = 0, B AS v % 4 = 1, C AS v % 4 = 2, D AS v % 4 = 3 );'); -DROP VIEW rpr_v; -- Nested ALT at start of branch inside outer ALT -- Pattern: (A ((B | C) D | E)) - preceding VAR + inner ALT as first branch element -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev68 AS SELECT count(*) OVER w FROM generate_series(1, 20) AS s(v) WINDOW w AS ( @@ -1814,7 +1750,7 @@ WINDOW w AS ( PATTERN (A ((B | C) D | E)) DEFINE A AS v % 5 = 0, B AS v % 5 = 1, C AS v % 5 = 2, D AS v % 5 = 3, E AS v % 5 = 4 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev68'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w @@ -1824,11 +1760,10 @@ WINDOW w AS ( PATTERN (A ((B | C) D | E)) DEFINE A AS v % 5 = 0, B AS v % 5 = 1, C AS v % 5 = 2, D AS v % 5 = 3, E AS v % 5 = 4 );'); -DROP VIEW rpr_v; -- Nested ALT at end of branch inside outer ALT -- Pattern: (C (A | B) | D) - inner ALT is last element in outer branch -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev69 AS SELECT count(*) OVER w FROM generate_series(1, 20) AS s(v) WINDOW w AS ( @@ -1836,7 +1771,7 @@ WINDOW w AS ( PATTERN (C (A | B) | D) DEFINE A AS v % 4 = 0, B AS v % 4 = 1, C AS v % 4 = 2, D AS v % 4 = 3 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev69'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w @@ -1846,14 +1781,13 @@ WINDOW w AS ( PATTERN (C (A | B) | D) DEFINE A AS v % 4 = 0, B AS v % 4 = 1, C AS v % 4 = 2, D AS v % 4 = 3 );'); -DROP VIEW rpr_v; -- ============================================================ -- Group Pattern Tests -- ============================================================ -- Simple group -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev70 AS SELECT count(*) OVER w FROM generate_series(1, 40) AS s(v) WINDOW w AS ( @@ -1862,7 +1796,7 @@ WINDOW w AS ( PATTERN ((A B)+) DEFINE A AS v % 2 = 1, B AS v % 2 = 0 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev70'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w @@ -1873,10 +1807,9 @@ WINDOW w AS ( PATTERN ((A B)+) DEFINE A AS v % 2 = 1, B AS v % 2 = 0 );'); -DROP VIEW rpr_v; -- Group with bounded quantifier -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev71 AS SELECT count(*) OVER w FROM generate_series(1, 40) AS s(v) WINDOW w AS ( @@ -1885,7 +1818,7 @@ WINDOW w AS ( PATTERN ((A B){2,4}) DEFINE A AS v % 2 = 1, B AS v % 2 = 0 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev71'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w @@ -1896,10 +1829,9 @@ WINDOW w AS ( PATTERN ((A B){2,4}) DEFINE A AS v % 2 = 1, B AS v % 2 = 0 );'); -DROP VIEW rpr_v; -- Nested groups -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev72 AS SELECT count(*) OVER w FROM generate_series(1, 60) AS s(v) WINDOW w AS ( @@ -1908,7 +1840,7 @@ WINDOW w AS ( PATTERN (((A B){2})+) DEFINE A AS v % 2 = 1, B AS v % 2 = 0 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev72'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w @@ -1919,10 +1851,9 @@ WINDOW w AS ( PATTERN (((A B){2})+) DEFINE A AS v % 2 = 1, B AS v % 2 = 0 );'); -DROP VIEW rpr_v; -- Deep nesting (3+ levels) -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev73 AS SELECT count(*) OVER w FROM generate_series(1, 40) AS s(v) WINDOW w AS ( @@ -1930,7 +1861,7 @@ WINDOW w AS ( PATTERN ((((A | B)+)+)+) DEFINE A AS v % 2 = 0, B AS v % 2 = 1 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev73'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w @@ -1940,10 +1871,9 @@ WINDOW w AS ( PATTERN ((((A | B)+)+)+) DEFINE A AS v % 2 = 0, B AS v % 2 = 1 );'); -DROP VIEW rpr_v; -- Bounded quantifier on alternation -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev74 AS SELECT count(*) OVER w FROM generate_series(1, 60) AS s(v) WINDOW w AS ( @@ -1951,7 +1881,7 @@ WINDOW w AS ( PATTERN ((A | B){2,3} C) DEFINE A AS v % 3 = 0, B AS v % 3 = 1, C AS v % 3 = 2 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev74'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w @@ -1961,10 +1891,9 @@ WINDOW w AS ( PATTERN ((A | B){2,3} C) DEFINE A AS v % 3 = 0, B AS v % 3 = 1, C AS v % 3 = 2 );'); -DROP VIEW rpr_v; -- Nested groups with quantifiers -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev75 AS SELECT count(*) OVER w FROM generate_series(1, 60) AS s(v) WINDOW w AS ( @@ -1972,7 +1901,7 @@ WINDOW w AS ( PATTERN (((A B)+ C)*) DEFINE A AS v % 3 = 0, B AS v % 3 = 1, C AS v % 3 = 2 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev75'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w @@ -1982,10 +1911,9 @@ WINDOW w AS ( PATTERN (((A B)+ C)*) DEFINE A AS v % 3 = 0, B AS v % 3 = 1, C AS v % 3 = 2 );'); -DROP VIEW rpr_v; -- Partial nested quantification -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev76 AS SELECT count(*) OVER w FROM generate_series(1, 60) AS s(v) WINDOW w AS ( @@ -1993,7 +1921,7 @@ WINDOW w AS ( PATTERN ((A (B C)+)*) DEFINE A AS v % 3 = 0, B AS v % 3 = 1, C AS v % 3 = 2 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev76'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w @@ -2003,14 +1931,13 @@ WINDOW w AS ( PATTERN ((A (B C)+)*) DEFINE A AS v % 3 = 0, B AS v % 3 = 1, C AS v % 3 = 2 );'); -DROP VIEW rpr_v; -- ============================================================ -- Window Function Combinations -- ============================================================ -- count(*) with pattern -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev77 AS SELECT count(*) OVER w FROM generate_series(1, 30) AS s(v) WINDOW w AS ( @@ -2019,7 +1946,7 @@ WINDOW w AS ( PATTERN (A+ B) DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev77'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w @@ -2030,10 +1957,9 @@ WINDOW w AS ( PATTERN (A+ B) DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 );'); -DROP VIEW rpr_v; -- first_value with pattern -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev78 AS SELECT first_value(v) OVER w FROM generate_series(1, 30) AS s(v) WINDOW w AS ( @@ -2042,7 +1968,7 @@ WINDOW w AS ( PATTERN (A+ B) DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev78'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT first_value(v) OVER w @@ -2053,10 +1979,9 @@ WINDOW w AS ( PATTERN (A+ B) DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 );'); -DROP VIEW rpr_v; -- last_value with pattern -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev79 AS SELECT last_value(v) OVER w FROM generate_series(1, 30) AS s(v) WINDOW w AS ( @@ -2065,7 +1990,7 @@ WINDOW w AS ( PATTERN (A+ B) DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev79'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT last_value(v) OVER w @@ -2076,10 +2001,9 @@ WINDOW w AS ( PATTERN (A+ B) DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 );'); -DROP VIEW rpr_v; -- Multiple window functions -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev80 AS SELECT count(*) OVER w, first_value(v) OVER w, @@ -2091,7 +2015,7 @@ WINDOW w AS ( PATTERN (A+ B) DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev80'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT @@ -2105,14 +2029,13 @@ WINDOW w AS ( PATTERN (A+ B) DEFINE A AS v % 5 <> 0, B AS v % 5 = 0 );'); -DROP VIEW rpr_v; -- ============================================================ -- DEFINE Expression Variations -- ============================================================ -- Complex boolean expressions -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev81 AS SELECT count(*) OVER w FROM generate_series(1, 50) AS s(v) WINDOW w AS ( @@ -2123,7 +2046,7 @@ WINDOW w AS ( A AS (v % 5 <> 0) AND (v % 3 <> 0), B AS (v % 5 = 0) OR (v % 3 = 0) ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev81'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w @@ -2136,10 +2059,9 @@ WINDOW w AS ( A AS (v % 5 <> 0) AND (v % 3 <> 0), B AS (v % 5 = 0) OR (v % 3 = 0) );'); -DROP VIEW rpr_v; -- Using PREV function -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev82 AS SELECT count(*) OVER w FROM generate_series(1, 30) AS s(v) WINDOW w AS ( @@ -2151,7 +2073,7 @@ WINDOW w AS ( U AS v > PREV(v), D AS v < PREV(v) ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev82'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w @@ -2165,10 +2087,9 @@ WINDOW w AS ( U AS v > PREV(v), D AS v < PREV(v) );'); -DROP VIEW rpr_v; -- Using NULL comparisons -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev83 AS SELECT count(*) OVER w FROM ( SELECT CASE WHEN v % 5 = 0 THEN NULL ELSE v END AS v @@ -2180,7 +2101,7 @@ WINDOW w AS ( PATTERN (A+ B) DEFINE A AS v IS NOT NULL, B AS v IS NULL ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev83'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w @@ -2194,14 +2115,13 @@ WINDOW w AS ( PATTERN (A+ B) DEFINE A AS v IS NOT NULL, B AS v IS NULL );'); -DROP VIEW rpr_v; -- ============================================================ -- Large Scale Statistics Verification -- ============================================================ -- 500 rows - verify statistics scale correctly -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev84 AS SELECT count(*) OVER w FROM generate_series(1, 500) AS s(v) WINDOW w AS ( @@ -2210,7 +2130,7 @@ WINDOW w AS ( PATTERN (A+ B C) DEFINE A AS v % 10 < 7, B AS v % 10 = 7, C AS v % 10 = 8 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev84'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w @@ -2221,10 +2141,9 @@ WINDOW w AS ( PATTERN (A+ B C) DEFINE A AS v % 10 < 7, B AS v % 10 = 7, C AS v % 10 = 8 );'); -DROP VIEW rpr_v; -- High match count scenario -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev85 AS SELECT count(*) OVER w FROM generate_series(1, 500) AS s(v) WINDOW w AS ( @@ -2233,7 +2152,7 @@ WINDOW w AS ( PATTERN (A B) DEFINE A AS v % 2 = 1, B AS v % 2 = 0 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev85'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w @@ -2244,10 +2163,9 @@ WINDOW w AS ( PATTERN (A B) DEFINE A AS v % 2 = 1, B AS v % 2 = 0 );'); -DROP VIEW rpr_v; -- High skip count scenario -CREATE TEMP VIEW rpr_v AS +CREATE VIEW rpr_ev86 AS SELECT count(*) OVER w FROM generate_series(1, 500) AS s(v) WINDOW w AS ( @@ -2261,7 +2179,7 @@ WINDOW w AS ( D AS v % 100 = 4, E AS v % 100 = 5 ); -SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_v'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev86'), E'\n')) AS line WHERE line ~ 'PATTERN'; SELECT rpr_explain_filter(' EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w @@ -2277,8 +2195,4 @@ WINDOW w AS ( D AS v % 100 = 4, E AS v % 100 = 5 );'); -DROP VIEW rpr_v; --- Cleanup -DROP TABLE nfa_test; -DROP TABLE nfa_complex; -- 2.50.1 (Apple Git-155)