Implementing Row Pattern Recognition
summary
Implements SQL:2016 Row Pattern Recognition inside PostgreSQL’s window clause (R020) with a usable subset: RPR in WINDOW clause (R020) supports DEFINE variables, pattern operators +/, and PREVIOUS/NEXT functions implemented while MEASURES/SUBSET and subqueries/aggregates in DEFINE* remain unimplemented. Patch touches parser, planner, executor, tests and docs (~3,000 lines) and targets PostgreSQL 18; engine maps pattern variables to single-letter tokens (current limit ~26), offers AFTER MATCH SKIP options, needs memory optimizations, and demo finds consecutive extreme hot days successfully.
AI-written summary · about
chapters (15)
- 0:00Introduction and speaker background
- 1:20What is row pattern recognition
- 4:31SQL standard and implementations
- 5:34Detecting time series patterns
- 8:40Finding consecutive events
- 11:17Two types of RPR in standard
- 13:43Syntax of RPR in window clause
- 17:23Define and pattern clauses
- 20:50Regular expressions in patterns
- 21:15Aggregates in target list
- 24:00Current RPR patches overview
- 28:51Implementation details and future plans
- 30:31Demonstration of extreme hot days
- 35:14References and conclusion
- 36:29Q&A session
related talks
38mOracle SQL Pattern Matching Made Easy
01h 06mPostgres Window Magic
24mPostgreSQL Design Patterns
45mGraph databases, PostgreSQL and SQL/PGQ
52mBoosting Typical Query Patterns: PostgreSQL 18's Performance Enhancements
49mSELECT 'amazing features' FROM "postgresql"
46mT-SQL Window Function Performance
30mLesser known features of PostgreSQL