nocfbot-0004-keep-rpr-test-objects-for-pg_upgrade-pg_dump-testing.txt
text/plain
Filename: nocfbot-0004-keep-rpr-test-objects-for-pg_upgrade-pg_dump-testing.txt
Type: text/plain
Part: 3
Message:
Re: Row pattern recognition
From f9c80e37bbc493737ed04d237abdd4115d5a412e Mon Sep 17 00:00:00 2001
From: Henson Choi <assam258@gmail.com>
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 (
</explain>
(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)