public inbox for [email protected]
help / color / mirror / Atom feedFrom: Henson Choi <[email protected]>
To: Tatsuo Ishii <[email protected]>
Cc: [email protected]
Cc: [email protected]
Cc: [email protected]
Cc: [email protected]
Cc: [email protected]
Cc: [email protected]
Cc: [email protected]
Cc: [email protected]
Subject: Re: Row pattern recognition
Date: Sat, 21 Mar 2026 00:36:18 +0900
Message-ID: <CAAAe_zBbrnx2fjK2s+Jgx6TSOdnKAPawXbHeX49WqmX9ji+Hdg@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<CAAAe_zBz7mjNVms-ooF79+p_7eydkONBmz8YCk=oS0+WOfj3fQ@mail.gmail.com>
<[email protected]>
<[email protected]>
Hi Tatsuo,
Sorry for sending these before your next revision of v45 --
I'll be busy with a project proposal for the next couple of
weeks, so I wanted to share them before that.
Here are the patches on top of v45, incorporating the fixes and
improvements I mentioned earlier, plus additional changes from a
thorough code review. There are quite a few patches since I went
ahead without prior discussion -- please review each one individually
and feel free to drop any that you disagree with.
Here's a summary of each patch (to be applied on top of v45):
0001: Fix mergeGroupPrefixSuffix() max increment
When absorbing prefix/suffix, child->max was not incremented,
causing incorrect quantifier bounds. (reported by Zsolt)
0002: Fix RPR error codes and GROUPS typo
Use ERRCODE_WINDOWING_ERROR instead of ERRCODE_SYNTAX_ERROR
for semantic window-frame violations. Fix "GROUP" typo to
"GROUPS" in frame validation error message. (reported by Zsolt)
0003: Add check_stack_depth() and CHECK_FOR_INTERRUPTS()
Add stack depth protection to recursive pattern optimization
and interrupt checks to NFA engine loops.
0004: Fix window_last_value set_mark during RPR
Restore set_mark=true in window_last_value (normal behavior)
and add an RPR-specific override inside WinGetFuncArgInFrame.
This keeps the RPR workaround localized rather than changing
the caller's semantics unconditionally.
0005: Fix row_is_in_reduced_frame in WINDOW_SEEK_TAIL
Pass frameheadpos directly to row_is_in_reduced_frame
instead of frameheadpos+relpos. Currently only last_value()
calls this path with relpos=0 so no actual bug, but the
old expression would be incorrect for negative relpos.
Also add a bounds check for future callers.
0006: Clarify ST_NONE intent
Add comment explaining ST_NONE = 0 is the default for
non-RPR windows.
0007: Clarify inverse transition optimization comment
Document why RPR disables inverse transition: the reduced
frame changes row by row.
0008: Reject unused DEFINE variables
I know you preferred silently ignoring unused DEFINE
variables [1], and I agree the standard doesn't mandate
an error. However, if we later add qualified column
references (e.g. B AS A.price > 100), B's expression
depends on A being present. If A is not used in PATTERN
and the planner silently removes it, B's reference to A
becomes dangling. I worry that silently allowing this now
could create forward-compatibility problems once qualified
references are introduced. For that reason, I'm inclined
to think rejecting them now may be safer than changing
behavior later, which would be a user-visible compatibility
break. This is also consistent with Oracle's behavior
(ORA-62503), as SungJun reported, and it helps catch user
typos in DEFINE variable names at parse time.
0009: Clarify RPR documentation in advanced.sgml
Improve absorption explanation and clarify non-match row
aggregation behavior with concrete examples.
0010: Fix typos in RPR comments and parser README
0011: Clarify excludeLocation and empty quantifier in gram.y
Add comments explaining the conditional location assignment
pattern and the empty quantifier rule.
0012: Clarify RPR_VARID_MAX definition
Document varId range 0-250 and reserved control element
values 252+.
0013: Move local variables to function scope
In row_is_in_reduced_frame, move declarations out of
switch case blocks.
0014: Reset reduced_frame_map pointer in release_partition
Set reduced_frame_map = NULL and alloc_sz = 0 to prevent
dangling pointer after partition context reset.
0015: Remove redundant list manipulation in nfa_add_matched_state
Simplify doubly-linked list operation that was duplicated
by the subsequent ExecRPRFreeContext() call.
experimental: Implement 1-slot PREV/NEXT navigation
The slot-based PREV/NEXT approach I mentioned earlier.
This no longer needs attno_map, so the plan cache mutation
issue is gone. PREV/NEXT now also accepts an optional
second argument for offset, e.g. PREV(price, 2).
Note: all existing regression tests pass with this patch,
but my understanding of TupleSlot internals and JIT is still
shallow -- the implementation largely copies surrounding
patterns, so there may be issues I haven't caught yet. I'd
like to keep this as a separate patch on top of v46 until it
is trustworthy enough, rather than folding it into the main
series. I'll continue reviewing and hardening it.
One design decision regarding PREV/NEXT: the SQL standard
requires that the first argument of PREV/NEXT contain at least
one column reference [2] -- e.g. PREV(1) is a syntax error.
Beyond the standard's rationale (no starting row for offsetting),
there is a practical reason: when the target row falls outside
the frame, PREV should return NULL, but a constant expression
like PREV(42) would still evaluate to 42 since it never reads
from the slot. I think it's correct to follow the standard and
reject PREV/NEXT without a column reference. What do you think?
[1]
https://www.postgresql.org/message-id/20260305.142049.1864331791480656300.ishii%40postgresql.org
[2] ISO/IEC 19075-5, Subclause 5.6.2
Best regards,
Henson
From a9c7017524a14820f07955366cca5e6a2a154319 Mon Sep 17 00:00:00 2001
From: Henson Choi <[email protected]>
Date: Thu, 19 Mar 2026 11:37:34 +0900
Subject: [PATCH] Fix mergeGroupPrefixSuffix() to also increment max when
absorbing prefix/suffix
When mergeGroupPrefixSuffix() found a prefix or suffix that matched a GROUP
node's children, it incremented GROUP's min quantifier but left max unchanged.
This produced invalid quantifier state (min > max) when the GROUP had a finite
max bound.
Fix by also incrementing max when it is finite. Guard both the prefix and
suffix cases with an overflow check (min < RPR_QUANTITY_INF - 1) to prevent
wraparound into the sentinel value.
Add Assert() calls in fillRPRPatternVar(), fillRPRPatternGroup(), and
finalizeRPRPattern() to catch invalid min/max combinations at pattern
compilation time.
---
src/backend/optimizer/plan/rpr.c | 33 ++++++++++++++++++++++++-----
src/test/regress/expected/rpr.out | 35 +++++++++++++++++++++++++++++++
src/test/regress/sql/rpr.sql | 23 ++++++++++++++++++++
3 files changed, 86 insertions(+), 5 deletions(-)
diff --git a/src/backend/optimizer/plan/rpr.c b/src/backend/optimizer/plan/rpr.c
index b958280e94c..c50cbdc18f1 100644
--- a/src/backend/optimizer/plan/rpr.c
+++ b/src/backend/optimizer/plan/rpr.c
@@ -541,13 +541,17 @@ mergeGroupPrefixSuffix(List *children)
/* Compare with GROUP's (possibly unwrapped) children */
if (rprPatternChildrenEqual(prefixElements, groupContent) &&
- child->min < RPR_QUANTITY_INF)
+ child->min < RPR_QUANTITY_INF - 1 &&
+ (child->max == RPR_QUANTITY_INF ||
+ child->max < RPR_QUANTITY_INF - 1))
{
/*
- * Match! Merge by incrementing GROUP's min. Remove the
- * prefix elements from output.
+ * Match! Merge by incrementing GROUP's quantifier. Remove
+ * the prefix elements from output.
*/
child->min += 1;
+ if (child->max != RPR_QUANTITY_INF)
+ child->max += 1;
/* Rebuild result without matched prefix */
trimmed = NIL;
@@ -595,12 +599,17 @@ mergeGroupPrefixSuffix(List *children)
/* Compare with GROUP's children */
if (list_length(suffixElements) == groupChildCount &&
rprPatternChildrenEqual(suffixElements, groupContent) &&
- child->min < RPR_QUANTITY_INF)
+ child->min < RPR_QUANTITY_INF - 1 &&
+ (child->max == RPR_QUANTITY_INF ||
+ child->max < RPR_QUANTITY_INF - 1))
{
/*
- * Match! Absorb suffix by incrementing min and skipping.
+ * Match! Absorb suffix by incrementing quantifier and
+ * skipping.
*/
child->min += 1;
+ if (child->max != RPR_QUANTITY_INF)
+ child->max += 1;
skipUntil = suffixStart + groupChildCount;
/*
@@ -1152,6 +1161,9 @@ fillRPRPatternVar(RPRPatternNode *node, RPRPattern *pat, int *idx, RPRDepth dept
elem->depth = depth;
elem->min = node->min;
elem->max = (node->max == INT_MAX) ? RPR_QUANTITY_INF : node->max;
+ Assert(elem->min >= 0 && elem->min < RPR_QUANTITY_INF &&
+ elem->max >= 1 &&
+ (elem->max == RPR_QUANTITY_INF || elem->min <= elem->max));
elem->next = RPR_ELEMIDX_INVALID;
elem->jump = RPR_ELEMIDX_INVALID;
if (node->reluctant)
@@ -1191,6 +1203,9 @@ fillRPRPatternGroup(RPRPatternNode *node, RPRPattern *pat, int *idx, RPRDepth de
elem->depth = depth;
elem->min = node->min;
elem->max = (node->max == INT_MAX) ? RPR_QUANTITY_INF : node->max;
+ Assert(elem->min >= 0 && elem->min < RPR_QUANTITY_INF &&
+ elem->max >= 1 &&
+ (elem->max == RPR_QUANTITY_INF || elem->min <= elem->max));
elem->next = RPR_ELEMIDX_INVALID; /* set by finalize */
elem->jump = RPR_ELEMIDX_INVALID; /* set after END */
if (node->reluctant)
@@ -1216,6 +1231,9 @@ fillRPRPatternGroup(RPRPatternNode *node, RPRPattern *pat, int *idx, RPRDepth de
endElem->depth = depth;
endElem->min = node->min;
endElem->max = (node->max == INT_MAX) ? RPR_QUANTITY_INF : node->max;
+ Assert(endElem->min >= 0 && endElem->min < RPR_QUANTITY_INF &&
+ endElem->max >= 1 &&
+ (endElem->max == RPR_QUANTITY_INF || endElem->min <= endElem->max));
endElem->next = RPR_ELEMIDX_INVALID;
endElem->jump = groupStartIdx; /* loop to first child */
if (node->reluctant)
@@ -1398,6 +1416,11 @@ finalizeRPRPattern(RPRPattern *result)
if (elem->next == RPR_ELEMIDX_INVALID)
elem->next = (i < finIdx - 1) ? i + 1 : finIdx;
+
+ /* Verify quantifier range is valid */
+ Assert(elem->min >= 0 && elem->min < RPR_QUANTITY_INF &&
+ elem->max >= 1 &&
+ (elem->max == RPR_QUANTITY_INF || elem->min <= elem->max));
}
/* Add FIN marker at the end */
diff --git a/src/test/regress/expected/rpr.out b/src/test/regress/expected/rpr.out
index ecbe835cbb4..e72171050c7 100644
--- a/src/test/regress/expected/rpr.out
+++ b/src/test/regress/expected/rpr.out
@@ -588,6 +588,41 @@ SELECT company, tdate, price, count(*) OVER w
company2 | 07-10-2023 | 1300 | 0
(20 rows)
+-- test prefix/suffix merge optimization with bounded quantifier
+-- Pattern A B (A B){1,2} A B should be optimized to (A B){3,4}
+CREATE TEMP TABLE rpr_t (id int, val text);
+INSERT INTO rpr_t VALUES
+ (1,'A'),(2,'B'),
+ (3,'A'),(4,'B'),
+ (5,'A'),(6,'B'),
+ (7,'A'),(8,'B'),
+ (9,'X');
+SELECT id, val, count(*) OVER w AS match_count
+FROM rpr_t
+WINDOW w AS (
+ ORDER BY id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ INITIAL
+ PATTERN (A B (A B){1,2} A B)
+ DEFINE
+ A AS val = 'A',
+ B AS val = 'B'
+);
+ id | val | match_count
+----+-----+-------------
+ 1 | A | 8
+ 2 | B | 0
+ 3 | A | 6
+ 4 | B | 0
+ 5 | A | 0
+ 6 | B | 0
+ 7 | A | 0
+ 8 | B | 0
+ 9 | X | 0
+(9 rows)
+
+DROP TABLE rpr_t;
-- last_value() should remain consistent
SELECT company, tdate, price, last_value(price) OVER w
FROM stock
diff --git a/src/test/regress/sql/rpr.sql b/src/test/regress/sql/rpr.sql
index b308f8d7cb4..95794d409e1 100644
--- a/src/test/regress/sql/rpr.sql
+++ b/src/test/regress/sql/rpr.sql
@@ -249,6 +249,29 @@ SELECT company, tdate, price, count(*) OVER w
A AS price > 100
);
+-- test prefix/suffix merge optimization with bounded quantifier
+-- Pattern A B (A B){1,2} A B should be optimized to (A B){3,4}
+CREATE TEMP TABLE rpr_t (id int, val text);
+INSERT INTO rpr_t VALUES
+ (1,'A'),(2,'B'),
+ (3,'A'),(4,'B'),
+ (5,'A'),(6,'B'),
+ (7,'A'),(8,'B'),
+ (9,'X');
+SELECT id, val, count(*) OVER w AS match_count
+FROM rpr_t
+WINDOW w AS (
+ ORDER BY id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ INITIAL
+ PATTERN (A B (A B){1,2} A B)
+ DEFINE
+ A AS val = 'A',
+ B AS val = 'B'
+);
+DROP TABLE rpr_t;
+
-- last_value() should remain consistent
SELECT company, tdate, price, last_value(price) OVER w
FROM stock
--
2.50.1 (Apple Git-155)
From 9abc7a58b48d389e279d1a3676351f36b9ebc6c3 Mon Sep 17 00:00:00 2001
From: Henson Choi <[email protected]>
Date: Thu, 19 Mar 2026 16:40:35 +0900
Subject: [PATCH] Fix RPR error codes and GROUPS typo in frame validation
Frame validation errors in transformRPR() were reported with
ERRCODE_SYNTAX_ERROR. These are semantic errors detected after parsing,
so ERRCODE_WINDOWING_ERROR is the correct code per SQL standard.
Also fix a typo in the error message: "FRAME option GROUP" ->
"FRAME option GROUPS" to match the actual SQL keyword.
---
src/backend/parser/parse_rpr.c | 10 +++++-----
src/test/regress/expected/rpr_base.out | 8 ++++----
src/test/regress/sql/rpr_base.sql | 4 ++--
3 files changed, 11 insertions(+), 11 deletions(-)
diff --git a/src/backend/parser/parse_rpr.c b/src/backend/parser/parse_rpr.c
index e574b69d9b5..66252cd185e 100644
--- a/src/backend/parser/parse_rpr.c
+++ b/src/backend/parser/parse_rpr.c
@@ -72,15 +72,15 @@ transformRPR(ParseState *pstate, WindowClause *wc, WindowDef *windef,
/* Frame type must be "ROW" */
if (wc->frameOptions & FRAMEOPTION_GROUPS)
ereport(ERROR,
- (errcode(ERRCODE_SYNTAX_ERROR),
- errmsg("FRAME option GROUP is not permitted when row pattern recognition is used"),
+ (errcode(ERRCODE_WINDOWING_ERROR),
+ errmsg("FRAME option GROUPS is not permitted when row pattern recognition is used"),
errhint("Use: ROWS instead"),
parser_errposition(pstate,
windef->frameLocation >= 0 ?
windef->frameLocation : windef->location)));
if (wc->frameOptions & FRAMEOPTION_RANGE)
ereport(ERROR,
- (errcode(ERRCODE_SYNTAX_ERROR),
+ (errcode(ERRCODE_WINDOWING_ERROR),
errmsg("FRAME option RANGE is not permitted when row pattern recognition is used"),
errhint("Use: ROWS instead"),
parser_errposition(pstate,
@@ -107,7 +107,7 @@ transformRPR(ParseState *pstate, WindowClause *wc, WindowDef *windef,
(wc->frameOptions & FRAMEOPTION_START_OFFSET_FOLLOWING));
ereport(ERROR,
- (errcode(ERRCODE_SYNTAX_ERROR),
+ (errcode(ERRCODE_WINDOWING_ERROR),
errmsg("FRAME must start at CURRENT ROW when row pattern recognition is used"),
errdetail("Current frame starts with %s.", startBound),
errhint("Use: %s BETWEEN CURRENT ROW AND ...", frameType),
@@ -133,7 +133,7 @@ transformRPR(ParseState *pstate, WindowClause *wc, WindowDef *windef,
(wc->frameOptions & FRAMEOPTION_EXCLUDE_TIES));
ereport(ERROR,
- (errcode(ERRCODE_SYNTAX_ERROR),
+ (errcode(ERRCODE_WINDOWING_ERROR),
errmsg("EXCLUDE options are not permitted when row pattern recognition is used"),
errdetail("Frame definition includes %s.", excludeType),
errhint("Remove the EXCLUDE clause from the window definition."),
diff --git a/src/test/regress/expected/rpr_base.out b/src/test/regress/expected/rpr_base.out
index 7931ad07d7d..50a9e7daea9 100644
--- a/src/test/regress/expected/rpr_base.out
+++ b/src/test/regress/expected/rpr_base.out
@@ -483,11 +483,11 @@ WINDOW w AS (
PATTERN (A+)
DEFINE A AS val > 0
);
-ERROR: FRAME option GROUP is not permitted when row pattern recognition is used
+ERROR: FRAME option GROUPS is not permitted when row pattern recognition is used
LINE 5: GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWI...
^
HINT: Use: ROWS instead
--- Expected: ERROR: FRAME option GROUP is not permitted when row pattern recognition is used
+-- Expected: ERROR: FRAME option GROUPS is not permitted when row pattern recognition is used
-- Starting with N PRECEDING
SELECT COUNT(*) OVER w
FROM rpr_frame
@@ -656,11 +656,11 @@ WINDOW w AS (
DEFINE A AS val >= 0, B AS val >= 0
)
ORDER BY id;
-ERROR: FRAME option GROUP is not permitted when row pattern recognition is used
+ERROR: FRAME option GROUPS is not permitted when row pattern recognition is used
LINE 5: GROUPS BETWEEN CURRENT ROW AND 1 FOLLOWING
^
HINT: Use: ROWS instead
--- Expected: ERROR: FRAME option GROUP is not permitted when row pattern recognition is used
+-- Expected: ERROR: FRAME option GROUPS is not permitted when row pattern recognition is used
DROP TABLE rpr_frame;
-- ============================================================
-- PARTITION BY + FRAME Tests
diff --git a/src/test/regress/sql/rpr_base.sql b/src/test/regress/sql/rpr_base.sql
index f8815c7376a..e54d54e400a 100644
--- a/src/test/regress/sql/rpr_base.sql
+++ b/src/test/regress/sql/rpr_base.sql
@@ -397,7 +397,7 @@ WINDOW w AS (
PATTERN (A+)
DEFINE A AS val > 0
);
--- Expected: ERROR: FRAME option GROUP is not permitted when row pattern recognition is used
+-- Expected: ERROR: FRAME option GROUPS is not permitted when row pattern recognition is used
-- Starting with N PRECEDING
SELECT COUNT(*) OVER w
@@ -517,7 +517,7 @@ WINDOW w AS (
DEFINE A AS val >= 0, B AS val >= 0
)
ORDER BY id;
--- Expected: ERROR: FRAME option GROUP is not permitted when row pattern recognition is used
+-- Expected: ERROR: FRAME option GROUPS is not permitted when row pattern recognition is used
DROP TABLE rpr_frame;
--
2.50.1 (Apple Git-155)
From 0bea151eb70ae266cf29ee24395e8d178bece917 Mon Sep 17 00:00:00 2001
From: Henson Choi <[email protected]>
Date: Sat, 14 Mar 2026 09:44:40 +0900
Subject: [PATCH] Add check_stack_depth() and CHECK_FOR_INTERRUPTS() to RPR
engine
Several recursive functions in the RPR implementation lacked stack
overflow protection, and the NFA evaluation loop lacked interrupt
handling.
Add check_stack_depth() to all recursive RPR functions:
- execRPR.c: nfa_advance_state() (NFA epsilon-closure DFS)
- rpr.c: optimizeRPRPattern(), scanRPRPatternRecursive(),
fillRPRPattern(), computeAbsorbabilityRecursive(),
collectPatternVariablesRecursive()
- parse_rpr.c: validateRPRPatternVarCount()
Add CHECK_FOR_INTERRUPTS() in the main NFA processing loops in
execRPR.c to allow query cancellation during long pattern matches.
---
src/backend/executor/execRPR.c | 17 ++++++++++++++++-
src/backend/optimizer/plan/rpr.c | 11 +++++++++++
src/backend/parser/parse_rpr.c | 3 +++
3 files changed, 30 insertions(+), 1 deletion(-)
diff --git a/src/backend/executor/execRPR.c b/src/backend/executor/execRPR.c
index 0d5ba7516e9..06934b95da3 100644
--- a/src/backend/executor/execRPR.c
+++ b/src/backend/executor/execRPR.c
@@ -24,6 +24,7 @@
#include "executor/execRPR.h"
#include "executor/executor.h"
+#include "miscadmin.h"
#include "optimizer/rpr.h"
#include "utils/memutils.h"
@@ -1994,6 +1995,8 @@ nfa_update_absorption_flags(RPRNFAContext *ctx)
*/
for (state = ctx->states; state != NULL; state = state->next)
{
+ CHECK_FOR_INTERRUPTS();
+
if (state->isAbsorbable)
hasAbsorbable = true;
else
@@ -2035,6 +2038,8 @@ nfa_states_covered(RPRPattern *pattern, RPRNFAContext *older, RPRNFAContext *new
for (olderState = older->states; olderState != NULL; olderState = olderState->next)
{
+ CHECK_FOR_INTERRUPTS();
+
/* Covering state must also be absorbable */
if (olderState->isAbsorbable &&
olderState->elemIdx == newerState->elemIdx &&
@@ -2195,6 +2200,8 @@ nfa_match(WindowAggState *winstate, RPRNFAContext *ctx, bool *varMatched)
{
RPRPatternElement *elem = &elements[state->elemIdx];
+ CHECK_FOR_INTERRUPTS();
+
nextState = state->next;
if (RPRElemIsVar(elem))
@@ -2670,6 +2677,9 @@ nfa_advance_state(WindowAggState *winstate, RPRNFAContext *ctx,
Assert(state->elemIdx >= 0 && state->elemIdx < pattern->numElements);
+ /* Protect against stack overflow for deeply complex patterns */
+ check_stack_depth();
+
/* Cycle detection: if this elemIdx was already visited in this DFS, bail */
if (winstate->nfaVisitedElems[WORDNUM(state->elemIdx)] &
((bitmapword) 1 << BITNUM(state->elemIdx)))
@@ -2722,13 +2732,15 @@ nfa_advance(WindowAggState *winstate, RPRNFAContext *ctx, int64 currentPos)
{
RPRNFAState *states = ctx->states;
RPRNFAState *state;
+ RPRNFAState *savedMatchedState;
ctx->states = NULL; /* Will rebuild */
/* Process each state in lexical order (DFS order from previous advance) */
while (states != NULL)
{
- RPRNFAState *savedMatchedState = ctx->matchedState;
+ CHECK_FOR_INTERRUPTS();
+ savedMatchedState = ctx->matchedState;
/* Clear visited bitmap before each state's DFS expansion */
memset(winstate->nfaVisitedElems, 0,
@@ -2912,6 +2924,9 @@ ExecRPRProcessRow(WindowAggState *winstate, int64 currentPos,
RPRNFAContext *ctx;
bool *varMatched = winstate->nfaVarMatched;
+ /* Allow query cancellation once per row for simple/low-state patterns */
+ CHECK_FOR_INTERRUPTS();
+
/*
* Phase 1: Match all contexts (convergence). Evaluate VAR elements,
* update counts, remove dead states.
diff --git a/src/backend/optimizer/plan/rpr.c b/src/backend/optimizer/plan/rpr.c
index c50cbdc18f1..0b4d93b933e 100644
--- a/src/backend/optimizer/plan/rpr.c
+++ b/src/backend/optimizer/plan/rpr.c
@@ -37,6 +37,7 @@
#include "postgres.h"
+#include "miscadmin.h"
#include "nodes/makefuncs.h"
#include "optimizer/rpr.h"
@@ -933,6 +934,8 @@ optimizeRPRPattern(RPRPatternNode *pattern)
/* Pattern nodes from parser are never NULL */
Assert(pattern != NULL);
+ check_stack_depth();
+
switch (pattern->nodeType)
{
case RPR_PATTERN_VAR:
@@ -991,6 +994,8 @@ scanRPRPatternRecursive(RPRPatternNode *node, char **varNames, int *numVars,
/* Pattern nodes from parser are never NULL */
Assert(node != NULL);
+ check_stack_depth();
+
/* Check recursion depth limit before overflow occurs */
if (depth >= RPR_DEPTH_MAX)
ereport(ERROR,
@@ -1367,6 +1372,8 @@ fillRPRPattern(RPRPatternNode *node, RPRPattern *pat, int *idx, RPRDepth depth)
/* Pattern nodes from parser are never NULL */
Assert(node != NULL);
+ check_stack_depth();
+
switch (node->nodeType)
{
case RPR_PATTERN_SEQ:
@@ -1609,6 +1616,8 @@ computeAbsorbabilityRecursive(RPRPattern *pattern, RPRElemIdx startIdx,
{
RPRPatternElement *elem = &pattern->elements[startIdx];
+ check_stack_depth();
+
if (RPRElemIsAlt(elem))
{
/* ALT: recursively check each branch */
@@ -1716,6 +1725,8 @@ collectPatternVariablesRecursive(RPRPatternNode *node, List **varNames)
Assert(node != NULL);
+ check_stack_depth();
+
switch (node->nodeType)
{
case RPR_PATTERN_VAR:
diff --git a/src/backend/parser/parse_rpr.c b/src/backend/parser/parse_rpr.c
index 66252cd185e..92fef2d9ba7 100644
--- a/src/backend/parser/parse_rpr.c
+++ b/src/backend/parser/parse_rpr.c
@@ -25,6 +25,7 @@
#include "postgres.h"
+#include "miscadmin.h"
#include "nodes/makefuncs.h"
#include "nodes/nodeFuncs.h"
#include "optimizer/rpr.h"
@@ -175,6 +176,8 @@ validateRPRPatternVarCount(ParseState *pstate, RPRPatternNode *node,
/* Pattern node must exist - parser always provides non-NULL root */
Assert(node != NULL);
+ check_stack_depth();
+
switch (node->nodeType)
{
case RPR_PATTERN_VAR:
--
2.50.1 (Apple Git-155)
From ddf11ac02969b003d1f9b4296d2806dd64c88469 Mon Sep 17 00:00:00 2001
From: Henson Choi <[email protected]>
Date: Fri, 20 Mar 2026 13:39:20 +0900
Subject: [PATCH] Fix window_last_value set_mark to not advance mark during RPR
window_last_value passes set_mark=true to WinGetFuncArgInFrame, which is
correct for normal use: advancing the mark allows the tuplestore to discard
rows no longer needed, reducing memory usage.
However, when RPR is active, the reduced frame changes row by row. Advancing
the mark would prevent revisiting earlier rows that still fall within a future
row's reduced frame, producing incorrect results.
Rather than requiring each caller to know about RPR, add an override in
WinGetFuncArgInFrame: if RPR is defined, force set_mark=false regardless
of what the caller passed. This keeps the fix localized and transparent
to existing callers.
---
src/backend/executor/nodeWindowAgg.c | 7 +++++++
src/backend/utils/adt/windowfuncs.c | 2 +-
2 files changed, 8 insertions(+), 1 deletion(-)
diff --git a/src/backend/executor/nodeWindowAgg.c b/src/backend/executor/nodeWindowAgg.c
index 0a9ba5bd4e7..d3ce9897a4f 100644
--- a/src/backend/executor/nodeWindowAgg.c
+++ b/src/backend/executor/nodeWindowAgg.c
@@ -4700,6 +4700,13 @@ WinGetFuncArgInFrame(WindowObject winobj, int argno,
econtext = winstate->ss.ps.ps_ExprContext;
slot = winstate->temp_slot_1;
+ /*
+ * When RPR is active, the reduced frame changes row by row, so we must
+ * not advance the mark — doing so would prevent revisiting earlier rows.
+ */
+ if (rpr_is_defined(winstate))
+ set_mark = false;
+
if (winobj->ignore_nulls == IGNORE_NULLS)
return ignorenulls_getfuncarginframe(winobj, argno, relpos, seektype,
set_mark, isnull, isout);
diff --git a/src/backend/utils/adt/windowfuncs.c b/src/backend/utils/adt/windowfuncs.c
index efb60c99052..74ef109f72e 100644
--- a/src/backend/utils/adt/windowfuncs.c
+++ b/src/backend/utils/adt/windowfuncs.c
@@ -682,7 +682,7 @@ window_last_value(PG_FUNCTION_ARGS)
WinCheckAndInitializeNullTreatment(winobj, true, fcinfo);
result = WinGetFuncArgInFrame(winobj, 0,
- 0, WINDOW_SEEK_TAIL, false,
+ 0, WINDOW_SEEK_TAIL, true,
&isnull, NULL);
if (isnull)
PG_RETURN_NULL();
--
2.50.1 (Apple Git-155)
From 40a5d3ae0b3f76ffb2ad73100d83d4efb2a3fcc6 Mon Sep 17 00:00:00 2001
From: Henson Choi <[email protected]>
Date: Fri, 20 Mar 2026 14:14:17 +0900
Subject: [PATCH] Fix row_is_in_reduced_frame argument and add bounds check in
WINDOW_SEEK_TAIL
In WinGetSlotInFrame, the WINDOW_SEEK_TAIL case passed frameheadpos+relpos
to row_is_in_reduced_frame to determine the reduced frame size. Since relpos
is non-positive, this queries a position at or before the frame head. When
relpos is negative the queried position falls outside the frame entirely,
which is invalid and could trigger internal Assert failures.
The correct argument is frameheadpos: the reduced frame always starts there,
and its size N gives the tail position as frameheadpos+N-1. Pass frameheadpos
directly instead.
Also add a bounds check (-relpos >= N -> out_of_frame) to handle future
callers that may pass negative relpos values. Currently only last_value()
calls this path with relpos=0, so the check has no effect at present.
---
src/backend/executor/nodeWindowAgg.c | 6 +++++-
1 file changed, 5 insertions(+), 1 deletion(-)
diff --git a/src/backend/executor/nodeWindowAgg.c b/src/backend/executor/nodeWindowAgg.c
index d3ce9897a4f..c92235c54c7 100644
--- a/src/backend/executor/nodeWindowAgg.c
+++ b/src/backend/executor/nodeWindowAgg.c
@@ -4906,12 +4906,16 @@ WinGetSlotInFrame(WindowObject winobj, TupleTableSlot *slot,
}
num_reduced_frame = row_is_in_reduced_frame(winobj,
- winstate->frameheadpos + relpos);
+ winstate->frameheadpos);
if (num_reduced_frame < 0)
goto out_of_frame;
else if (num_reduced_frame > 0)
+ {
+ if (-relpos >= num_reduced_frame)
+ goto out_of_frame;
abs_pos = winstate->frameheadpos + relpos +
num_reduced_frame - 1;
+ }
break;
default:
elog(ERROR, "unrecognized window seek type: %d", seektype);
--
2.50.1 (Apple Git-155)
From e267e4f1e40bd6919efb4ebef769e60da3a7ac4e Mon Sep 17 00:00:00 2001
From: Henson Choi <[email protected]>
Date: Fri, 20 Mar 2026 16:59:21 +0900
Subject: [PATCH] Clarify ST_NONE intent in RPSkipTo enum and WindowClause
initialization
ST_NONE serves as the sentinel value (0) indicating that a WindowClause
has no RPR AFTER MATCH clause. This was implicit via palloc0 zero-filling
but not documented.
Add a comment to the ST_NONE enum value explaining its role as the default
for non-RPR windows, and add an explicit assignment in
transformWindowDefinitions() to make the intent clear in code.
---
src/backend/parser/parse_clause.c | 2 ++
src/include/nodes/parsenodes.h | 3 ++-
2 files changed, 4 insertions(+), 1 deletion(-)
diff --git a/src/backend/parser/parse_clause.c b/src/backend/parser/parse_clause.c
index b30c22933ec..3796a69ac3a 100644
--- a/src/backend/parser/parse_clause.c
+++ b/src/backend/parser/parse_clause.c
@@ -2889,6 +2889,8 @@ transformWindowDefinitions(ParseState *pstate,
* And prepare the new WindowClause.
*/
wc = makeNode(WindowClause);
+ wc->rpSkipTo = ST_NONE; /* ST_NONE marks this as a non-RPR window;
+ * overridden by transformRPR() if RPR is used */
wc->name = windef->name;
wc->refname = windef->refname;
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 432b0573990..c5bf2ce80bf 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -583,7 +583,8 @@ typedef struct SortBy
*/
typedef enum RPSkipTo
{
- ST_NONE, /* AFTER MATCH omitted */
+ ST_NONE, /* no AFTER MATCH clause; default for non-RPR
+ * windows */
ST_NEXT_ROW, /* SKIP TO NEXT ROW */
ST_PAST_LAST_ROW, /* SKIP TO PAST LAST ROW */
} RPSkipTo;
--
2.50.1 (Apple Git-155)
From 124119ef649d05bcd3e20f5e5a1c353a186f99db Mon Sep 17 00:00:00 2001
From: Henson Choi <[email protected]>
Date: Fri, 20 Mar 2026 19:01:43 +0900
Subject: [PATCH] Clarify why RPR disables inverse transition optimization
When RPR is active, the reduced frame depends on pattern matching results
which can differ entirely from row to row. This makes it impossible to
use the inverse transition function to incrementally remove rows from the
aggregate state, so a full restart is required for every row.
Add a comment explaining this reasoning at the restart decision logic.
---
src/backend/executor/nodeWindowAgg.c | 4 +++-
1 file changed, 3 insertions(+), 1 deletion(-)
diff --git a/src/backend/executor/nodeWindowAgg.c b/src/backend/executor/nodeWindowAgg.c
index c92235c54c7..d144fa39375 100644
--- a/src/backend/executor/nodeWindowAgg.c
+++ b/src/backend/executor/nodeWindowAgg.c
@@ -853,7 +853,9 @@ eval_windowaggregates(WindowAggState *winstate)
* transition function, or
* - we have an EXCLUSION clause, or
* - if the new frame doesn't overlap the old one
- * - if RPR is enabled
+ * - if RPR (Row Pattern Recognition) is enabled, because the reduced
+ * frame depends on pattern matching results which can differ entirely
+ * from row to row, making inverse transition optimization inapplicable
*
* Note that we don't strictly need to restart in the last case, but if
* we're going to remove all rows from the aggregation anyway, a restart
--
2.50.1 (Apple Git-155)
From 213650dbee9962492bfdd65ce3ed61fee674e5b4 Mon Sep 17 00:00:00 2001
From: Henson Choi <[email protected]>
Date: Fri, 20 Mar 2026 19:42:27 +0900
Subject: [PATCH] Reject DEFINE variables not used in PATTERN
DEFINE variables that do not appear in PATTERN are now rejected with
an error at parse time. When DEFINE supports qualified column references
(e.g. A.price), a DEFINE expression may reference other pattern variables.
If the planner silently removes unused variables, those references become
dangling, leading to incorrect evaluation. Rejecting at parse time avoids
this class of bugs entirely.
Remove filterDefineClause() from the planner and replace it with
buildDefineVariableList(), which builds the variable name list without
filtering. Update regression tests accordingly.
---
src/backend/executor/execRPR.c | 5 +-
src/backend/optimizer/plan/createplan.c | 15 ++---
src/backend/optimizer/plan/rpr.c | 34 ++--------
src/backend/parser/parse_rpr.c | 9 ++-
src/include/optimizer/rpr.h | 4 +-
src/test/regress/expected/rpr_base.out | 82 +++++--------------------
src/test/regress/sql/rpr_base.sql | 46 ++------------
7 files changed, 43 insertions(+), 152 deletions(-)
diff --git a/src/backend/executor/execRPR.c b/src/backend/executor/execRPR.c
index 06934b95da3..a0a462256ad 100644
--- a/src/backend/executor/execRPR.c
+++ b/src/backend/executor/execRPR.c
@@ -204,8 +204,7 @@
* IV-1. Entry Point
*
* create_windowagg_plan() (createplan.c)
- * +-- collectPatternVariables() Collect variable names
- * +-- filterDefineClause() Remove unused DEFINE entries
+ * +-- buildDefineVariableList() Build variable name list from DEFINE
* +-- buildRPRPattern() NFA compilation (6 phases)
*
* IV-2. The 6 Phases of buildRPRPattern()
@@ -1297,7 +1296,7 @@
* transformRPR parse_rpr.c Parser entry point
* transformDefineClause parse_rpr.c DEFINE transformation
* collectPatternVariables rpr.c Variable collection
- * filterDefineClause rpr.c DEFINE filtering
+ * buildDefineVariableList rpr.c DEFINE variable list
* buildRPRPattern rpr.c NFA compilation main
* optimizeRPRPattern rpr.c AST optimization
* fillRPRPattern rpr.c NFA element generation
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
index da71e7f3d64..9ac24cc222d 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -2539,19 +2539,16 @@ create_windowagg_plan(PlannerInfo *root, WindowAggPath *best_path)
ordNumCols++;
}
- /* Build RPR pattern and filter defineClause */
+ /* Build RPR pattern and defineVariableList */
if (wc->rpPattern)
{
- List *patternVars;
-
/*
- * Filter defineClause to include only variables used in PATTERN. This
- * eliminates unnecessary DEFINE evaluations at runtime.
+ * Build defineVariableList from defineClause. The parser already
+ * rejects DEFINE variables not used in PATTERN, so no filtering is
+ * needed.
*/
- patternVars = collectPatternVariables(wc->rpPattern);
- filteredDefineClause = filterDefineClause(wc->defineClause,
- patternVars,
- &defineVariableList);
+ buildDefineVariableList(wc->defineClause, &defineVariableList);
+ filteredDefineClause = wc->defineClause;
/* Compile and optimize RPR patterns */
compiledPattern = buildRPRPattern(wc->rpPattern,
diff --git a/src/backend/optimizer/plan/rpr.c b/src/backend/optimizer/plan/rpr.c
index 0b4d93b933e..85b1a00d095 100644
--- a/src/backend/optimizer/plan/rpr.c
+++ b/src/backend/optimizer/plan/rpr.c
@@ -1771,50 +1771,28 @@ collectPatternVariables(RPRPatternNode *pattern)
}
/*
- * filterDefineClause
- * Filter defineClause to include only variables used in PATTERN.
+ * buildDefineVariableList
+ * Build defineVariableList from defineClause.
*
- * This eliminates unnecessary DEFINE evaluations at runtime.
- * Also builds defineVariableList from the filtered result.
+ * The parser already ensures that all DEFINE variables appear in PATTERN,
+ * so no filtering is needed here.
*
- * Returns filtered defineClause (list of TargetEntry).
* Sets *defineVariableList to list of variable names (String nodes).
*/
-List *
-filterDefineClause(List *defineClause, List *patternVars,
- List **defineVariableList)
+void
+buildDefineVariableList(List *defineClause, List **defineVariableList)
{
- List *filteredDefineClause = NIL;
ListCell *lc;
- ListCell *lc2;
*defineVariableList = NIL;
- /* Filter defineClause: keep only variables used in PATTERN */
foreach(lc, defineClause)
{
TargetEntry *te = (TargetEntry *) lfirst(lc);
- foreach(lc2, patternVars)
- {
- if (strcmp(strVal(lfirst(lc2)), te->resname) == 0)
- {
- filteredDefineClause = lappend(filteredDefineClause, te);
- break;
- }
- }
- }
-
- /* Build defineVariableList from filtered defineClause */
- foreach(lc, filteredDefineClause)
- {
- TargetEntry *te = (TargetEntry *) lfirst(lc);
-
*defineVariableList = lappend(*defineVariableList,
makeString(pstrdup(te->resname)));
}
-
- return filteredDefineClause;
}
/*
diff --git a/src/backend/parser/parse_rpr.c b/src/backend/parser/parse_rpr.c
index 92fef2d9ba7..55283ab4bbe 100644
--- a/src/backend/parser/parse_rpr.c
+++ b/src/backend/parser/parse_rpr.c
@@ -244,8 +244,11 @@ validateRPRPatternVarCount(ParseState *pstate, RPRPatternNode *node,
}
}
if (!found)
- *varNames = lappend(*varNames,
- makeString(pstrdup(rt->name)));
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("DEFINE variable \"%s\" is not used in PATTERN",
+ rt->name),
+ parser_errposition(pstate, rt->location)));
}
}
}
@@ -265,7 +268,7 @@ validateRPRPatternVarCount(ParseState *pstate, RPRPatternNode *node,
* 6. Marks column origins and assigns collation information
*
* Note: Variables not in DEFINE are evaluated as TRUE by the executor.
- * Variables in DEFINE but not in PATTERN are filtered out by the planner.
+ * Variables in DEFINE but not in PATTERN are rejected as an error.
*
* XXX Pattern variable qualified column references in DEFINE (e.g.
* "A.price") are not yet supported. Currently rejected by
diff --git a/src/include/optimizer/rpr.h b/src/include/optimizer/rpr.h
index fa2d075925c..f93a128096b 100644
--- a/src/include/optimizer/rpr.h
+++ b/src/include/optimizer/rpr.h
@@ -52,8 +52,8 @@
#define RPRElemCanSkip(e) ((e)->min == 0)
extern List *collectPatternVariables(RPRPatternNode *pattern);
-extern List *filterDefineClause(List *defineClause, List *patternVars,
- List **defineVariableList);
+extern void buildDefineVariableList(List *defineClause,
+ List **defineVariableList);
extern RPRPattern *buildRPRPattern(RPRPatternNode *pattern, List *defineVariableList,
RPSkipTo rpSkipTo, int frameOptions);
diff --git a/src/test/regress/expected/rpr_base.out b/src/test/regress/expected/rpr_base.out
index 50a9e7daea9..3168468d0ae 100644
--- a/src/test/regress/expected/rpr_base.out
+++ b/src/test/regress/expected/rpr_base.out
@@ -353,12 +353,9 @@ WINDOW w AS (
DEFINE A AS id > 0, B AS id > 5 -- B not in pattern
)
ORDER BY id;
- id | cnt
-----+-----
- 1 | 2
- 2 | 0
-(2 rows)
-
+ERROR: DEFINE variable "b" is not used in PATTERN
+LINE 7: DEFINE A AS id > 0, B AS id > 5 -- B not in pattern
+ ^
DROP TABLE rpr_unused;
-- ============================================================
-- FRAME Options Tests
@@ -2860,9 +2857,9 @@ WINDOW w AS (
PATTERN (A+)
DEFINE A AS val > 0, B AS B.val > 0
);
-ERROR: pattern variable qualified column reference "b.val" is not supported in DEFINE clause
+ERROR: DEFINE variable "b" is not used in PATTERN
LINE 7: DEFINE A AS val > 0, B AS B.val > 0
- ^
+ ^
-- Expected: ERROR: pattern variable qualified column reference "b.val" is not supported
-- FROM-clause range variable qualified name: not allowed (prohibited by §6.5)
SELECT COUNT(*) OVER w
@@ -2941,12 +2938,9 @@ WINDOW w AS (
DEFINE A AS val > 0, B AS val > 5, C AS val > 10
)
ORDER BY id;
- id | val | cnt
-----+-----+-----
- 1 | 10 | 2
- 2 | 20 | 0
-(2 rows)
-
+ERROR: DEFINE variable "b" is not used in PATTERN
+LINE 7: DEFINE A AS val > 0, B AS val > 5, C AS val > 10
+ ^
DROP TABLE rpr_err;
-- NULL handling
CREATE TABLE rpr_null (id INT, val INT);
@@ -5670,7 +5664,7 @@ DROP TABLE rpr_stress;
-- Tests for error conditions in rpr.c
CREATE TABLE rpr_errors (id INT, val INT);
INSERT INTO rpr_errors VALUES (1, 10), (2, 20);
--- Test: PATTERN variable without DEFINE (A), DEFINE variable not in PATTERN (B)
+-- Test: DEFINE variable not in PATTERN (error)
SELECT id, val, COUNT(*) OVER w FROM rpr_errors
WINDOW w AS (
ORDER BY id
@@ -5679,55 +5673,11 @@ WINDOW w AS (
DEFINE
B AS TRUE
);
- id | val | count
-----+-----+-------
- 1 | 10 | 0
- 2 | 20 | 0
-(2 rows)
-
--- Expected: Success - A is implicitly TRUE, B is filtered out
--- Test: 3 variables in PATTERN, 253 in DEFINE (DEFINE filtering test)
-SELECT COUNT(*) OVER w FROM rpr_errors
-WINDOW w AS (
- ORDER BY id
- ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
- PATTERN (V1 V2 V3)
- DEFINE
- V1 AS val > 0, V2 AS val > 0, V3 AS val > 0, V4 AS val > 0, V5 AS val > 0, V6 AS val > 0, V7 AS val > 0, V8 AS val > 0, V9 AS val > 0, V10 AS val > 0,
- V11 AS val > 0, V12 AS val > 0, V13 AS val > 0, V14 AS val > 0, V15 AS val > 0, V16 AS val > 0, V17 AS val > 0, V18 AS val > 0, V19 AS val > 0, V20 AS val > 0,
- V21 AS val > 0, V22 AS val > 0, V23 AS val > 0, V24 AS val > 0, V25 AS val > 0, V26 AS val > 0, V27 AS val > 0, V28 AS val > 0, V29 AS val > 0, V30 AS val > 0,
- V31 AS val > 0, V32 AS val > 0, V33 AS val > 0, V34 AS val > 0, V35 AS val > 0, V36 AS val > 0, V37 AS val > 0, V38 AS val > 0, V39 AS val > 0, V40 AS val > 0,
- V41 AS val > 0, V42 AS val > 0, V43 AS val > 0, V44 AS val > 0, V45 AS val > 0, V46 AS val > 0, V47 AS val > 0, V48 AS val > 0, V49 AS val > 0, V50 AS val > 0,
- V51 AS val > 0, V52 AS val > 0, V53 AS val > 0, V54 AS val > 0, V55 AS val > 0, V56 AS val > 0, V57 AS val > 0, V58 AS val > 0, V59 AS val > 0, V60 AS val > 0,
- V61 AS val > 0, V62 AS val > 0, V63 AS val > 0, V64 AS val > 0, V65 AS val > 0, V66 AS val > 0, V67 AS val > 0, V68 AS val > 0, V69 AS val > 0, V70 AS val > 0,
- V71 AS val > 0, V72 AS val > 0, V73 AS val > 0, V74 AS val > 0, V75 AS val > 0, V76 AS val > 0, V77 AS val > 0, V78 AS val > 0, V79 AS val > 0, V80 AS val > 0,
- V81 AS val > 0, V82 AS val > 0, V83 AS val > 0, V84 AS val > 0, V85 AS val > 0, V86 AS val > 0, V87 AS val > 0, V88 AS val > 0, V89 AS val > 0, V90 AS val > 0,
- V91 AS val > 0, V92 AS val > 0, V93 AS val > 0, V94 AS val > 0, V95 AS val > 0, V96 AS val > 0, V97 AS val > 0, V98 AS val > 0, V99 AS val > 0, V100 AS val > 0,
- V101 AS val > 0, V102 AS val > 0, V103 AS val > 0, V104 AS val > 0, V105 AS val > 0, V106 AS val > 0, V107 AS val > 0, V108 AS val > 0, V109 AS val > 0, V110 AS val > 0,
- V111 AS val > 0, V112 AS val > 0, V113 AS val > 0, V114 AS val > 0, V115 AS val > 0, V116 AS val > 0, V117 AS val > 0, V118 AS val > 0, V119 AS val > 0, V120 AS val > 0,
- V121 AS val > 0, V122 AS val > 0, V123 AS val > 0, V124 AS val > 0, V125 AS val > 0, V126 AS val > 0, V127 AS val > 0, V128 AS val > 0, V129 AS val > 0, V130 AS val > 0,
- V131 AS val > 0, V132 AS val > 0, V133 AS val > 0, V134 AS val > 0, V135 AS val > 0, V136 AS val > 0, V137 AS val > 0, V138 AS val > 0, V139 AS val > 0, V140 AS val > 0,
- V141 AS val > 0, V142 AS val > 0, V143 AS val > 0, V144 AS val > 0, V145 AS val > 0, V146 AS val > 0, V147 AS val > 0, V148 AS val > 0, V149 AS val > 0, V150 AS val > 0,
- V151 AS val > 0, V152 AS val > 0, V153 AS val > 0, V154 AS val > 0, V155 AS val > 0, V156 AS val > 0, V157 AS val > 0, V158 AS val > 0, V159 AS val > 0, V160 AS val > 0,
- V161 AS val > 0, V162 AS val > 0, V163 AS val > 0, V164 AS val > 0, V165 AS val > 0, V166 AS val > 0, V167 AS val > 0, V168 AS val > 0, V169 AS val > 0, V170 AS val > 0,
- V171 AS val > 0, V172 AS val > 0, V173 AS val > 0, V174 AS val > 0, V175 AS val > 0, V176 AS val > 0, V177 AS val > 0, V178 AS val > 0, V179 AS val > 0, V180 AS val > 0,
- V181 AS val > 0, V182 AS val > 0, V183 AS val > 0, V184 AS val > 0, V185 AS val > 0, V186 AS val > 0, V187 AS val > 0, V188 AS val > 0, V189 AS val > 0, V190 AS val > 0,
- V191 AS val > 0, V192 AS val > 0, V193 AS val > 0, V194 AS val > 0, V195 AS val > 0, V196 AS val > 0, V197 AS val > 0, V198 AS val > 0, V199 AS val > 0, V200 AS val > 0,
- V201 AS val > 0, V202 AS val > 0, V203 AS val > 0, V204 AS val > 0, V205 AS val > 0, V206 AS val > 0, V207 AS val > 0, V208 AS val > 0, V209 AS val > 0, V210 AS val > 0,
- V211 AS val > 0, V212 AS val > 0, V213 AS val > 0, V214 AS val > 0, V215 AS val > 0, V216 AS val > 0, V217 AS val > 0, V218 AS val > 0, V219 AS val > 0, V220 AS val > 0,
- V221 AS val > 0, V222 AS val > 0, V223 AS val > 0, V224 AS val > 0, V225 AS val > 0, V226 AS val > 0, V227 AS val > 0, V228 AS val > 0, V229 AS val > 0, V230 AS val > 0,
- V231 AS val > 0, V232 AS val > 0, V233 AS val > 0, V234 AS val > 0, V235 AS val > 0, V236 AS val > 0, V237 AS val > 0, V238 AS val > 0, V239 AS val > 0, V240 AS val > 0,
- V241 AS val > 0, V242 AS val > 0, V243 AS val > 0, V244 AS val > 0, V245 AS val > 0, V246 AS val > 0, V247 AS val > 0, V248 AS val > 0, V249 AS val > 0, V250 AS val > 0,
- V251 AS val > 0, V252 AS val > 0, V253 AS val > 0
-);
- count
--------
- 0
- 0
-(2 rows)
-
--- Expected: Success - unused DEFINE variables are filtered out
--- Test: 251 variables in PATTERN, 252 in DEFINE (boundary - should succeed)
+ERROR: DEFINE variable "b" is not used in PATTERN
+LINE 7: B AS TRUE
+ ^
+-- Expected: Error - B is not used in PATTERN
+-- Test: 251 variables in PATTERN and DEFINE (boundary - should succeed)
SELECT COUNT(*) OVER w FROM rpr_errors
WINDOW w AS (
ORDER BY id
@@ -5759,7 +5709,7 @@ WINDOW w AS (
V221 AS val > 0, V222 AS val > 0, V223 AS val > 0, V224 AS val > 0, V225 AS val > 0, V226 AS val > 0, V227 AS val > 0, V228 AS val > 0, V229 AS val > 0, V230 AS val > 0,
V231 AS val > 0, V232 AS val > 0, V233 AS val > 0, V234 AS val > 0, V235 AS val > 0, V236 AS val > 0, V237 AS val > 0, V238 AS val > 0, V239 AS val > 0, V240 AS val > 0,
V241 AS val > 0, V242 AS val > 0, V243 AS val > 0, V244 AS val > 0, V245 AS val > 0, V246 AS val > 0, V247 AS val > 0, V248 AS val > 0, V249 AS val > 0, V250 AS val > 0,
- V251 AS val > 0, V252 AS val > 0
+ V251 AS val > 0
);
count
-------
@@ -5767,7 +5717,7 @@ WINDOW w AS (
0
(2 rows)
--- Expected: Success - unused DEFINE variables are filtered out
+-- Expected: Success - exactly at RPR_VARID_MAX boundary
-- Test: 252 variables in PATTERN, 251 in DEFINE (exceeds limit with implicit TRUE)
SELECT COUNT(*) OVER w FROM rpr_errors
WINDOW w AS (
diff --git a/src/test/regress/sql/rpr_base.sql b/src/test/regress/sql/rpr_base.sql
index e54d54e400a..cf6c062ae85 100644
--- a/src/test/regress/sql/rpr_base.sql
+++ b/src/test/regress/sql/rpr_base.sql
@@ -3639,7 +3639,7 @@ DROP TABLE rpr_stress;
CREATE TABLE rpr_errors (id INT, val INT);
INSERT INTO rpr_errors VALUES (1, 10), (2, 20);
--- Test: PATTERN variable without DEFINE (A), DEFINE variable not in PATTERN (B)
+-- Test: DEFINE variable not in PATTERN (error)
SELECT id, val, COUNT(*) OVER w FROM rpr_errors
WINDOW w AS (
ORDER BY id
@@ -3648,45 +3648,9 @@ WINDOW w AS (
DEFINE
B AS TRUE
);
--- Expected: Success - A is implicitly TRUE, B is filtered out
+-- Expected: Error - B is not used in PATTERN
--- Test: 3 variables in PATTERN, 253 in DEFINE (DEFINE filtering test)
-SELECT COUNT(*) OVER w FROM rpr_errors
-WINDOW w AS (
- ORDER BY id
- ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
- PATTERN (V1 V2 V3)
- DEFINE
- V1 AS val > 0, V2 AS val > 0, V3 AS val > 0, V4 AS val > 0, V5 AS val > 0, V6 AS val > 0, V7 AS val > 0, V8 AS val > 0, V9 AS val > 0, V10 AS val > 0,
- V11 AS val > 0, V12 AS val > 0, V13 AS val > 0, V14 AS val > 0, V15 AS val > 0, V16 AS val > 0, V17 AS val > 0, V18 AS val > 0, V19 AS val > 0, V20 AS val > 0,
- V21 AS val > 0, V22 AS val > 0, V23 AS val > 0, V24 AS val > 0, V25 AS val > 0, V26 AS val > 0, V27 AS val > 0, V28 AS val > 0, V29 AS val > 0, V30 AS val > 0,
- V31 AS val > 0, V32 AS val > 0, V33 AS val > 0, V34 AS val > 0, V35 AS val > 0, V36 AS val > 0, V37 AS val > 0, V38 AS val > 0, V39 AS val > 0, V40 AS val > 0,
- V41 AS val > 0, V42 AS val > 0, V43 AS val > 0, V44 AS val > 0, V45 AS val > 0, V46 AS val > 0, V47 AS val > 0, V48 AS val > 0, V49 AS val > 0, V50 AS val > 0,
- V51 AS val > 0, V52 AS val > 0, V53 AS val > 0, V54 AS val > 0, V55 AS val > 0, V56 AS val > 0, V57 AS val > 0, V58 AS val > 0, V59 AS val > 0, V60 AS val > 0,
- V61 AS val > 0, V62 AS val > 0, V63 AS val > 0, V64 AS val > 0, V65 AS val > 0, V66 AS val > 0, V67 AS val > 0, V68 AS val > 0, V69 AS val > 0, V70 AS val > 0,
- V71 AS val > 0, V72 AS val > 0, V73 AS val > 0, V74 AS val > 0, V75 AS val > 0, V76 AS val > 0, V77 AS val > 0, V78 AS val > 0, V79 AS val > 0, V80 AS val > 0,
- V81 AS val > 0, V82 AS val > 0, V83 AS val > 0, V84 AS val > 0, V85 AS val > 0, V86 AS val > 0, V87 AS val > 0, V88 AS val > 0, V89 AS val > 0, V90 AS val > 0,
- V91 AS val > 0, V92 AS val > 0, V93 AS val > 0, V94 AS val > 0, V95 AS val > 0, V96 AS val > 0, V97 AS val > 0, V98 AS val > 0, V99 AS val > 0, V100 AS val > 0,
- V101 AS val > 0, V102 AS val > 0, V103 AS val > 0, V104 AS val > 0, V105 AS val > 0, V106 AS val > 0, V107 AS val > 0, V108 AS val > 0, V109 AS val > 0, V110 AS val > 0,
- V111 AS val > 0, V112 AS val > 0, V113 AS val > 0, V114 AS val > 0, V115 AS val > 0, V116 AS val > 0, V117 AS val > 0, V118 AS val > 0, V119 AS val > 0, V120 AS val > 0,
- V121 AS val > 0, V122 AS val > 0, V123 AS val > 0, V124 AS val > 0, V125 AS val > 0, V126 AS val > 0, V127 AS val > 0, V128 AS val > 0, V129 AS val > 0, V130 AS val > 0,
- V131 AS val > 0, V132 AS val > 0, V133 AS val > 0, V134 AS val > 0, V135 AS val > 0, V136 AS val > 0, V137 AS val > 0, V138 AS val > 0, V139 AS val > 0, V140 AS val > 0,
- V141 AS val > 0, V142 AS val > 0, V143 AS val > 0, V144 AS val > 0, V145 AS val > 0, V146 AS val > 0, V147 AS val > 0, V148 AS val > 0, V149 AS val > 0, V150 AS val > 0,
- V151 AS val > 0, V152 AS val > 0, V153 AS val > 0, V154 AS val > 0, V155 AS val > 0, V156 AS val > 0, V157 AS val > 0, V158 AS val > 0, V159 AS val > 0, V160 AS val > 0,
- V161 AS val > 0, V162 AS val > 0, V163 AS val > 0, V164 AS val > 0, V165 AS val > 0, V166 AS val > 0, V167 AS val > 0, V168 AS val > 0, V169 AS val > 0, V170 AS val > 0,
- V171 AS val > 0, V172 AS val > 0, V173 AS val > 0, V174 AS val > 0, V175 AS val > 0, V176 AS val > 0, V177 AS val > 0, V178 AS val > 0, V179 AS val > 0, V180 AS val > 0,
- V181 AS val > 0, V182 AS val > 0, V183 AS val > 0, V184 AS val > 0, V185 AS val > 0, V186 AS val > 0, V187 AS val > 0, V188 AS val > 0, V189 AS val > 0, V190 AS val > 0,
- V191 AS val > 0, V192 AS val > 0, V193 AS val > 0, V194 AS val > 0, V195 AS val > 0, V196 AS val > 0, V197 AS val > 0, V198 AS val > 0, V199 AS val > 0, V200 AS val > 0,
- V201 AS val > 0, V202 AS val > 0, V203 AS val > 0, V204 AS val > 0, V205 AS val > 0, V206 AS val > 0, V207 AS val > 0, V208 AS val > 0, V209 AS val > 0, V210 AS val > 0,
- V211 AS val > 0, V212 AS val > 0, V213 AS val > 0, V214 AS val > 0, V215 AS val > 0, V216 AS val > 0, V217 AS val > 0, V218 AS val > 0, V219 AS val > 0, V220 AS val > 0,
- V221 AS val > 0, V222 AS val > 0, V223 AS val > 0, V224 AS val > 0, V225 AS val > 0, V226 AS val > 0, V227 AS val > 0, V228 AS val > 0, V229 AS val > 0, V230 AS val > 0,
- V231 AS val > 0, V232 AS val > 0, V233 AS val > 0, V234 AS val > 0, V235 AS val > 0, V236 AS val > 0, V237 AS val > 0, V238 AS val > 0, V239 AS val > 0, V240 AS val > 0,
- V241 AS val > 0, V242 AS val > 0, V243 AS val > 0, V244 AS val > 0, V245 AS val > 0, V246 AS val > 0, V247 AS val > 0, V248 AS val > 0, V249 AS val > 0, V250 AS val > 0,
- V251 AS val > 0, V252 AS val > 0, V253 AS val > 0
-);
--- Expected: Success - unused DEFINE variables are filtered out
-
--- Test: 251 variables in PATTERN, 252 in DEFINE (boundary - should succeed)
+-- Test: 251 variables in PATTERN and DEFINE (boundary - should succeed)
SELECT COUNT(*) OVER w FROM rpr_errors
WINDOW w AS (
ORDER BY id
@@ -3718,9 +3682,9 @@ WINDOW w AS (
V221 AS val > 0, V222 AS val > 0, V223 AS val > 0, V224 AS val > 0, V225 AS val > 0, V226 AS val > 0, V227 AS val > 0, V228 AS val > 0, V229 AS val > 0, V230 AS val > 0,
V231 AS val > 0, V232 AS val > 0, V233 AS val > 0, V234 AS val > 0, V235 AS val > 0, V236 AS val > 0, V237 AS val > 0, V238 AS val > 0, V239 AS val > 0, V240 AS val > 0,
V241 AS val > 0, V242 AS val > 0, V243 AS val > 0, V244 AS val > 0, V245 AS val > 0, V246 AS val > 0, V247 AS val > 0, V248 AS val > 0, V249 AS val > 0, V250 AS val > 0,
- V251 AS val > 0, V252 AS val > 0
+ V251 AS val > 0
);
--- Expected: Success - unused DEFINE variables are filtered out
+-- Expected: Success - exactly at RPR_VARID_MAX boundary
-- Test: 252 variables in PATTERN, 251 in DEFINE (exceeds limit with implicit TRUE)
SELECT COUNT(*) OVER w FROM rpr_errors
--
2.50.1 (Apple Git-155)
From c4943f2f4fbe264238b806e49e3ab486e15534dc Mon Sep 17 00:00:00 2001
From: Henson Choi <[email protected]>
Date: Fri, 20 Mar 2026 20:45:20 +0900
Subject: [PATCH] Clarify RPR documentation in advanced.sgml
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
Make the absorption optimization paragraph explicitly reference the
O(n²) complexity it mitigates, rather than using an ambiguous "this"
that could be misread as referring to the pattern simplification
paragraph immediately above.
Also clarify the aggregate behavior description for non-starting rows:
replace the vague "NULL or 0 depending on its aggregation definition"
with concrete examples (count() returns 0, sum() returns NULL).
---
doc/src/sgml/advanced.sgml | 12 +++++++-----
1 file changed, 7 insertions(+), 5 deletions(-)
diff --git a/doc/src/sgml/advanced.sgml b/doc/src/sgml/advanced.sgml
index 3e696eefc66..1336f2daa14 100644
--- a/doc/src/sgml/advanced.sgml
+++ b/doc/src/sgml/advanced.sgml
@@ -585,10 +585,11 @@ DEFINE
rows which satisfies the PATTERN is found, in the starting row all columns
or functions are shown in the target list. Note that aggregations only
look into the matched rows, rather than the whole frame. On the second or
- subsequent rows all window functions are shown as NULL. Aggregates are
- NULL or 0 depending on its aggregation definition. A count() aggregate
- shows 0. For rows that do not match on the PATTERN, columns are shown AS
- NULL too. Example of a <literal>SELECT</literal> using
+ subsequent rows all window functions are shown as NULL. Aggregates on
+ non-starting rows return their initial value: for example,
+ <function>count()</function> returns 0 and <function>sum()</function>
+ returns NULL. For rows that do not match the PATTERN, columns are shown
+ as NULL too. Example of a <literal>SELECT</literal> using
the <literal>DEFINE</literal> and <literal>PATTERN</literal> clause is as
follows.
@@ -653,7 +654,8 @@ FROM stock
</para>
<para>
- To mitigate this, <productname>PostgreSQL</productname> employs
+ To mitigate the O(n<superscript>2</superscript>) complexity described
+ above, <productname>PostgreSQL</productname> also employs
a context absorption optimization. When a pattern starts with a greedy
unbounded element, newer matching contexts cannot produce longer matches
than older contexts. By detecting and eliminating these redundant
--
2.50.1 (Apple Git-155)
From ce9d9867146c9d1fade273ef5b3a9fe15279200c Mon Sep 17 00:00:00 2001
From: Henson Choi <[email protected]>
Date: Fri, 20 Mar 2026 21:15:03 +0900
Subject: [PATCH] Fix typos in RPR comments and parser README
Fix "do lopp" to "do loop" and "successfullt" to "successfully" in
nodeWindowAgg.c. Also fix tab-space mix for the parse_rpr.c entry
in parser/README.
---
src/backend/executor/nodeWindowAgg.c | 7 ++++---
src/backend/parser/README | 2 +-
2 files changed, 5 insertions(+), 4 deletions(-)
diff --git a/src/backend/executor/nodeWindowAgg.c b/src/backend/executor/nodeWindowAgg.c
index d144fa39375..942f071b457 100644
--- a/src/backend/executor/nodeWindowAgg.c
+++ b/src/backend/executor/nodeWindowAgg.c
@@ -3729,7 +3729,7 @@ ignorenulls_getfuncarginframe(WindowObject winobj, int argno,
* contiguous. So we can do the check followings safely. Note,
* however, it is possible that a row is out of reduced frame if
* there's a NULL in the middle. So we need to check it in the
- * following do lopp.
+ * following do loop.
*/
if (seektype == WINDOW_SEEK_HEAD && relpos >= num_reduced_frame)
goto out_of_frame;
@@ -4704,7 +4704,8 @@ WinGetFuncArgInFrame(WindowObject winobj, int argno,
/*
* When RPR is active, the reduced frame changes row by row, so we must
- * not advance the mark — doing so would prevent revisiting earlier rows.
+ * not advance the mark — doing so would prevent revisiting earlier
+ * rows.
*/
if (rpr_is_defined(winstate))
set_mark = false;
@@ -4739,7 +4740,7 @@ WinGetFuncArgInFrame(WindowObject winobj, int argno,
* isout: output argument, set to indicate whether target row position
* is out of frame (can pass NULL if caller doesn't care about this)
*
- * Returns 0 if we successfullt got the slot. false if out of frame.
+ * Returns 0 if we successfully got the slot. false if out of frame.
* (also isout is set)
*/
static int
diff --git a/src/backend/parser/README b/src/backend/parser/README
index 2baffa9517e..22a5e91c8cf 100644
--- a/src/backend/parser/README
+++ b/src/backend/parser/README
@@ -26,7 +26,7 @@ parse_node.c create nodes for various structures
parse_oper.c handle operators in expressions
parse_param.c handle Params (for the cases used in the core backend)
parse_relation.c support routines for tables and column handling
-parse_rpr.c handle Row Pattern Recognition
+parse_rpr.c handle Row Pattern Recognition
parse_target.c handle the result list of the query
parse_type.c support routines for data type handling
parse_utilcmd.c parse analysis for utility commands (done at execution time)
--
2.50.1 (Apple Git-155)
From 7b9d7fb735a467ff391898ed4ebc11bd6ef315fa Mon Sep 17 00:00:00 2001
From: Henson Choi <[email protected]>
Date: Fri, 20 Mar 2026 21:21:58 +0900
Subject: [PATCH] Add clarifying comments for excludeLocation and empty
quantifier in gram.y
Add comments explaining that excludeLocation is set to -1 when no
EXCLUDE clause is present (opt_window_exclusion_clause returns 0),
and that the EMPTY quantifier rule's @$ location is unused since
min=max=1 never produces an error.
---
src/backend/parser/gram.y | 7 ++++++-
1 file changed, 6 insertions(+), 1 deletion(-)
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 9dcac63bdc7..6d390258532 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -16757,6 +16757,7 @@ opt_frame_clause:
n->frameOptions |= FRAMEOPTION_NONDEFAULT | FRAMEOPTION_RANGE;
n->frameOptions |= $3;
n->frameLocation = @1;
+ /* -1 when no EXCLUDE clause (opt_window_exclusion_clause returns 0) */
n->excludeLocation = ($3 != 0) ? @3 : -1;
$$ = n;
}
@@ -16767,6 +16768,7 @@ opt_frame_clause:
n->frameOptions |= FRAMEOPTION_NONDEFAULT | FRAMEOPTION_ROWS;
n->frameOptions |= $3;
n->frameLocation = @1;
+ /* -1 when no EXCLUDE clause (opt_window_exclusion_clause returns 0) */
n->excludeLocation = ($3 != 0) ? @3 : -1;
$$ = n;
}
@@ -16777,6 +16779,7 @@ opt_frame_clause:
n->frameOptions |= FRAMEOPTION_NONDEFAULT | FRAMEOPTION_GROUPS;
n->frameOptions |= $3;
n->frameLocation = @1;
+ /* -1 when no EXCLUDE clause (opt_window_exclusion_clause returns 0) */
n->excludeLocation = ($3 != 0) ? @3 : -1;
$$ = n;
}
@@ -17069,7 +17072,9 @@ row_pattern_primary:
;
row_pattern_quantifier_opt:
- /* EMPTY */ { $$ = (Node *) makeRPRQuantifier(1, 1, -1, @$, yyscanner); }
+ /* EMPTY - no quantifier means exactly once; @$ is unused since
+ * min=max=1 never produces an error */
+ { $$ = (Node *) makeRPRQuantifier(1, 1, -1, @$, yyscanner); }
| '*' { $$ = (Node *) makeRPRQuantifier(0, INT_MAX, -1, @1, yyscanner); }
| '+' { $$ = (Node *) makeRPRQuantifier(1, INT_MAX, -1, @1, yyscanner); }
| Op
--
2.50.1 (Apple Git-155)
From f7d0cd30573e7ca2ab72c80a4ed6537b5451cdb4 Mon Sep 17 00:00:00 2001
From: Henson Choi <[email protected]>
Date: Fri, 20 Mar 2026 21:27:33 +0900
Subject: [PATCH] Clarify RPR_VARID_MAX definition in rpr.h
RPR_VARID_MAX = 251 allows varId 0 through 250, giving a maximum of
251 unique pattern variables. Values from 252 onward are reserved for
control elements (BEGIN, END, ALT, FIN). Add a comment explaining this
layout.
---
src/include/optimizer/rpr.h | 6 +++++-
1 file changed, 5 insertions(+), 1 deletion(-)
diff --git a/src/include/optimizer/rpr.h b/src/include/optimizer/rpr.h
index f93a128096b..e78092678bb 100644
--- a/src/include/optimizer/rpr.h
+++ b/src/include/optimizer/rpr.h
@@ -17,7 +17,11 @@
#include "nodes/plannodes.h"
/* Limits and special values */
-#define RPR_VARID_MAX 251 /* max pattern variables: 251 */
+/*
+ * Maximum number of unique pattern variables (varId 0 to RPR_VARID_MAX - 1).
+ * Values from RPR_VARID_BEGIN (252) onward are reserved for control elements.
+ */
+#define RPR_VARID_MAX 251
#define RPR_QUANTITY_INF INT32_MAX /* unbounded quantifier */
#define RPR_COUNT_MAX INT32_MAX /* max runtime count (NFA state) */
#define RPR_ELEMIDX_MAX INT16_MAX /* max pattern elements */
--
2.50.1 (Apple Git-155)
From 0e944668568a73c3387636e29dc52bd056907437 Mon Sep 17 00:00:00 2001
From: Henson Choi <[email protected]>
Date: Fri, 20 Mar 2026 21:40:05 +0900
Subject: [PATCH] Move local variables to function scope in
row_is_in_reduced_frame
Variables i and num_reduced_rows were declared inside the switch block
before the first case label. Move them to the function top to follow
PostgreSQL coding conventions.
---
src/backend/executor/nodeWindowAgg.c | 5 ++---
1 file changed, 2 insertions(+), 3 deletions(-)
diff --git a/src/backend/executor/nodeWindowAgg.c b/src/backend/executor/nodeWindowAgg.c
index 942f071b457..6575cf9dd96 100644
--- a/src/backend/executor/nodeWindowAgg.c
+++ b/src/backend/executor/nodeWindowAgg.c
@@ -3969,6 +3969,8 @@ row_is_in_reduced_frame(WindowObject winobj, int64 pos)
WindowAggState *winstate = winobj->winstate;
int state;
int rtn;
+ int64 i;
+ int num_reduced_rows;
if (!rpr_is_defined(winstate))
{
@@ -3996,9 +3998,6 @@ row_is_in_reduced_frame(WindowObject winobj, int64 pos)
switch (state)
{
- int64 i;
- int num_reduced_rows;
-
case RF_FRAME_HEAD:
num_reduced_rows = 1;
for (i = pos + 1;
--
2.50.1 (Apple Git-155)
From 6fbd1e03665f9c65d20d8159cd2923e46f25bff9 Mon Sep 17 00:00:00 2001
From: Henson Choi <[email protected]>
Date: Fri, 20 Mar 2026 21:48:36 +0900
Subject: [PATCH] Reset reduced_frame_map pointer in release_partition
After MemoryContextReset(partcontext), reduced_frame_map becomes a
dangling pointer. Set it to NULL and reset alloc_sz to zero so that
create_reduced_frame_map starts fresh for the next partition.
---
src/backend/executor/nodeWindowAgg.c | 4 ++++
1 file changed, 4 insertions(+)
diff --git a/src/backend/executor/nodeWindowAgg.c b/src/backend/executor/nodeWindowAgg.c
index 6575cf9dd96..f1f9d60b39d 100644
--- a/src/backend/executor/nodeWindowAgg.c
+++ b/src/backend/executor/nodeWindowAgg.c
@@ -1580,6 +1580,10 @@ release_partition(WindowAggState *winstate)
winstate->partition_spooled = false;
winstate->next_partition = true;
+ /* Reset RPR reduced frame map */
+ winstate->reduced_frame_map = NULL;
+ winstate->alloc_sz = 0;
+
/* Reset NFA state for new partition */
winstate->nfaContext = NULL;
winstate->nfaContextTail = NULL;
--
2.50.1 (Apple Git-155)
From a224ed18261a09d5b1e65aba3c97fc5741234589 Mon Sep 17 00:00:00 2001
From: Henson Choi <[email protected]>
Date: Fri, 20 Mar 2026 22:11:30 +0900
Subject: [PATCH] Remove redundant list manipulation in nfa_add_matched_state
nfa_add_matched_state() manually unlinked pruned contexts before
calling ExecRPRFreeContext(), which internally calls nfa_unlink_context()
to perform the same list operations. Remove the manual forward-link
update and the post-loop tail fixup, letting nfa_unlink_context()
handle forward link, backward link, and tail update consistently.
---
src/backend/executor/execRPR.c | 6 +-----
1 file changed, 1 insertion(+), 5 deletions(-)
diff --git a/src/backend/executor/execRPR.c b/src/backend/executor/execRPR.c
index a0a462256ad..bab5257f68f 100644
--- a/src/backend/executor/execRPR.c
+++ b/src/backend/executor/execRPR.c
@@ -1808,14 +1808,12 @@ nfa_add_matched_state(WindowAggState *winstate, RPRNFAContext *ctx,
/* Prune contexts that started within this match's range */
if (winstate->rpSkipTo == ST_PAST_LAST_ROW)
{
- RPRNFAContext *nextCtx;
int64 skippedLen;
while (ctx->next != NULL &&
ctx->next->matchStartRow <= matchEndRow)
{
- nextCtx = ctx->next;
- ctx->next = ctx->next->next;
+ RPRNFAContext *nextCtx = ctx->next;
Assert(nextCtx->lastProcessedRow >= nextCtx->matchStartRow);
skippedLen = nextCtx->lastProcessedRow - nextCtx->matchStartRow + 1;
@@ -1823,8 +1821,6 @@ nfa_add_matched_state(WindowAggState *winstate, RPRNFAContext *ctx,
ExecRPRFreeContext(winstate, nextCtx);
}
- if (ctx->next == NULL)
- winstate->nfaContextTail = ctx;
}
}
--
2.50.1 (Apple Git-155)
From f22f8e90163e89bbb235a363db3cfcac5b95f522 Mon Sep 17 00:00:00 2001
From: Henson Choi <[email protected]>
Date: Fri, 20 Mar 2026 23:47:12 +0900
Subject: [PATCH] Implement 1-slot PREV/NEXT navigation for RPR
Add PREV(value [, offset]) and NEXT(value [, offset]) navigation functions
for use in the DEFINE clause of row pattern recognition.
These functions return the column value at a row offset rows before/after
the current row within the window frame, returning NULL if the target row
is outside the frame. The offset defaults to 1 if omitted; offset=0 refers
to the current row itself; NULL offset returns NULL.
Implementation adds nav_slot and nav_null_slot to WindowAggState to fetch
the target row without disturbing the main evaluation slot, along with a
dedicated nav_winobj for navigation reads. The RPRNavExpr node carries
the offset expression and direction (PREV/NEXT); it is evaluated via a
new EEOP_RPR_NAV expression step in the expression interpreter (and
corresponding LLVM JIT path).
Parser changes: PREV/NEXT are parsed as function calls and transformed into
RPRNavExpr nodes in transformDefineClause(). Validation rejects nested
PREV/NEXT calls, requires at least one column reference in the first
argument, and ensures the offset argument is a run-time constant.
Documentation updated to reflect the 2-argument form and NULL-offset
behavior.
---
doc/src/sgml/func/func-window.sgml | 22 +-
src/backend/executor/execExpr.c | 57 +++
src/backend/executor/execExprInterp.c | 105 +++++
src/backend/executor/nodeWindowAgg.c | 240 +++++------
src/backend/jit/llvm/llvmjit_expr.c | 29 ++
src/backend/jit/llvm/llvmjit_types.c | 2 +
src/backend/nodes/nodeFuncs.c | 33 ++
src/backend/parser/parse_func.c | 30 +-
src/backend/parser/parse_rpr.c | 79 ++++
src/backend/utils/adt/ruleutils.c | 16 +
src/backend/utils/adt/windowfuncs.c | 52 ++-
src/include/catalog/pg_proc.dat | 6 +
src/include/executor/execExpr.h | 19 +
src/include/executor/nodeWindowAgg.h | 3 +
src/include/nodes/execnodes.h | 10 +-
src/include/nodes/primnodes.h | 31 ++
src/test/regress/expected/rpr.out | 493 +++++++++++++++++++++-
src/test/regress/expected/rpr_explain.out | 114 ++++-
src/test/regress/sql/rpr.sql | 291 ++++++++++++-
src/test/regress/sql/rpr_explain.sql | 84 +++-
src/tools/pgindent/typedefs.list | 3 +-
21 files changed, 1524 insertions(+), 195 deletions(-)
diff --git a/doc/src/sgml/func/func-window.sgml b/doc/src/sgml/func/func-window.sgml
index ae36e0f3135..cb1a852c4d2 100644
--- a/doc/src/sgml/func/func-window.sgml
+++ b/doc/src/sgml/func/func-window.sgml
@@ -304,12 +304,17 @@
<indexterm>
<primary>prev</primary>
</indexterm>
- <function>prev</function> ( <parameter>value</parameter> <type>anyelement</type> )
+ <function>prev</function> ( <parameter>value</parameter> <type>anyelement</type> [, <parameter>offset</parameter> <type>bigint</type> ] )
<returnvalue>anyelement</returnvalue>
</para>
<para>
- Returns the column value at the previous row;
- returns NULL if there is no previous row in the window frame.
+ Returns the column value at the row <parameter>offset</parameter>
+ rows before the current row within the window frame;
+ returns NULL if the target row is outside the window frame.
+ <parameter>offset</parameter> defaults to 1 if omitted.
+ <parameter>offset</parameter> must be a non-negative integer;
+ an offset of 0 refers to the current row itself.
+ If <parameter>offset</parameter> is NULL, NULL is returned.
</para></entry>
</row>
@@ -318,12 +323,17 @@
<indexterm>
<primary>next</primary>
</indexterm>
- <function>next</function> ( <parameter>value</parameter> <type>anyelement</type> )
+ <function>next</function> ( <parameter>value</parameter> <type>anyelement</type> [, <parameter>offset</parameter> <type>bigint</type> ] )
<returnvalue>anyelement</returnvalue>
</para>
<para>
- Returns the column value at the next row;
- returns NULL if there is no next row in the window frame.
+ Returns the column value at the row <parameter>offset</parameter>
+ rows after the current row within the window frame;
+ returns NULL if the target row is outside the window frame.
+ <parameter>offset</parameter> defaults to 1 if omitted.
+ <parameter>offset</parameter> must be a non-negative integer;
+ an offset of 0 refers to the current row itself.
+ If <parameter>offset</parameter> is NULL, NULL is returned.
</para></entry>
</row>
diff --git a/src/backend/executor/execExpr.c b/src/backend/executor/execExpr.c
index 088eca24021..a8136638618 100644
--- a/src/backend/executor/execExpr.c
+++ b/src/backend/executor/execExpr.c
@@ -1189,6 +1189,63 @@ ExecInitExprRec(Expr *node, ExprState *state,
break;
}
+ case T_RPRNavExpr:
+ {
+ /*
+ * RPR navigation functions (PREV/NEXT) are compiled into
+ * EEOP_RPR_NAV_SET / EEOP_RPR_NAV_RESTORE opcodes instead of
+ * a normal function call. The SET opcode swaps
+ * ecxt_outertuple to the target row, the argument expression
+ * is compiled normally (reads from the swapped slot), and the
+ * RESTORE opcode restores the original slot.
+ */
+ RPRNavExpr *nav = (RPRNavExpr *) node;
+ WindowAggState *winstate;
+
+ Assert(state->parent && IsA(state->parent, WindowAggState));
+ winstate = (WindowAggState *) state->parent;
+
+ /* Emit SET opcode: swap slot to target row */
+ scratch.opcode = EEOP_RPR_NAV_SET;
+ scratch.d.rpr_nav.winstate = winstate;
+ scratch.d.rpr_nav.offset = nav->kind; /* RPR_NAV_PREV=-1,
+ * RPR_NAV_NEXT=1 */
+
+ if (nav->offset_arg != NULL)
+ {
+ /*
+ * Allocate storage for the runtime offset value. The
+ * offset expression is compiled below so it runs before
+ * EEOP_RPR_NAV_SET.
+ */
+ Datum *offset_value = palloc_object(Datum);
+ bool *offset_isnull = palloc_object(bool);
+
+ /* Compile the offset expression into the temp storage */
+ ExecInitExprRec(nav->offset_arg, state,
+ offset_value, offset_isnull);
+
+ scratch.d.rpr_nav.offset_value = offset_value;
+ scratch.d.rpr_nav.offset_isnull = offset_isnull;
+ }
+ else
+ {
+ scratch.d.rpr_nav.offset_value = NULL;
+ scratch.d.rpr_nav.offset_isnull = NULL;
+ }
+
+ ExprEvalPushStep(state, &scratch);
+
+ /* Compile the argument expression normally */
+ ExecInitExprRec(nav->arg, state, resv, resnull);
+
+ /* Emit RESTORE opcode: restore original slot */
+ scratch.opcode = EEOP_RPR_NAV_RESTORE;
+ scratch.d.rpr_nav.winstate = winstate;
+ ExprEvalPushStep(state, &scratch);
+ break;
+ }
+
case T_FuncExpr:
{
FuncExpr *func = (FuncExpr *) node;
diff --git a/src/backend/executor/execExprInterp.c b/src/backend/executor/execExprInterp.c
index 61ff5ddc74c..789b8f0c1c7 100644
--- a/src/backend/executor/execExprInterp.c
+++ b/src/backend/executor/execExprInterp.c
@@ -56,11 +56,13 @@
*/
#include "postgres.h"
+#include "common/int.h"
#include "access/heaptoast.h"
#include "catalog/pg_type.h"
#include "commands/sequence.h"
#include "executor/execExpr.h"
#include "executor/nodeSubplan.h"
+#include "executor/nodeWindowAgg.h"
#include "funcapi.h"
#include "miscadmin.h"
#include "nodes/miscnodes.h"
@@ -576,6 +578,8 @@ ExecInterpExpr(ExprState *state, ExprContext *econtext, bool *isnull)
&&CASE_EEOP_WINDOW_FUNC,
&&CASE_EEOP_MERGE_SUPPORT_FUNC,
&&CASE_EEOP_SUBPLAN,
+ &&CASE_EEOP_RPR_NAV_SET,
+ &&CASE_EEOP_RPR_NAV_RESTORE,
&&CASE_EEOP_AGG_STRICT_DESERIALIZE,
&&CASE_EEOP_AGG_DESERIALIZE,
&&CASE_EEOP_AGG_STRICT_INPUT_CHECK_ARGS,
@@ -2003,6 +2007,24 @@ ExecInterpExpr(ExprState *state, ExprContext *econtext, bool *isnull)
EEO_NEXT();
}
+ /* RPR navigation: swap slot to target row */
+ EEO_CASE(EEOP_RPR_NAV_SET)
+ {
+ ExecEvalRPRNavSet(state, op, econtext);
+ outerslot = econtext->ecxt_outertuple;
+
+ EEO_NEXT();
+ }
+
+ /* RPR navigation: restore slot to original row */
+ EEO_CASE(EEOP_RPR_NAV_RESTORE)
+ {
+ ExecEvalRPRNavRestore(state, op, econtext);
+ outerslot = econtext->ecxt_outertuple;
+
+ EEO_NEXT();
+ }
+
/* evaluate a strict aggregate deserialization function */
EEO_CASE(EEOP_AGG_STRICT_DESERIALIZE)
{
@@ -5918,3 +5940,86 @@ ExecAggPlainTransByRef(AggState *aggstate, AggStatePerTrans pertrans,
MemoryContextSwitchTo(oldContext);
}
+
+/*
+ * Evaluate RPR PREV/NEXT navigation: swap slot to target row.
+ *
+ * Saves the current outertuple into winstate for later restore, computes
+ * the target row position, fetches the corresponding slot from the
+ * tuplestore, and replaces econtext->ecxt_outertuple with it.
+ *
+ * This is called both from the interpreter inline handler and from
+ * JIT-compiled expressions via build_EvalXFunc.
+ */
+void
+ExecEvalRPRNavSet(ExprState *state, ExprEvalStep *op, ExprContext *econtext)
+{
+ WindowAggState *winstate = op->d.rpr_nav.winstate;
+ int64 offset;
+ int64 target_pos;
+ TupleTableSlot *target_slot;
+
+ /* Save current slot for later restore */
+ winstate->nav_saved_outertuple = econtext->ecxt_outertuple;
+
+ /*
+ * Determine the signed offset. For 2-arg PREV/NEXT the offset expression
+ * has already been evaluated into offset_value. NULL or negative offsets
+ * are errors (matching Oracle behavior).
+ */
+ if (op->d.rpr_nav.offset_value != NULL)
+ {
+ int64 raw_offset;
+
+ if (*op->d.rpr_nav.offset_isnull)
+ ereport(ERROR,
+ (errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
+ errmsg("PREV/NEXT offset must not be null")));
+
+ raw_offset = DatumGetInt64(*op->d.rpr_nav.offset_value);
+
+ if (raw_offset < 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("PREV/NEXT offset must not be negative")));
+
+ /* Apply direction sign: PREV subtracts, NEXT adds */
+ offset = raw_offset * op->d.rpr_nav.offset;
+ }
+ else
+ offset = op->d.rpr_nav.offset;
+
+ /*
+ * Calculate target position. On overflow, use -1 so that
+ * ExecRPRNavGetSlot treats it as out of range.
+ */
+ if (pg_add_s64_overflow(winstate->currentpos, offset, &target_pos))
+ target_pos = -1;
+
+ /* Fetch target row slot (returns nav_null_slot if out of range) */
+ target_slot = ExecRPRNavGetSlot(winstate, target_pos);
+
+ /*
+ * Update econtext to point to the target slot. Also decompress the new
+ * slot's attributes since FETCHSOME already ran for the original slot.
+ * The caller (interpreter or JIT) is responsible for updating any local
+ * slot cache (e.g. outerslot) from econtext after we return.
+ */
+ slot_getallattrs(target_slot);
+ econtext->ecxt_outertuple = target_slot;
+}
+
+/*
+ * Evaluate RPR PREV/NEXT navigation: restore slot to original row.
+ *
+ * Restores econtext->ecxt_outertuple from the saved slot in winstate.
+ * The caller is responsible for updating any local slot cache.
+ */
+void
+ExecEvalRPRNavRestore(ExprState *state, ExprEvalStep *op,
+ ExprContext *econtext)
+{
+ WindowAggState *winstate = op->d.rpr_nav.winstate;
+
+ econtext->ecxt_outertuple = winstate->nav_saved_outertuple;
+}
diff --git a/src/backend/executor/nodeWindowAgg.c b/src/backend/executor/nodeWindowAgg.c
index f1f9d60b39d..83377b4f18d 100644
--- a/src/backend/executor/nodeWindowAgg.c
+++ b/src/backend/executor/nodeWindowAgg.c
@@ -53,7 +53,6 @@
#include "utils/acl.h"
#include "utils/builtins.h"
#include "utils/datum.h"
-#include "utils/fmgroids.h"
#include "utils/expandeddatum.h"
#include "utils/lsyscache.h"
#include "utils/memutils.h"
@@ -176,14 +175,6 @@ typedef struct WindowStatePerAggData
bool restart; /* need to restart this agg in this cycle? */
} WindowStatePerAggData;
-/*
- * Structure used by check_rpr_navigation() and rpr_navigation_walker().
- */
-typedef struct NavigationInfo
-{
- bool is_prev; /* true if PREV */
- int num_vars; /* number of var nodes */
-} NavigationInfo;
static void initialize_windowaggregate(WindowAggState *winstate,
WindowStatePerFunc perfuncstate,
@@ -242,9 +233,6 @@ static uint8 get_notnull_info(WindowObject winobj,
int64 pos, int argno);
static void put_notnull_info(WindowObject winobj,
int64 pos, int argno, bool isnull);
-static void attno_map(Node *node);
-static bool attno_map_walker(Node *node, void *context);
-
static bool rpr_is_defined(WindowAggState *winstate);
static int row_is_in_reduced_frame(WindowObject winobj, int64 pos);
@@ -255,9 +243,6 @@ static void register_reduced_frame_map(WindowAggState *winstate, int64 pos,
int val);
static void update_reduced_frame(WindowObject winobj, int64 pos);
-static void check_rpr_navigation(Node *node, bool is_prev);
-static bool rpr_navigation_walker(Node *node, void *context);
-
/* Forward declarations - NFA row evaluation */
static bool nfa_evaluate_row(WindowObject winobj, int64 pos, bool *varMatched);
@@ -1301,6 +1286,21 @@ prepare_tuplestore(WindowAggState *winstate)
}
}
+ /* Create read/mark pointers for RPR navigation if needed */
+ if (winstate->nav_winobj)
+ {
+ /*
+ * Allocate a mark pointer pinned at position 0 so that the tuplestore
+ * never truncates rows that a PREV(expr, N) might need.
+ */
+ winstate->nav_winobj->markptr =
+ tuplestore_alloc_read_pointer(winstate->buffer, 0);
+ winstate->nav_winobj->readptr =
+ tuplestore_alloc_read_pointer(winstate->buffer,
+ EXEC_FLAG_BACKWARD);
+ winstate->nav_winobj->markpos = 0;
+ }
+
/*
* If we are in RANGE or GROUPS mode, then determining frame boundaries
* requires physical access to the frame endpoint rows, except in certain
@@ -1411,6 +1411,13 @@ begin_partition(WindowAggState *winstate)
winstate->aggregatedupto = 0;
}
+ /* reset mark and seek positions for RPR navigation */
+ if (winstate->nav_winobj)
+ {
+ winstate->nav_winobj->markpos = -1;
+ winstate->nav_winobj->seekpos = -1;
+ }
+
/* reset mark and seek positions for each real window function */
for (int i = 0; i < numfuncs; i++)
{
@@ -2756,15 +2763,18 @@ ExecInitWindowAgg(WindowAgg *node, EState *estate, int eflags)
winstate->temp_slot_2 = ExecInitExtraTupleSlot(estate, scanDesc,
&TTSOpsMinimalTuple);
- winstate->prev_slot = ExecInitExtraTupleSlot(estate, scanDesc,
- &TTSOpsMinimalTuple);
+ if (node->rpPattern != NULL)
+ {
+ winstate->nav_slot = ExecInitExtraTupleSlot(estate, scanDesc,
+ &TTSOpsMinimalTuple);
+ winstate->nav_slot_pos = -1;
- winstate->next_slot = ExecInitExtraTupleSlot(estate, scanDesc,
- &TTSOpsMinimalTuple);
+ winstate->nav_null_slot = ExecInitExtraTupleSlot(estate, scanDesc,
+ &TTSOpsMinimalTuple);
+ winstate->nav_null_slot = ExecStoreAllNullTuple(winstate->nav_null_slot);
- winstate->null_slot = ExecInitExtraTupleSlot(estate, scanDesc,
- &TTSOpsMinimalTuple);
- winstate->null_slot = ExecStoreAllNullTuple(winstate->null_slot);
+ winstate->nav_saved_outertuple = NULL;
+ }
/*
* create frame head and tail slots only if needed (must create slots in
@@ -2934,6 +2944,23 @@ ExecInitWindowAgg(WindowAgg *node, EState *estate, int eflags)
winstate->agg_winobj = agg_winobj;
}
+ /*
+ * Set up WindowObject for RPR navigation opcodes. This is separate from
+ * agg_winobj because it needs its own read pointer to avoid interfering
+ * with aggregate processing.
+ */
+ if (node->rpPattern != NULL)
+ {
+ WindowObject nav_winobj = makeNode(WindowObjectData);
+
+ nav_winobj->winstate = winstate;
+ nav_winobj->argstates = NIL;
+ nav_winobj->localmem = NULL;
+ nav_winobj->markptr = -1;
+ nav_winobj->readptr = -1;
+ winstate->nav_winobj = nav_winobj;
+ }
+
/* Set the status to running */
winstate->status = WINDOWAGG_RUN;
@@ -2974,7 +3001,9 @@ ExecInitWindowAgg(WindowAgg *node, EState *estate, int eflags)
if (node->defineClause != NIL)
{
/*
- * Tweak arg var of PREV/NEXT so that it refers to scan/inner slot.
+ * Compile DEFINE clause expressions. PREV/NEXT navigation is handled
+ * by EEOP_RPR_NAV_SET/RESTORE opcodes emitted during ExecInitExpr, so
+ * no varno rewriting is needed here.
*/
foreach(l, node->defineClause)
{
@@ -2991,7 +3020,6 @@ ExecInitWindowAgg(WindowAgg *node, EState *estate, int eflags)
winstate->defineVariableList =
lappend(winstate->defineVariableList,
makeString(pstrdup(name)));
- attno_map((Node *) expr);
exps = ExecInitExpr(expr, (PlanState *) winstate);
winstate->defineClauseList =
lappend(winstate->defineClauseList, exps);
@@ -3026,107 +3054,39 @@ ExecInitWindowAgg(WindowAgg *node, EState *estate, int eflags)
}
/*
- * Rewrite varno of Var nodes that are the argument of PREV/NET so that they
- * see scan tuple (PREV) or inner tuple (NEXT). Also we check the arguments
- * of PREV/NEXT include at least 1 column reference. This is required by the
- * SQL standard.
+ * ExecRPRNavGetSlot
+ *
+ * Fetch tuple at given position for RPR navigation opcodes.
+ * Returns nav_slot with the tuple loaded, or nav_null_slot if out of range.
+ * Called from EEOP_RPR_NAV_SET handler in execExprInterp.c.
*/
-static void
-attno_map(Node *node)
+TupleTableSlot *
+ExecRPRNavGetSlot(WindowAggState *winstate, int64 pos)
{
- (void) expression_tree_walker(node, attno_map_walker, NULL);
-}
+ WindowObject winobj = winstate->nav_winobj;
+ TupleTableSlot *slot = winstate->nav_slot;
-static bool
-attno_map_walker(Node *node, void *context)
-{
- FuncExpr *func;
- int nargs;
- bool is_prev;
+ if (pos < 0)
+ return winstate->nav_null_slot;
- if (node == NULL)
- return false;
+ /*
+ * If nav_slot already holds this position, return it without re-fetching.
+ * This is critical when multiple PREV/NEXT calls in the same expression
+ * navigate to the same row, because re-fetching would free the slot's
+ * tuple memory and invalidate any pass-by-ref Datum pointers from earlier
+ * navigation results.
+ */
+ if (winstate->nav_slot_pos == pos)
+ return slot;
- if (IsA(node, FuncExpr))
+ if (!window_gettupleslot(winobj, pos, slot))
{
- func = (FuncExpr *) node;
-
- if (func->funcid == F_PREV || func->funcid == F_NEXT)
- {
- /*
- * The SQL standard allows to have two more arguments form of
- * PREV/NEXT. But currently we allow only 1 argument form.
- */
- nargs = list_length(func->args);
- if (list_length(func->args) != 1)
- elog(ERROR, "PREV/NEXT must have 1 argument but function %d has %d args",
- func->funcid, nargs);
-
- /*
- * Check expr of PREV/NEXT aruguments and replace varno.
- */
- is_prev = (func->funcid == F_PREV) ? true : false;
- check_rpr_navigation(node, is_prev);
- }
+ winstate->nav_slot_pos = -1;
+ return winstate->nav_null_slot;
}
- return expression_tree_walker(node, attno_map_walker, NULL);
-}
-/*
- * Rewrite varno of Var of RPR navigation operations (PREV/NEXT).
- * If is_prev is true, we take care PREV, otherwise NEXT.
- */
-static void
-check_rpr_navigation(Node *node, bool is_prev)
-{
- NavigationInfo context;
-
- context.is_prev = is_prev;
- context.num_vars = 0;
- (void) expression_tree_walker(node, rpr_navigation_walker, &context);
- if (context.num_vars < 1)
- ereport(ERROR,
- errmsg("row pattern navigation operation's argument must include at least one column reference"));
-}
-
-static bool
-rpr_navigation_walker(Node *node, void *context)
-{
- NavigationInfo *nav = (NavigationInfo *) context;
-
- if (node == NULL)
- return false;
-
- switch (nodeTag(node))
- {
- case T_Var:
- {
- Var *var = (Var *) node;
-
- nav->num_vars++;
-
- if (nav->is_prev)
- {
- /*
- * Rewrite varno from OUTER_VAR to regular var no so that
- * the var references scan tuple.
- */
- var->varno = var->varnosyn;
- }
- else
- var->varno = INNER_VAR;
- }
- break;
- case T_Const:
- case T_FuncExpr:
- case T_OpExpr:
- break;
-
- default:
- ereport(ERROR,
- errmsg("row pattern navigation operation's argument includes unsupported expression"));
- }
- return expression_tree_walker(node, rpr_navigation_walker, context);
+ winstate->nav_slot_pos = pos;
+ return slot;
}
@@ -3187,8 +3147,8 @@ ExecReScanWindowAgg(WindowAggState *node)
ExecClearTuple(node->agg_row_slot);
ExecClearTuple(node->temp_slot_1);
ExecClearTuple(node->temp_slot_2);
- ExecClearTuple(node->prev_slot);
- ExecClearTuple(node->next_slot);
+ if (node->nav_slot)
+ ExecClearTuple(node->nav_slot);
if (node->framehead_slot)
ExecClearTuple(node->framehead_slot);
if (node->frametail_slot)
@@ -4277,6 +4237,10 @@ register_result:
* Returns true if the row exists, false if out of partition.
* If row exists, fills varMatched array.
* varMatched[i] = true if variable i matched at current row.
+ *
+ * Uses 1-slot model: only ecxt_outertuple is set to the current row.
+ * PREV/NEXT navigation is handled by EEOP_RPR_NAV_SET/RESTORE opcodes
+ * during expression evaluation, which temporarily swap the slot.
*/
static bool
nfa_evaluate_row(WindowObject winobj, int64 pos, bool *varMatched)
@@ -4287,37 +4251,25 @@ nfa_evaluate_row(WindowObject winobj, int64 pos, bool *varMatched)
ListCell *lc;
int varIdx = 0;
TupleTableSlot *slot;
+ int64 saved_pos;
- /*
- * Set up slots for current, previous, and next rows. We don't call
- * get_slots() here to avoid recursion through row_is_in_frame ->
- * update_reduced_frame -> ExecRPRProcessRow.
- */
-
- /* Current row -> ecxt_outertuple */
+ /* Fetch current row into temp_slot_1 */
slot = winstate->temp_slot_1;
if (!window_gettupleslot(winobj, pos, slot))
return false; /* No row exists */
+
+ /* Set up 1-slot context: only ecxt_outertuple */
econtext->ecxt_outertuple = slot;
- /* Previous row -> ecxt_scantuple (for PREV) */
- if (pos > 0)
- {
- slot = winstate->prev_slot;
- if (!window_gettupleslot(winobj, pos - 1, slot))
- econtext->ecxt_scantuple = winstate->null_slot;
- else
- econtext->ecxt_scantuple = slot;
- }
- else
- econtext->ecxt_scantuple = winstate->null_slot;
+ /*
+ * Save and set currentpos so that EEOP_RPR_NAV_SET opcodes can calculate
+ * target positions (currentpos +/- offset).
+ */
+ saved_pos = winstate->currentpos;
+ winstate->currentpos = pos;
- /* Next row -> ecxt_innertuple (for NEXT) */
- slot = winstate->next_slot;
- if (!window_gettupleslot(winobj, pos + 1, slot))
- econtext->ecxt_innertuple = winstate->null_slot;
- else
- econtext->ecxt_innertuple = slot;
+ /* Invalidate nav_slot cache so PREV/NEXT re-fetch for new row */
+ winstate->nav_slot_pos = -1;
foreach(lc, winstate->defineClauseList)
{
@@ -4335,6 +4287,8 @@ nfa_evaluate_row(WindowObject winobj, int64 pos, bool *varMatched)
break;
}
+ winstate->currentpos = saved_pos;
+
return true; /* Row exists */
}
diff --git a/src/backend/jit/llvm/llvmjit_expr.c b/src/backend/jit/llvm/llvmjit_expr.c
index 650f1d42a93..150a1a7226c 100644
--- a/src/backend/jit/llvm/llvmjit_expr.c
+++ b/src/backend/jit/llvm/llvmjit_expr.c
@@ -2432,6 +2432,35 @@ llvm_compile_expr(ExprState *state)
LLVMBuildBr(b, opblocks[opno + 1]);
break;
+ case EEOP_RPR_NAV_SET:
+ build_EvalXFunc(b, mod, "ExecEvalRPRNavSet",
+ v_state, op, v_econtext);
+
+ /*
+ * Reload v_outerslot from econtext since the C function
+ * swapped ecxt_outertuple to a different row.
+ */
+ v_outerslot = l_load_struct_gep(b,
+ StructExprContext,
+ v_econtext,
+ FIELDNO_EXPRCONTEXT_OUTERTUPLE,
+ "v_outerslot");
+ LLVMBuildBr(b, opblocks[opno + 1]);
+ break;
+
+ case EEOP_RPR_NAV_RESTORE:
+ build_EvalXFunc(b, mod, "ExecEvalRPRNavRestore",
+ v_state, op, v_econtext);
+
+ /* Reload v_outerslot after restore */
+ v_outerslot = l_load_struct_gep(b,
+ StructExprContext,
+ v_econtext,
+ FIELDNO_EXPRCONTEXT_OUTERTUPLE,
+ "v_outerslot");
+ LLVMBuildBr(b, opblocks[opno + 1]);
+ break;
+
case EEOP_AGG_STRICT_DESERIALIZE:
case EEOP_AGG_DESERIALIZE:
{
diff --git a/src/backend/jit/llvm/llvmjit_types.c b/src/backend/jit/llvm/llvmjit_types.c
index c8a1f841293..e78b31d775f 100644
--- a/src/backend/jit/llvm/llvmjit_types.c
+++ b/src/backend/jit/llvm/llvmjit_types.c
@@ -168,6 +168,8 @@ void *referenced_functions[] =
ExecEvalScalarArrayOp,
ExecEvalHashedScalarArrayOp,
ExecEvalSubPlan,
+ ExecEvalRPRNavSet,
+ ExecEvalRPRNavRestore,
ExecEvalSysVar,
ExecEvalWholeRowVar,
ExecEvalXmlExpr,
diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c
index ddf6812d7b0..0799ca1108f 100644
--- a/src/backend/nodes/nodeFuncs.c
+++ b/src/backend/nodes/nodeFuncs.c
@@ -69,6 +69,9 @@ exprType(const Node *expr)
case T_MergeSupportFunc:
type = ((const MergeSupportFunc *) expr)->msftype;
break;
+ case T_RPRNavExpr:
+ type = ((const RPRNavExpr *) expr)->resulttype;
+ break;
case T_SubscriptingRef:
type = ((const SubscriptingRef *) expr)->refrestype;
break;
@@ -848,6 +851,9 @@ exprCollation(const Node *expr)
case T_MergeSupportFunc:
coll = ((const MergeSupportFunc *) expr)->msfcollid;
break;
+ case T_RPRNavExpr:
+ coll = ((const RPRNavExpr *) expr)->resultcollid;
+ break;
case T_SubscriptingRef:
coll = ((const SubscriptingRef *) expr)->refcollid;
break;
@@ -1146,6 +1152,9 @@ exprSetCollation(Node *expr, Oid collation)
case T_MergeSupportFunc:
((MergeSupportFunc *) expr)->msfcollid = collation;
break;
+ case T_RPRNavExpr:
+ ((RPRNavExpr *) expr)->resultcollid = collation;
+ break;
case T_SubscriptingRef:
((SubscriptingRef *) expr)->refcollid = collation;
break;
@@ -1418,6 +1427,9 @@ exprLocation(const Node *expr)
case T_MergeSupportFunc:
loc = ((const MergeSupportFunc *) expr)->location;
break;
+ case T_RPRNavExpr:
+ loc = ((const RPRNavExpr *) expr)->location;
+ break;
case T_SubscriptingRef:
/* just use container argument's location */
loc = exprLocation((Node *) ((const SubscriptingRef *) expr)->refexpr);
@@ -2178,6 +2190,16 @@ expression_tree_walker_impl(Node *node,
return true;
}
break;
+ case T_RPRNavExpr:
+ {
+ RPRNavExpr *expr = (RPRNavExpr *) node;
+
+ if (WALK(expr->arg))
+ return true;
+ if (expr->offset_arg && WALK(expr->offset_arg))
+ return true;
+ }
+ break;
case T_SubscriptingRef:
{
SubscriptingRef *sbsref = (SubscriptingRef *) node;
@@ -3081,6 +3103,17 @@ expression_tree_mutator_impl(Node *node,
return (Node *) newnode;
}
break;
+ case T_RPRNavExpr:
+ {
+ RPRNavExpr *nav = (RPRNavExpr *) node;
+ RPRNavExpr *newnode;
+
+ FLATCOPY(newnode, nav, RPRNavExpr);
+ MUTATE(newnode->arg, nav->arg, Expr *);
+ MUTATE(newnode->offset_arg, nav->offset_arg, Expr *);
+ return (Node *) newnode;
+ }
+ break;
case T_SubscriptingRef:
{
SubscriptingRef *sbsref = (SubscriptingRef *) node;
diff --git a/src/backend/parser/parse_func.c b/src/backend/parser/parse_func.c
index ba9523ae3d4..c25e2c4d3a7 100644
--- a/src/backend/parser/parse_func.c
+++ b/src/backend/parser/parse_func.c
@@ -760,7 +760,8 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
/* next() and prev() are only allowed in a WINDOW DEFINE clause */
if (fdresult == FUNCDETAIL_NORMAL &&
pstate->p_expr_kind != EXPR_KIND_RPR_DEFINE &&
- (funcid == F_PREV || funcid == F_NEXT))
+ (funcid == F_PREV_ANYELEMENT || funcid == F_NEXT_ANYELEMENT ||
+ funcid == F_PREV_ANYELEMENT_INT8 || funcid == F_NEXT_ANYELEMENT_INT8))
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
errmsg("%s can only be used in a DEFINE clause",
@@ -768,7 +769,32 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs,
parser_errposition(pstate, location)));
/* build the appropriate output structure */
- if (fdresult == FUNCDETAIL_NORMAL || fdresult == FUNCDETAIL_PROCEDURE)
+ if (fdresult == FUNCDETAIL_NORMAL &&
+ (funcid == F_PREV_ANYELEMENT || funcid == F_NEXT_ANYELEMENT ||
+ funcid == F_PREV_ANYELEMENT_INT8 || funcid == F_NEXT_ANYELEMENT_INT8))
+ {
+ /*
+ * PREV() and NEXT() are compiled into EEOP_RPR_NAV_SET /
+ * EEOP_RPR_NAV_RESTORE opcodes instead of a normal function call.
+ * Represent them as RPRNavExpr nodes so that later stages can
+ * identify them without relying on funcid comparisons.
+ */
+ bool is_next = (funcid == F_NEXT_ANYELEMENT ||
+ funcid == F_NEXT_ANYELEMENT_INT8);
+ bool has_offset = (funcid == F_PREV_ANYELEMENT_INT8 ||
+ funcid == F_NEXT_ANYELEMENT_INT8);
+ RPRNavExpr *navexpr = makeNode(RPRNavExpr);
+
+ navexpr->kind = is_next ? RPR_NAV_NEXT : RPR_NAV_PREV;
+ navexpr->arg = (Expr *) linitial(fargs);
+ navexpr->offset_arg = has_offset ? (Expr *) lsecond(fargs) : NULL;
+ navexpr->resulttype = rettype;
+ /* resultcollid will be set by parse_collate.c */
+ navexpr->location = location;
+
+ retval = (Node *) navexpr;
+ }
+ else if (fdresult == FUNCDETAIL_NORMAL || fdresult == FUNCDETAIL_PROCEDURE)
{
FuncExpr *funcexpr = makeNode(FuncExpr);
diff --git a/src/backend/parser/parse_rpr.c b/src/backend/parser/parse_rpr.c
index 55283ab4bbe..7c6001eec9f 100644
--- a/src/backend/parser/parse_rpr.c
+++ b/src/backend/parser/parse_rpr.c
@@ -28,6 +28,7 @@
#include "miscadmin.h"
#include "nodes/makefuncs.h"
#include "nodes/nodeFuncs.h"
+#include "optimizer/optimizer.h"
#include "optimizer/rpr.h"
#include "parser/parse_clause.h"
#include "parser/parse_collate.h"
@@ -41,6 +42,9 @@ static void validateRPRPatternVarCount(ParseState *pstate, RPRPatternNode *node,
List *rpDefs, List **varNames);
static List *transformDefineClause(ParseState *pstate, WindowClause *wc,
WindowDef *windef, List **targetlist);
+static bool contains_rpr_nav_walker(Node *node, void *context);
+static bool contains_column_ref_walker(Node *node, void *context);
+static bool check_rpr_nav_nesting_walker(Node *node, void *context);
/*
* transformRPR
@@ -400,6 +404,10 @@ transformDefineClause(ParseState *pstate, WindowClause *wc, WindowDef *windef,
foreach_ptr(TargetEntry, te, defineClause)
te->expr = (Expr *) coerce_to_boolean(pstate, (Node *) te->expr, "DEFINE");
+ /* check for nested PREV/NEXT and missing column references */
+ foreach_ptr(TargetEntry, te, defineClause)
+ (void) check_rpr_nav_nesting_walker((Node *) te->expr, pstate);
+
/* mark column origins */
markTargetListOrigins(pstate, defineClause);
@@ -408,3 +416,74 @@ transformDefineClause(ParseState *pstate, WindowClause *wc, WindowDef *windef,
return defineClause;
}
+
+/*
+ * contains_rpr_nav_walker
+ * Return true if the expression tree contains a PREV or NEXT call.
+ */
+static bool
+contains_rpr_nav_walker(Node *node, void *context)
+{
+ if (node == NULL)
+ return false;
+ if (IsA(node, RPRNavExpr))
+ return true;
+ return expression_tree_walker(node, contains_rpr_nav_walker, context);
+}
+
+/*
+ * contains_column_ref_walker
+ * Return true if the expression tree contains at least one Var node
+ * (column reference).
+ */
+static bool
+contains_column_ref_walker(Node *node, void *context)
+{
+ if (node == NULL)
+ return false;
+ if (IsA(node, Var))
+ return true;
+ return expression_tree_walker(node, contains_column_ref_walker, context);
+}
+
+/*
+ * check_rpr_nav_nesting_walker
+ * Raise an error if PREV or NEXT appears nested inside another PREV/NEXT,
+ * if the first argument contains no column reference, or if the optional
+ * offset argument is not a run-time constant (i.e., it contains a column
+ * reference).
+ */
+static bool
+check_rpr_nav_nesting_walker(Node *node, void *context)
+{
+ if (node == NULL)
+ return false;
+ if (IsA(node, RPRNavExpr))
+ {
+ RPRNavExpr *nav = (RPRNavExpr *) node;
+
+ if (contains_rpr_nav_walker((Node *) nav->arg, NULL))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("PREV and NEXT cannot be nested"),
+ parser_errposition((ParseState *) context,
+ nav->location)));
+ if (!contains_column_ref_walker((Node *) nav->arg, NULL))
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("argument of row pattern navigation operation must include at least one column reference"),
+ parser_errposition((ParseState *) context,
+ nav->location)));
+ if (nav->offset_arg != NULL &&
+ (contains_column_ref_walker((Node *) nav->offset_arg, NULL) ||
+ contain_volatile_functions((Node *) nav->offset_arg)))
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("PREV/NEXT offset must be a run-time constant"),
+ parser_errposition((ParseState *) context,
+ nav->location)));
+ /* don't recurse into arg; nesting already checked above */
+ return false;
+ }
+ return expression_tree_walker(node, check_rpr_nav_nesting_walker, context);
+}
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 928bed2e9fb..d19f3718b30 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -9581,6 +9581,22 @@ get_rule_expr(Node *node, deparse_context *context,
get_func_expr((FuncExpr *) node, context, showimplicit);
break;
+ case T_RPRNavExpr:
+ {
+ RPRNavExpr *nav = (RPRNavExpr *) node;
+
+ appendStringInfoString(buf,
+ nav->kind == RPR_NAV_PREV ? "PREV(" : "NEXT(");
+ get_rule_expr((Node *) nav->arg, context, showimplicit);
+ if (nav->offset_arg != NULL)
+ {
+ appendStringInfoString(buf, ", ");
+ get_rule_expr((Node *) nav->offset_arg, context, showimplicit);
+ }
+ appendStringInfoChar(buf, ')');
+ }
+ break;
+
case T_NamedArgExpr:
{
NamedArgExpr *na = (NamedArgExpr *) node;
diff --git a/src/backend/utils/adt/windowfuncs.c b/src/backend/utils/adt/windowfuncs.c
index 74ef109f72e..091260d2cce 100644
--- a/src/backend/utils/adt/windowfuncs.c
+++ b/src/backend/utils/adt/windowfuncs.c
@@ -726,22 +726,62 @@ window_nth_value(PG_FUNCTION_ARGS)
/*
* prev
- * Dummy function to invoke RPR's navigation operator "PREV".
- * This is *not* a window function.
+ * Catalog placeholder for RPR's PREV navigation operator.
+ *
+ * The parser transforms prev() calls inside DEFINE into RPRNavExpr nodes,
+ * so this function is never reached during normal RPR execution. It exists
+ * only so that the parser can resolve the function name from pg_proc.
+ * Calls outside DEFINE are rejected by parse_func.c (EXPR_KIND_RPR_DEFINE
+ * check). The error below is a defensive measure in case that check is
+ * bypassed (e.g., direct C-level function invocation).
*/
Datum
window_prev(PG_FUNCTION_ARGS)
{
- PG_RETURN_DATUM(PG_GETARG_DATUM(0));
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("prev() can only be used in a DEFINE clause")));
+ PG_RETURN_NULL(); /* not reached */
}
/*
* next
- * Dummy function to invoke RPR's navigation operation "NEXT".
- * This is *not* a window function.
+ * Catalog placeholder for RPR's NEXT navigation operator.
+ * See window_prev() for details.
*/
Datum
window_next(PG_FUNCTION_ARGS)
{
- PG_RETURN_DATUM(PG_GETARG_DATUM(0));
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("next() can only be used in a DEFINE clause")));
+ PG_RETURN_NULL(); /* not reached */
+}
+
+/*
+ * prev(value, offset)
+ * Catalog placeholder for RPR's PREV navigation operator with offset.
+ * See window_prev() for details.
+ */
+Datum
+window_prev_offset(PG_FUNCTION_ARGS)
+{
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("prev() can only be used in a DEFINE clause")));
+ PG_RETURN_NULL(); /* not reached */
+}
+
+/*
+ * next(value, offset)
+ * Catalog placeholder for RPR's NEXT navigation operator with offset.
+ * See window_prev() for details.
+ */
+Datum
+window_next_offset(PG_FUNCTION_ARGS)
+{
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("next() can only be used in a DEFINE clause")));
+ PG_RETURN_NULL(); /* not reached */
}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 7ddaef3d04a..8496c1babc4 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -10957,9 +10957,15 @@
{ oid => '8126', descr => 'previous value',
proname => 'prev', provolatile => 's', prorettype => 'anyelement',
proargtypes => 'anyelement', prosrc => 'window_prev' },
+{ oid => '8128', descr => 'previous value at offset',
+ proname => 'prev', provolatile => 's', proisstrict => 'f', prorettype => 'anyelement',
+ proargtypes => 'anyelement int8', prosrc => 'window_prev_offset' },
{ oid => '8127', descr => 'next value',
proname => 'next', provolatile => 's', prorettype => 'anyelement',
proargtypes => 'anyelement', prosrc => 'window_next' },
+{ oid => '8129', descr => 'next value at offset',
+ proname => 'next', provolatile => 's', proisstrict => 'f', prorettype => 'anyelement',
+ proargtypes => 'anyelement int8', prosrc => 'window_next_offset' },
# functions for range types
{ oid => '3832', descr => 'I/O',
diff --git a/src/include/executor/execExpr.h b/src/include/executor/execExpr.h
index aa9b361fa31..6067f3b6ff8 100644
--- a/src/include/executor/execExpr.h
+++ b/src/include/executor/execExpr.h
@@ -274,6 +274,10 @@ typedef enum ExprEvalOp
EEOP_MERGE_SUPPORT_FUNC,
EEOP_SUBPLAN,
+ /* row pattern navigation (RPR PREV/NEXT) */
+ EEOP_RPR_NAV_SET,
+ EEOP_RPR_NAV_RESTORE,
+
/* aggregation related nodes */
EEOP_AGG_STRICT_DESERIALIZE,
EEOP_AGG_DESERIALIZE,
@@ -691,6 +695,17 @@ typedef struct ExprEvalStep
SubPlanState *sstate;
} subplan;
+ /* for EEOP_RPR_NAV_SET / EEOP_RPR_NAV_RESTORE */
+ struct
+ {
+ WindowAggState *winstate;
+ int64 offset; /* 1-arg: static signed offset (PREV=-1,
+ * NEXT=+1) */
+ Datum *offset_value; /* 2-arg: runtime offset value, or
+ * NULL */
+ bool *offset_isnull; /* 2-arg: runtime offset null flag */
+ } rpr_nav;
+
/* for EEOP_AGG_*DESERIALIZE */
struct
{
@@ -898,6 +913,10 @@ extern void ExecEvalMergeSupportFunc(ExprState *state, ExprEvalStep *op,
ExprContext *econtext);
extern void ExecEvalSubPlan(ExprState *state, ExprEvalStep *op,
ExprContext *econtext);
+extern void ExecEvalRPRNavSet(ExprState *state, ExprEvalStep *op,
+ ExprContext *econtext);
+extern void ExecEvalRPRNavRestore(ExprState *state, ExprEvalStep *op,
+ ExprContext *econtext);
extern void ExecEvalWholeRowVar(ExprState *state, ExprEvalStep *op,
ExprContext *econtext);
extern void ExecEvalSysVar(ExprState *state, ExprEvalStep *op,
diff --git a/src/include/executor/nodeWindowAgg.h b/src/include/executor/nodeWindowAgg.h
index ada4a1c458c..f6f6645131c 100644
--- a/src/include/executor/nodeWindowAgg.h
+++ b/src/include/executor/nodeWindowAgg.h
@@ -20,4 +20,7 @@ extern WindowAggState *ExecInitWindowAgg(WindowAgg *node, EState *estate, int ef
extern void ExecEndWindowAgg(WindowAggState *node);
extern void ExecReScanWindowAgg(WindowAggState *node);
+/* RPR navigation support for expression evaluation opcodes */
+extern TupleTableSlot *ExecRPRNavGetSlot(WindowAggState *winstate, int64 pos);
+
#endif /* NODEWINDOWAGG_H */
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index 3681d905bde..9c692301313 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -2704,10 +2704,12 @@ typedef struct WindowAggState
TupleTableSlot *temp_slot_1;
TupleTableSlot *temp_slot_2;
- /* temporary slots for RPR */
- TupleTableSlot *prev_slot; /* PREV row navigation operator */
- TupleTableSlot *next_slot; /* NEXT row navigation operator */
- TupleTableSlot *null_slot; /* all NULL slot */
+ /* RPR navigation */
+ struct WindowObjectData *nav_winobj; /* winobj for RPR nav fetch */
+ int64 nav_slot_pos; /* position cached in nav_slot, or -1 */
+ TupleTableSlot *nav_slot; /* slot for PREV/NEXT target row */
+ TupleTableSlot *nav_saved_outertuple; /* saved slot during nav swap */
+ TupleTableSlot *nav_null_slot; /* all NULL slot */
/*
* Each byte corresponds to a row positioned at absolute its pos in
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h
index 384df50c80a..7143570f4e0 100644
--- a/src/include/nodes/primnodes.h
+++ b/src/include/nodes/primnodes.h
@@ -648,6 +648,37 @@ typedef struct WindowFuncRunCondition
Expr *arg;
} WindowFuncRunCondition;
+/*
+ * RPRNavExpr
+ *
+ * Represents a PREV() or NEXT() navigation call in an RPR DEFINE clause.
+ * At expression compile time this is translated into EEOP_RPR_NAV_SET /
+ * EEOP_RPR_NAV_RESTORE opcodes rather than a normal function call.
+ *
+ * kind: RPR_NAV_PREV (-1) or RPR_NAV_NEXT (+1)
+ * arg: the expression to evaluate against the target row
+ * offset_arg: optional explicit offset expression (2-arg form); NULL for
+ * the 1-arg form which uses an implicit offset of 1
+ */
+typedef enum RPRNavKind
+{
+ RPR_NAV_PREV = -1,
+ RPR_NAV_NEXT = 1,
+} RPRNavKind;
+
+typedef struct RPRNavExpr
+{
+ Expr xpr;
+ RPRNavKind kind; /* PREV or NEXT */
+ Expr *arg; /* argument expression */
+ Expr *offset_arg; /* offset expression, or NULL for 1-arg form */
+ Oid resulttype; /* result type (same as arg's type) */
+ /* OID of collation of result */
+ Oid resultcollid pg_node_attr(query_jumble_ignore);
+ /* token location, or -1 if unknown */
+ ParseLoc location;
+} RPRNavExpr;
+
/*
* MergeSupportFunc
*
diff --git a/src/test/regress/expected/rpr.out b/src/test/regress/expected/rpr.out
index e72171050c7..79b5e63a657 100644
--- a/src/test/regress/expected/rpr.out
+++ b/src/test/regress/expected/rpr.out
@@ -1018,6 +1018,497 @@ WINDOW w AS (
5 | 0.1 | 0
(5 rows)
+--
+-- Error cases: PREV/NEXT usage restrictions
+--
+-- PREV outside DEFINE clause
+SELECT prev(price) FROM stock;
+ERROR: prev can only be used in a DEFINE clause
+LINE 1: SELECT prev(price) FROM stock;
+ ^
+-- NEXT outside DEFINE clause
+SELECT next(price) FROM stock;
+ERROR: next can only be used in a DEFINE clause
+LINE 1: SELECT next(price) FROM stock;
+ ^
+-- Nested PREV
+SELECT price FROM stock
+WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (A)
+ DEFINE A AS price > PREV(PREV(price))
+);
+ERROR: PREV and NEXT cannot be nested
+LINE 7: DEFINE A AS price > PREV(PREV(price))
+ ^
+-- Nested NEXT
+SELECT price FROM stock
+WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (A)
+ DEFINE A AS price > NEXT(NEXT(price))
+);
+ERROR: PREV and NEXT cannot be nested
+LINE 7: DEFINE A AS price > NEXT(NEXT(price))
+ ^
+-- PREV nested inside NEXT
+SELECT price FROM stock
+WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (A)
+ DEFINE A AS price > NEXT(PREV(price))
+);
+ERROR: PREV and NEXT cannot be nested
+LINE 7: DEFINE A AS price > NEXT(PREV(price))
+ ^
+-- PREV nested inside expression inside NEXT
+SELECT price FROM stock
+WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (A)
+ DEFINE A AS price > NEXT(price * PREV(price))
+);
+ERROR: PREV and NEXT cannot be nested
+LINE 7: DEFINE A AS price > NEXT(price * PREV(price))
+ ^
+-- Triple nesting: error reported at outermost PREV
+SELECT price FROM stock
+WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (A)
+ DEFINE A AS price > PREV(PREV(PREV(price)))
+);
+ERROR: PREV and NEXT cannot be nested
+LINE 7: DEFINE A AS price > PREV(PREV(PREV(price)))
+ ^
+-- No column reference in PREV/NEXT argument
+-- PREV(1): constant only, no column reference
+SELECT price FROM stock
+WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (A)
+ DEFINE A AS PREV(1) > 0
+);
+ERROR: argument of row pattern navigation operation must include at least one column reference
+LINE 7: DEFINE A AS PREV(1) > 0
+ ^
+-- NEXT(1 + 2): constant expression, no column reference
+SELECT price FROM stock
+WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (A)
+ DEFINE A AS NEXT(1 + 2) > 0
+);
+ERROR: argument of row pattern navigation operation must include at least one column reference
+LINE 7: DEFINE A AS NEXT(1 + 2) > 0
+ ^
+-- 2-arg form: PREV(1, 1): constant expression as first arg
+SELECT price FROM stock
+WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (A)
+ DEFINE A AS PREV(1, 1) > 0
+);
+ERROR: argument of row pattern navigation operation must include at least one column reference
+LINE 7: DEFINE A AS PREV(1, 1) > 0
+ ^
+-- Non-constant offset: column reference as offset
+SELECT price FROM stock
+WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (A)
+ DEFINE A AS PREV(price, price) > 0
+);
+ERROR: PREV/NEXT offset must be a run-time constant
+LINE 7: DEFINE A AS PREV(price, price) > 0
+ ^
+-- Non-constant offset: volatile function as offset
+SELECT price FROM stock
+WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (A)
+ DEFINE A AS PREV(price, random()::int) > 0
+);
+ERROR: PREV/NEXT offset must be a run-time constant
+LINE 7: DEFINE A AS PREV(price, random()::int) > 0
+ ^
+--
+-- 2-arg PREV/NEXT: functional tests
+--
+-- PREV(price, 2): match rows where current price > price 2 rows back
+-- stock: 100, 90, 80, 95, 110
+-- Pattern (A B+): A=any, B where price > PREV(price, 2)
+-- At pos 2 (80): A matches. pos 3 (95): 95 > PREV(95,2)=90 TRUE.
+-- pos 4 (110): 110 > PREV(110,2)=80 TRUE. Match!
+SELECT company, tdate, price,
+ first_value(price) OVER w, last_value(price) OVER w, count(*) OVER w
+FROM stock
+WINDOW w AS (
+ PARTITION BY company ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A B+)
+ DEFINE
+ A AS TRUE,
+ B AS price > PREV(price, 2)
+);
+ company | tdate | price | first_value | last_value | count
+----------+------------+-------+-------------+------------+-------
+ company1 | 07-01-2023 | 100 | | | 0
+ company1 | 07-02-2023 | 200 | 200 | 150 | 2
+ company1 | 07-03-2023 | 150 | | | 0
+ company1 | 07-04-2023 | 140 | | | 0
+ company1 | 07-05-2023 | 150 | | | 0
+ company1 | 07-06-2023 | 90 | | | 0
+ company1 | 07-07-2023 | 110 | 110 | 120 | 3
+ company1 | 07-08-2023 | 130 | | | 0
+ company1 | 07-09-2023 | 120 | | | 0
+ company1 | 07-10-2023 | 130 | | | 0
+ company2 | 07-01-2023 | 50 | | | 0
+ company2 | 07-02-2023 | 2000 | 2000 | 1500 | 2
+ company2 | 07-03-2023 | 1500 | | | 0
+ company2 | 07-04-2023 | 1400 | | | 0
+ company2 | 07-05-2023 | 1500 | | | 0
+ company2 | 07-06-2023 | 60 | | | 0
+ company2 | 07-07-2023 | 1100 | 1100 | 1200 | 3
+ company2 | 07-08-2023 | 1300 | | | 0
+ company2 | 07-09-2023 | 1200 | | | 0
+ company2 | 07-10-2023 | 1300 | | | 0
+(20 rows)
+
+-- NEXT(price, 2): match rows where current price > price 2 rows ahead
+-- pos 0 (100): NEXT(100,2)=80, 100>80 TRUE. pos 1 (90): NEXT(90,2)=95, 90>95 FALSE. Match ends.
+SELECT company, tdate, price,
+ first_value(price) OVER w, last_value(price) OVER w, count(*) OVER w
+FROM stock
+WINDOW w AS (
+ PARTITION BY company ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+)
+ DEFINE A AS price > NEXT(price, 2)
+);
+ company | tdate | price | first_value | last_value | count
+----------+------------+-------+-------------+------------+-------
+ company1 | 07-01-2023 | 100 | | | 0
+ company1 | 07-02-2023 | 200 | 200 | 200 | 1
+ company1 | 07-03-2023 | 150 | | | 0
+ company1 | 07-04-2023 | 140 | 140 | 150 | 2
+ company1 | 07-05-2023 | 150 | | | 0
+ company1 | 07-06-2023 | 90 | | | 0
+ company1 | 07-07-2023 | 110 | | | 0
+ company1 | 07-08-2023 | 130 | | | 0
+ company1 | 07-09-2023 | 120 | | | 0
+ company1 | 07-10-2023 | 130 | | | 0
+ company2 | 07-01-2023 | 50 | | | 0
+ company2 | 07-02-2023 | 2000 | 2000 | 2000 | 1
+ company2 | 07-03-2023 | 1500 | | | 0
+ company2 | 07-04-2023 | 1400 | 1400 | 1500 | 2
+ company2 | 07-05-2023 | 1500 | | | 0
+ company2 | 07-06-2023 | 60 | | | 0
+ company2 | 07-07-2023 | 1100 | | | 0
+ company2 | 07-08-2023 | 1300 | | | 0
+ company2 | 07-09-2023 | 1200 | | | 0
+ company2 | 07-10-2023 | 1300 | | | 0
+(20 rows)
+
+-- Expressions inside PREV/NEXT arg: expr is evaluated on target row
+-- PREV(price - 50, 1): fetches (price - 50) from 1 row back
+SELECT company, tdate, price,
+ first_value(price) OVER w, last_value(price) OVER w, count(*) OVER w
+FROM stock
+WINDOW w AS (
+ PARTITION BY company ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+)
+ DEFINE A AS price > PREV(price - 50, 1)
+);
+ company | tdate | price | first_value | last_value | count
+----------+------------+-------+-------------+------------+-------
+ company1 | 07-01-2023 | 100 | | | 0
+ company1 | 07-02-2023 | 200 | 200 | 200 | 1
+ company1 | 07-03-2023 | 150 | | | 0
+ company1 | 07-04-2023 | 140 | 140 | 150 | 2
+ company1 | 07-05-2023 | 150 | | | 0
+ company1 | 07-06-2023 | 90 | | | 0
+ company1 | 07-07-2023 | 110 | 110 | 130 | 4
+ company1 | 07-08-2023 | 130 | | | 0
+ company1 | 07-09-2023 | 120 | | | 0
+ company1 | 07-10-2023 | 130 | | | 0
+ company2 | 07-01-2023 | 50 | | | 0
+ company2 | 07-02-2023 | 2000 | 2000 | 2000 | 1
+ company2 | 07-03-2023 | 1500 | | | 0
+ company2 | 07-04-2023 | 1400 | | | 0
+ company2 | 07-05-2023 | 1500 | 1500 | 1500 | 1
+ company2 | 07-06-2023 | 60 | | | 0
+ company2 | 07-07-2023 | 1100 | 1100 | 1300 | 2
+ company2 | 07-08-2023 | 1300 | | | 0
+ company2 | 07-09-2023 | 1200 | | | 0
+ company2 | 07-10-2023 | 1300 | 1300 | 1300 | 1
+(20 rows)
+
+-- NEXT(price * 2, 1): fetches (price * 2) from 1 row ahead
+SELECT company, tdate, price,
+ first_value(price) OVER w, last_value(price) OVER w, count(*) OVER w
+FROM stock
+WINDOW w AS (
+ PARTITION BY company ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+)
+ DEFINE A AS price < NEXT(price * 2, 1)
+);
+ company | tdate | price | first_value | last_value | count
+----------+------------+-------+-------------+------------+-------
+ company1 | 07-01-2023 | 100 | 100 | 120 | 9
+ company1 | 07-02-2023 | 200 | | | 0
+ company1 | 07-03-2023 | 150 | | | 0
+ company1 | 07-04-2023 | 140 | | | 0
+ company1 | 07-05-2023 | 150 | | | 0
+ company1 | 07-06-2023 | 90 | | | 0
+ company1 | 07-07-2023 | 110 | | | 0
+ company1 | 07-08-2023 | 130 | | | 0
+ company1 | 07-09-2023 | 120 | | | 0
+ company1 | 07-10-2023 | 130 | | | 0
+ company2 | 07-01-2023 | 50 | 50 | 1400 | 4
+ company2 | 07-02-2023 | 2000 | | | 0
+ company2 | 07-03-2023 | 1500 | | | 0
+ company2 | 07-04-2023 | 1400 | | | 0
+ company2 | 07-05-2023 | 1500 | | | 0
+ company2 | 07-06-2023 | 60 | 60 | 1200 | 4
+ company2 | 07-07-2023 | 1100 | | | 0
+ company2 | 07-08-2023 | 1300 | | | 0
+ company2 | 07-09-2023 | 1200 | | | 0
+ company2 | 07-10-2023 | 1300 | | | 0
+(20 rows)
+
+-- Large offset: PREV(val, 999) on 1000-row series matches only last row
+-- NEXT(val, 999) matches only first row
+SELECT val, first_value(val) OVER w, last_value(val) OVER w, count(*) OVER w
+FROM generate_series(1, 1000) AS t(val)
+WINDOW w AS (
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+)
+ DEFINE A AS PREV(val, 999) = 1
+)
+ORDER BY val DESC LIMIT 3;
+ val | first_value | last_value | count
+------+-------------+------------+-------
+ 1000 | 1000 | 1000 | 1
+ 999 | | | 0
+ 998 | | | 0
+(3 rows)
+
+SELECT val, first_value(val) OVER w, last_value(val) OVER w, count(*) OVER w
+FROM generate_series(1, 1000) AS t(val)
+WINDOW w AS (
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+)
+ DEFINE A AS NEXT(val, 999) = 1000
+)
+LIMIT 3;
+ val | first_value | last_value | count
+-----+-------------+------------+-------
+ 1 | 1 | 1 | 1
+ 2 | | | 0
+ 3 | | | 0
+(3 rows)
+
+-- PREV(price, 0): offset 0 means current row, always equal to price
+-- A+ matches entire partition as one group; count = partition size
+SELECT company, tdate, price,
+ first_value(price) OVER w, last_value(price) OVER w, count(*) OVER w
+FROM stock
+WINDOW w AS (
+ PARTITION BY company ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+)
+ DEFINE A AS PREV(price, 0) = price
+);
+ company | tdate | price | first_value | last_value | count
+----------+------------+-------+-------------+------------+-------
+ company1 | 07-01-2023 | 100 | 100 | 130 | 10
+ company1 | 07-02-2023 | 200 | | | 0
+ company1 | 07-03-2023 | 150 | | | 0
+ company1 | 07-04-2023 | 140 | | | 0
+ company1 | 07-05-2023 | 150 | | | 0
+ company1 | 07-06-2023 | 90 | | | 0
+ company1 | 07-07-2023 | 110 | | | 0
+ company1 | 07-08-2023 | 130 | | | 0
+ company1 | 07-09-2023 | 120 | | | 0
+ company1 | 07-10-2023 | 130 | | | 0
+ company2 | 07-01-2023 | 50 | 50 | 1300 | 10
+ company2 | 07-02-2023 | 2000 | | | 0
+ company2 | 07-03-2023 | 1500 | | | 0
+ company2 | 07-04-2023 | 1400 | | | 0
+ company2 | 07-05-2023 | 1500 | | | 0
+ company2 | 07-06-2023 | 60 | | | 0
+ company2 | 07-07-2023 | 1100 | | | 0
+ company2 | 07-08-2023 | 1300 | | | 0
+ company2 | 07-09-2023 | 1200 | | | 0
+ company2 | 07-10-2023 | 1300 | | | 0
+(20 rows)
+
+-- 2-arg PREV/NEXT outside DEFINE clause
+SELECT prev(price, 2) FROM stock;
+ERROR: prev can only be used in a DEFINE clause
+LINE 1: SELECT prev(price, 2) FROM stock;
+ ^
+SELECT next(price, 2) FROM stock;
+ERROR: next can only be used in a DEFINE clause
+LINE 1: SELECT next(price, 2) FROM stock;
+ ^
+-- 2-arg PREV/NEXT: negative offset
+SELECT company, tdate, price, first_value(price) OVER w
+FROM stock
+WINDOW w AS (
+ PARTITION BY company ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+)
+ DEFINE A AS PREV(price, -1) IS NOT NULL
+);
+ERROR: PREV/NEXT offset must not be negative
+-- 2-arg PREV/NEXT: NULL offset (typed)
+SELECT company, tdate, price, first_value(price) OVER w
+FROM stock
+WINDOW w AS (
+ PARTITION BY company ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+)
+ DEFINE A AS PREV(price, NULL::int8) IS NOT NULL
+);
+ERROR: PREV/NEXT offset must not be null
+-- 2-arg PREV/NEXT: NULL offset (untyped)
+SELECT company, tdate, price, first_value(price) OVER w
+FROM stock
+WINDOW w AS (
+ PARTITION BY company ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+)
+ DEFINE A AS PREV(price, NULL) IS NOT NULL
+);
+ERROR: PREV/NEXT offset must not be null
+-- 2-arg PREV/NEXT: host variable negative and NULL
+PREPARE test_prev_offset(int8) AS
+SELECT company, tdate, price, first_value(price) OVER w
+FROM stock
+WINDOW w AS (
+ PARTITION BY company ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+)
+ DEFINE A AS price > PREV(price, $1)
+);
+EXECUTE test_prev_offset(-1);
+ERROR: PREV/NEXT offset must not be negative
+EXECUTE test_prev_offset(NULL);
+ERROR: PREV/NEXT offset must not be null
+DEALLOCATE test_prev_offset;
+-- 2-arg PREV/NEXT: host variable with expression (0 + $1)
+PREPARE test_prev_offset(int8) AS
+SELECT company, tdate, price, first_value(price) OVER w
+FROM stock
+WINDOW w AS (
+ PARTITION BY company ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+)
+ DEFINE A AS price > PREV(price, 0 + $1)
+);
+EXECUTE test_prev_offset(-1);
+ERROR: PREV/NEXT offset must not be negative
+EXECUTE test_prev_offset(NULL);
+ERROR: PREV/NEXT offset must not be null
+DEALLOCATE test_prev_offset;
+-- 2-arg: two PREV with different offsets in same DEFINE clause
+-- B: price exceeds both 1-back and 2-back values
+SELECT company, tdate, price,
+ first_value(price) OVER w, last_value(price) OVER w, count(*) OVER w
+FROM stock
+WINDOW w AS (
+ PARTITION BY company ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP PAST LAST ROW
+ PATTERN (A B+)
+ DEFINE
+ A AS TRUE,
+ B AS price > PREV(price, 1) AND price > PREV(price, 2)
+);
+ company | tdate | price | first_value | last_value | count
+----------+------------+-------+-------------+------------+-------
+ company1 | 07-01-2023 | 100 | | | 0
+ company1 | 07-02-2023 | 200 | | | 0
+ company1 | 07-03-2023 | 150 | | | 0
+ company1 | 07-04-2023 | 140 | | | 0
+ company1 | 07-05-2023 | 150 | | | 0
+ company1 | 07-06-2023 | 90 | | | 0
+ company1 | 07-07-2023 | 110 | 110 | 130 | 2
+ company1 | 07-08-2023 | 130 | | | 0
+ company1 | 07-09-2023 | 120 | | | 0
+ company1 | 07-10-2023 | 130 | | | 0
+ company2 | 07-01-2023 | 50 | | | 0
+ company2 | 07-02-2023 | 2000 | | | 0
+ company2 | 07-03-2023 | 1500 | | | 0
+ company2 | 07-04-2023 | 1400 | | | 0
+ company2 | 07-05-2023 | 1500 | | | 0
+ company2 | 07-06-2023 | 60 | | | 0
+ company2 | 07-07-2023 | 1100 | 1100 | 1300 | 2
+ company2 | 07-08-2023 | 1300 | | | 0
+ company2 | 07-09-2023 | 1200 | | | 0
+ company2 | 07-10-2023 | 1300 | | | 0
+(20 rows)
+
+-- 2-arg: PREV and NEXT with explicit offsets in same DEFINE clause
+-- A: price exceeds 1-back and is below 1-ahead (ascending interior point)
+SELECT company, tdate, price,
+ first_value(price) OVER w, last_value(price) OVER w, count(*) OVER w
+FROM stock
+WINDOW w AS (
+ PARTITION BY company ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP PAST LAST ROW
+ PATTERN (A+)
+ DEFINE A AS price > PREV(price, 1) AND price < NEXT(price, 1)
+);
+ company | tdate | price | first_value | last_value | count
+----------+------------+-------+-------------+------------+-------
+ company1 | 07-01-2023 | 100 | | | 0
+ company1 | 07-02-2023 | 200 | | | 0
+ company1 | 07-03-2023 | 150 | | | 0
+ company1 | 07-04-2023 | 140 | | | 0
+ company1 | 07-05-2023 | 150 | | | 0
+ company1 | 07-06-2023 | 90 | | | 0
+ company1 | 07-07-2023 | 110 | 110 | 110 | 1
+ company1 | 07-08-2023 | 130 | | | 0
+ company1 | 07-09-2023 | 120 | | | 0
+ company1 | 07-10-2023 | 130 | | | 0
+ company2 | 07-01-2023 | 50 | | | 0
+ company2 | 07-02-2023 | 2000 | | | 0
+ company2 | 07-03-2023 | 1500 | | | 0
+ company2 | 07-04-2023 | 1400 | | | 0
+ company2 | 07-05-2023 | 1500 | | | 0
+ company2 | 07-06-2023 | 60 | | | 0
+ company2 | 07-07-2023 | 1100 | 1100 | 1100 | 1
+ company2 | 07-08-2023 | 1300 | | | 0
+ company2 | 07-09-2023 | 1200 | | | 0
+ company2 | 07-10-2023 | 1300 | | | 0
+(20 rows)
+
--
-- SKIP TO / Backtracking / Frame boundary
--
@@ -1479,7 +1970,7 @@ count(*) OVER w
(14 rows)
-- ReScan test: LATERAL join forces WindowAgg rescan with RPR
--- Tests ExecReScanWindowAgg clearing prev_slot/next_slot
+-- Tests ExecReScanWindowAgg clearing nav_slot
SELECT g.x, sub.*
FROM generate_series(1, 2) g(x),
LATERAL (
diff --git a/src/test/regress/expected/rpr_explain.out b/src/test/regress/expected/rpr_explain.out
index bd345906133..0d2b7550ea8 100644
--- a/src/test/regress/expected/rpr_explain.out
+++ b/src/test/regress/expected/rpr_explain.out
@@ -3646,9 +3646,89 @@ WINDOW w AS (
-> Function Scan on generate_series s (actual rows=30.00 loops=1)
(8 rows)
--- Using NULL comparisons
+-- Using 1-arg PREV (implicit offset 1)
CREATE VIEW rpr_ev83 AS
SELECT count(*) OVER w
+FROM generate_series(1, 30) AS s(v)
+WINDOW w AS (
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP PAST LAST ROW
+ PATTERN (A B+)
+ DEFINE
+ A AS TRUE,
+ B AS v > PREV(v)
+);
+SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev83'), E'\n')) AS line WHERE line ~ 'PATTERN|DEFINE|PREV|NEXT';
+ line
+-------------------------
+ PATTERN (a b+)
+ DEFINE
+ b AS (v > PREV(v)) );
+(3 rows)
+
+-- Using 1-arg NEXT (implicit offset 1)
+CREATE VIEW rpr_ev84 AS
+SELECT count(*) OVER w
+FROM generate_series(1, 30) AS s(v)
+WINDOW w AS (
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP PAST LAST ROW
+ PATTERN (A B+)
+ DEFINE
+ A AS TRUE,
+ B AS v < NEXT(v)
+);
+SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev84'), E'\n')) AS line WHERE line ~ 'PATTERN|DEFINE|PREV|NEXT';
+ line
+-------------------------
+ PATTERN (a b+)
+ DEFINE
+ b AS (v < NEXT(v)) );
+(3 rows)
+
+-- Using 2-arg PREV (explicit offset)
+CREATE VIEW rpr_ev85 AS
+SELECT count(*) OVER w
+FROM generate_series(1, 30) AS s(v)
+WINDOW w AS (
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP PAST LAST ROW
+ PATTERN (A B+)
+ DEFINE
+ A AS TRUE,
+ B AS v > PREV(v, 2)
+);
+SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev85'), E'\n')) AS line WHERE line ~ 'PATTERN|DEFINE|PREV|NEXT';
+ line
+--------------------------------------
+ PATTERN (a b+)
+ DEFINE
+ b AS (v > PREV(v, (2)::bigint)) );
+(3 rows)
+
+-- Using 2-arg NEXT (explicit offset)
+CREATE VIEW rpr_ev86 AS
+SELECT count(*) OVER w
+FROM generate_series(1, 30) AS s(v)
+WINDOW w AS (
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP PAST LAST ROW
+ PATTERN (A B+)
+ DEFINE
+ A AS TRUE,
+ B AS v < NEXT(v, 2)
+);
+SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev86'), E'\n')) AS line WHERE line ~ 'PATTERN|DEFINE|PREV|NEXT';
+ line
+--------------------------------------
+ PATTERN (a b+)
+ DEFINE
+ b AS (v < NEXT(v, (2)::bigint)) );
+(3 rows)
+
+-- Using NULL comparisons
+CREATE VIEW rpr_ev87 AS
+SELECT count(*) OVER w
FROM (
SELECT CASE WHEN v % 5 = 0 THEN NULL ELSE v END AS v
FROM generate_series(1, 30) v
@@ -3659,7 +3739,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_ev83'), E'\n')) AS line WHERE line ~ 'PATTERN';
+SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev87'), E'\n')) AS line WHERE line ~ 'PATTERN';
line
-------------------
PATTERN (a+ b)
@@ -3695,7 +3775,7 @@ WINDOW w AS (
-- Large Scale Statistics Verification
-- ============================================================
-- 500 rows - verify statistics scale correctly
-CREATE VIEW rpr_ev84 AS
+CREATE VIEW rpr_ev88 AS
SELECT count(*) OVER w
FROM generate_series(1, 500) AS s(v)
WINDOW w AS (
@@ -3704,7 +3784,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_ev84'), E'\n')) AS line WHERE line ~ 'PATTERN';
+SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev88'), E'\n')) AS line WHERE line ~ 'PATTERN';
line
---------------------
PATTERN (a+ b c)
@@ -3734,7 +3814,7 @@ WINDOW w AS (
(9 rows)
-- High match count scenario
-CREATE VIEW rpr_ev85 AS
+CREATE VIEW rpr_ev89 AS
SELECT count(*) OVER w
FROM generate_series(1, 500) AS s(v)
WINDOW w AS (
@@ -3743,7 +3823,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_ev85'), E'\n')) AS line WHERE line ~ 'PATTERN';
+SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev89'), E'\n')) AS line WHERE line ~ 'PATTERN';
line
------------------
PATTERN (a b)
@@ -3773,7 +3853,7 @@ WINDOW w AS (
(9 rows)
-- High skip count scenario
-CREATE VIEW rpr_ev86 AS
+CREATE VIEW rpr_ev90 AS
SELECT count(*) OVER w
FROM generate_series(1, 500) AS s(v)
WINDOW w AS (
@@ -3787,7 +3867,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_ev86'), E'\n')) AS line WHERE line ~ 'PATTERN';
+SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev90'), E'\n')) AS line WHERE line ~ 'PATTERN';
line
------------------------
PATTERN (a b c d e)
@@ -3831,17 +3911,17 @@ WINDOW w AS (
-- Test with row_number() as representative case.
--
-- Without RPR: row_number() frame is optimized to ROWS UNBOUNDED PRECEDING
-CREATE VIEW rpr_ev87 AS
+CREATE VIEW rpr_ev91 AS
SELECT row_number() OVER w
FROM generate_series(1, 10) AS s(v)
WINDOW w AS (
ORDER BY v
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
);
-EXPLAIN (COSTS OFF) SELECT * FROM rpr_ev87;
+EXPLAIN (COSTS OFF) SELECT * FROM rpr_ev91;
QUERY PLAN
--------------------------------------------------------------
- Subquery Scan on rpr_ev87
+ Subquery Scan on rpr_ev91
-> WindowAgg
Window: w AS (ORDER BY s.v ROWS UNBOUNDED PRECEDING)
-> Sort
@@ -3850,7 +3930,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM rpr_ev87;
(6 rows)
-- With RPR: frame must remain ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
-CREATE VIEW rpr_ev88 AS
+CREATE VIEW rpr_ev92 AS
SELECT row_number() OVER w
FROM generate_series(1, 10) AS s(v)
WINDOW w AS (
@@ -3861,10 +3941,10 @@ WINDOW w AS (
DEFINE
B AS v > PREV(v)
);
-EXPLAIN (COSTS OFF) SELECT * FROM rpr_ev88;
+EXPLAIN (COSTS OFF) SELECT * FROM rpr_ev92;
QUERY PLAN
--------------------------------------------------------------------------------------
- Subquery Scan on rpr_ev88
+ Subquery Scan on rpr_ev92
-> WindowAgg
Window: w AS (ORDER BY s.v ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
Pattern: a b+
@@ -3877,7 +3957,7 @@ EXPLAIN (COSTS OFF) SELECT * FROM rpr_ev88;
-- Planner optimization: non-RPR and RPR windows that share the same base frame
-- after frame optimization are kept as separate WindowAgg nodes.
--
-CREATE VIEW rpr_ev89 AS
+CREATE VIEW rpr_ev93 AS
SELECT
row_number() OVER w_normal AS rn_normal,
row_number() OVER w_rpr AS rn_rpr
@@ -3890,10 +3970,10 @@ WINDOW
PATTERN (A+)
DEFINE A AS v > 1
);
-EXPLAIN (COSTS OFF) SELECT * FROM rpr_ev89;
+EXPLAIN (COSTS OFF) SELECT * FROM rpr_ev93;
QUERY PLAN
------------------------------------------------------------------------------------------
- Subquery Scan on rpr_ev89
+ Subquery Scan on rpr_ev93
-> WindowAgg
Window: w_rpr AS (ORDER BY s.v ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
Pattern: a+"
diff --git a/src/test/regress/sql/rpr.sql b/src/test/regress/sql/rpr.sql
index 95794d409e1..787ebddcaec 100644
--- a/src/test/regress/sql/rpr.sql
+++ b/src/test/regress/sql/rpr.sql
@@ -440,6 +440,295 @@ WINDOW w AS (
B AS val > PREV(val) * 0.99
);
+--
+-- Error cases: PREV/NEXT usage restrictions
+--
+
+-- PREV outside DEFINE clause
+SELECT prev(price) FROM stock;
+
+-- NEXT outside DEFINE clause
+SELECT next(price) FROM stock;
+
+-- Nested PREV
+SELECT price FROM stock
+WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (A)
+ DEFINE A AS price > PREV(PREV(price))
+);
+
+-- Nested NEXT
+SELECT price FROM stock
+WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (A)
+ DEFINE A AS price > NEXT(NEXT(price))
+);
+
+-- PREV nested inside NEXT
+SELECT price FROM stock
+WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (A)
+ DEFINE A AS price > NEXT(PREV(price))
+);
+
+-- PREV nested inside expression inside NEXT
+SELECT price FROM stock
+WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (A)
+ DEFINE A AS price > NEXT(price * PREV(price))
+);
+
+-- Triple nesting: error reported at outermost PREV
+SELECT price FROM stock
+WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (A)
+ DEFINE A AS price > PREV(PREV(PREV(price)))
+);
+
+-- No column reference in PREV/NEXT argument
+-- PREV(1): constant only, no column reference
+SELECT price FROM stock
+WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (A)
+ DEFINE A AS PREV(1) > 0
+);
+
+-- NEXT(1 + 2): constant expression, no column reference
+SELECT price FROM stock
+WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (A)
+ DEFINE A AS NEXT(1 + 2) > 0
+);
+
+-- 2-arg form: PREV(1, 1): constant expression as first arg
+SELECT price FROM stock
+WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (A)
+ DEFINE A AS PREV(1, 1) > 0
+);
+
+-- Non-constant offset: column reference as offset
+SELECT price FROM stock
+WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (A)
+ DEFINE A AS PREV(price, price) > 0
+);
+
+-- Non-constant offset: volatile function as offset
+SELECT price FROM stock
+WINDOW w AS (
+ PARTITION BY company
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ INITIAL
+ PATTERN (A)
+ DEFINE A AS PREV(price, random()::int) > 0
+);
+
+--
+-- 2-arg PREV/NEXT: functional tests
+--
+
+-- PREV(price, 2): match rows where current price > price 2 rows back
+-- stock: 100, 90, 80, 95, 110
+-- Pattern (A B+): A=any, B where price > PREV(price, 2)
+-- At pos 2 (80): A matches. pos 3 (95): 95 > PREV(95,2)=90 TRUE.
+-- pos 4 (110): 110 > PREV(110,2)=80 TRUE. Match!
+SELECT company, tdate, price,
+ first_value(price) OVER w, last_value(price) OVER w, count(*) OVER w
+FROM stock
+WINDOW w AS (
+ PARTITION BY company ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A B+)
+ DEFINE
+ A AS TRUE,
+ B AS price > PREV(price, 2)
+);
+
+-- NEXT(price, 2): match rows where current price > price 2 rows ahead
+-- pos 0 (100): NEXT(100,2)=80, 100>80 TRUE. pos 1 (90): NEXT(90,2)=95, 90>95 FALSE. Match ends.
+SELECT company, tdate, price,
+ first_value(price) OVER w, last_value(price) OVER w, count(*) OVER w
+FROM stock
+WINDOW w AS (
+ PARTITION BY company ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+)
+ DEFINE A AS price > NEXT(price, 2)
+);
+
+-- Expressions inside PREV/NEXT arg: expr is evaluated on target row
+-- PREV(price - 50, 1): fetches (price - 50) from 1 row back
+SELECT company, tdate, price,
+ first_value(price) OVER w, last_value(price) OVER w, count(*) OVER w
+FROM stock
+WINDOW w AS (
+ PARTITION BY company ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+)
+ DEFINE A AS price > PREV(price - 50, 1)
+);
+
+-- NEXT(price * 2, 1): fetches (price * 2) from 1 row ahead
+SELECT company, tdate, price,
+ first_value(price) OVER w, last_value(price) OVER w, count(*) OVER w
+FROM stock
+WINDOW w AS (
+ PARTITION BY company ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+)
+ DEFINE A AS price < NEXT(price * 2, 1)
+);
+
+-- Large offset: PREV(val, 999) on 1000-row series matches only last row
+-- NEXT(val, 999) matches only first row
+SELECT val, first_value(val) OVER w, last_value(val) OVER w, count(*) OVER w
+FROM generate_series(1, 1000) AS t(val)
+WINDOW w AS (
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+)
+ DEFINE A AS PREV(val, 999) = 1
+)
+ORDER BY val DESC LIMIT 3;
+
+SELECT val, first_value(val) OVER w, last_value(val) OVER w, count(*) OVER w
+FROM generate_series(1, 1000) AS t(val)
+WINDOW w AS (
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+)
+ DEFINE A AS NEXT(val, 999) = 1000
+)
+LIMIT 3;
+
+-- PREV(price, 0): offset 0 means current row, always equal to price
+-- A+ matches entire partition as one group; count = partition size
+SELECT company, tdate, price,
+ first_value(price) OVER w, last_value(price) OVER w, count(*) OVER w
+FROM stock
+WINDOW w AS (
+ PARTITION BY company ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+)
+ DEFINE A AS PREV(price, 0) = price
+);
+
+-- 2-arg PREV/NEXT outside DEFINE clause
+SELECT prev(price, 2) FROM stock;
+SELECT next(price, 2) FROM stock;
+
+-- 2-arg PREV/NEXT: negative offset
+SELECT company, tdate, price, first_value(price) OVER w
+FROM stock
+WINDOW w AS (
+ PARTITION BY company ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+)
+ DEFINE A AS PREV(price, -1) IS NOT NULL
+);
+
+-- 2-arg PREV/NEXT: NULL offset (typed)
+SELECT company, tdate, price, first_value(price) OVER w
+FROM stock
+WINDOW w AS (
+ PARTITION BY company ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+)
+ DEFINE A AS PREV(price, NULL::int8) IS NOT NULL
+);
+
+-- 2-arg PREV/NEXT: NULL offset (untyped)
+SELECT company, tdate, price, first_value(price) OVER w
+FROM stock
+WINDOW w AS (
+ PARTITION BY company ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+)
+ DEFINE A AS PREV(price, NULL) IS NOT NULL
+);
+
+-- 2-arg PREV/NEXT: host variable negative and NULL
+PREPARE test_prev_offset(int8) AS
+SELECT company, tdate, price, first_value(price) OVER w
+FROM stock
+WINDOW w AS (
+ PARTITION BY company ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+)
+ DEFINE A AS price > PREV(price, $1)
+);
+EXECUTE test_prev_offset(-1);
+EXECUTE test_prev_offset(NULL);
+DEALLOCATE test_prev_offset;
+
+-- 2-arg PREV/NEXT: host variable with expression (0 + $1)
+PREPARE test_prev_offset(int8) AS
+SELECT company, tdate, price, first_value(price) OVER w
+FROM stock
+WINDOW w AS (
+ PARTITION BY company ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ PATTERN (A+)
+ DEFINE A AS price > PREV(price, 0 + $1)
+);
+EXECUTE test_prev_offset(-1);
+EXECUTE test_prev_offset(NULL);
+DEALLOCATE test_prev_offset;
+
+-- 2-arg: two PREV with different offsets in same DEFINE clause
+-- B: price exceeds both 1-back and 2-back values
+SELECT company, tdate, price,
+ first_value(price) OVER w, last_value(price) OVER w, count(*) OVER w
+FROM stock
+WINDOW w AS (
+ PARTITION BY company ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP PAST LAST ROW
+ PATTERN (A B+)
+ DEFINE
+ A AS TRUE,
+ B AS price > PREV(price, 1) AND price > PREV(price, 2)
+);
+
+-- 2-arg: PREV and NEXT with explicit offsets in same DEFINE clause
+-- A: price exceeds 1-back and is below 1-ahead (ascending interior point)
+SELECT company, tdate, price,
+ first_value(price) OVER w, last_value(price) OVER w, count(*) OVER w
+FROM stock
+WINDOW w AS (
+ PARTITION BY company ORDER BY tdate
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP PAST LAST ROW
+ PATTERN (A+)
+ DEFINE A AS price > PREV(price, 1) AND price < NEXT(price, 1)
+);
+
--
-- SKIP TO / Backtracking / Frame boundary
--
@@ -671,7 +960,7 @@ count(*) OVER w
);
-- ReScan test: LATERAL join forces WindowAgg rescan with RPR
--- Tests ExecReScanWindowAgg clearing prev_slot/next_slot
+-- Tests ExecReScanWindowAgg clearing nav_slot
SELECT g.x, sub.*
FROM generate_series(1, 2) g(x),
LATERAL (
diff --git a/src/test/regress/sql/rpr_explain.sql b/src/test/regress/sql/rpr_explain.sql
index 93e06b0cbdf..c2cbe2edd59 100644
--- a/src/test/regress/sql/rpr_explain.sql
+++ b/src/test/regress/sql/rpr_explain.sql
@@ -2088,9 +2088,65 @@ WINDOW w AS (
D AS v < PREV(v)
);');
--- Using NULL comparisons
+-- Using 1-arg PREV (implicit offset 1)
CREATE VIEW rpr_ev83 AS
SELECT count(*) OVER w
+FROM generate_series(1, 30) AS s(v)
+WINDOW w AS (
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP PAST LAST ROW
+ PATTERN (A B+)
+ DEFINE
+ A AS TRUE,
+ B AS v > PREV(v)
+);
+SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev83'), E'\n')) AS line WHERE line ~ 'PATTERN|DEFINE|PREV|NEXT';
+
+-- Using 1-arg NEXT (implicit offset 1)
+CREATE VIEW rpr_ev84 AS
+SELECT count(*) OVER w
+FROM generate_series(1, 30) AS s(v)
+WINDOW w AS (
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP PAST LAST ROW
+ PATTERN (A B+)
+ DEFINE
+ A AS TRUE,
+ B AS v < NEXT(v)
+);
+SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev84'), E'\n')) AS line WHERE line ~ 'PATTERN|DEFINE|PREV|NEXT';
+
+-- Using 2-arg PREV (explicit offset)
+CREATE VIEW rpr_ev85 AS
+SELECT count(*) OVER w
+FROM generate_series(1, 30) AS s(v)
+WINDOW w AS (
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP PAST LAST ROW
+ PATTERN (A B+)
+ DEFINE
+ A AS TRUE,
+ B AS v > PREV(v, 2)
+);
+SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev85'), E'\n')) AS line WHERE line ~ 'PATTERN|DEFINE|PREV|NEXT';
+
+-- Using 2-arg NEXT (explicit offset)
+CREATE VIEW rpr_ev86 AS
+SELECT count(*) OVER w
+FROM generate_series(1, 30) AS s(v)
+WINDOW w AS (
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP PAST LAST ROW
+ PATTERN (A B+)
+ DEFINE
+ A AS TRUE,
+ B AS v < NEXT(v, 2)
+);
+SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev86'), E'\n')) AS line WHERE line ~ 'PATTERN|DEFINE|PREV|NEXT';
+
+-- Using NULL comparisons
+CREATE VIEW rpr_ev87 AS
+SELECT count(*) OVER w
FROM (
SELECT CASE WHEN v % 5 = 0 THEN NULL ELSE v END AS v
FROM generate_series(1, 30) v
@@ -2101,7 +2157,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_ev83'), E'\n')) AS line WHERE line ~ 'PATTERN';
+SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev87'), 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
@@ -2121,7 +2177,7 @@ WINDOW w AS (
-- ============================================================
-- 500 rows - verify statistics scale correctly
-CREATE VIEW rpr_ev84 AS
+CREATE VIEW rpr_ev88 AS
SELECT count(*) OVER w
FROM generate_series(1, 500) AS s(v)
WINDOW w AS (
@@ -2130,7 +2186,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_ev84'), E'\n')) AS line WHERE line ~ 'PATTERN';
+SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev88'), 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
@@ -2143,7 +2199,7 @@ WINDOW w AS (
);');
-- High match count scenario
-CREATE VIEW rpr_ev85 AS
+CREATE VIEW rpr_ev89 AS
SELECT count(*) OVER w
FROM generate_series(1, 500) AS s(v)
WINDOW w AS (
@@ -2152,7 +2208,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_ev85'), E'\n')) AS line WHERE line ~ 'PATTERN';
+SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev89'), 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,7 +2221,7 @@ WINDOW w AS (
);');
-- High skip count scenario
-CREATE VIEW rpr_ev86 AS
+CREATE VIEW rpr_ev90 AS
SELECT count(*) OVER w
FROM generate_series(1, 500) AS s(v)
WINDOW w AS (
@@ -2179,7 +2235,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_ev86'), E'\n')) AS line WHERE line ~ 'PATTERN';
+SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev90'), 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
@@ -2207,7 +2263,7 @@ WINDOW w AS (
--
-- Without RPR: row_number() frame is optimized to ROWS UNBOUNDED PRECEDING
-CREATE VIEW rpr_ev87 AS
+CREATE VIEW rpr_ev91 AS
SELECT row_number() OVER w
FROM generate_series(1, 10) AS s(v)
WINDOW w AS (
@@ -2215,10 +2271,10 @@ WINDOW w AS (
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
);
-EXPLAIN (COSTS OFF) SELECT * FROM rpr_ev87;
+EXPLAIN (COSTS OFF) SELECT * FROM rpr_ev91;
-- With RPR: frame must remain ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
-CREATE VIEW rpr_ev88 AS
+CREATE VIEW rpr_ev92 AS
SELECT row_number() OVER w
FROM generate_series(1, 10) AS s(v)
WINDOW w AS (
@@ -2230,13 +2286,13 @@ WINDOW w AS (
B AS v > PREV(v)
);
-EXPLAIN (COSTS OFF) SELECT * FROM rpr_ev88;
+EXPLAIN (COSTS OFF) SELECT * FROM rpr_ev92;
--
-- Planner optimization: non-RPR and RPR windows that share the same base frame
-- after frame optimization are kept as separate WindowAgg nodes.
--
-CREATE VIEW rpr_ev89 AS
+CREATE VIEW rpr_ev93 AS
SELECT
row_number() OVER w_normal AS rn_normal,
row_number() OVER w_rpr AS rn_rpr
@@ -2250,7 +2306,7 @@ WINDOW
DEFINE A AS v > 1
);
-EXPLAIN (COSTS OFF) SELECT * FROM rpr_ev89;
+EXPLAIN (COSTS OFF) SELECT * FROM rpr_ev93;
--
-- Planner optimization: find_window_run_conditions must not push down
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 36091f56418..ca9faabc7e3 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -1775,7 +1775,6 @@ NamedLWLockTrancheRequest
NamedTuplestoreScan
NamedTuplestoreScanState
NamespaceInfo
-NavigationInfo
NestLoop
NestLoopParam
NestLoopState
@@ -2441,6 +2440,8 @@ QuerySource
QueueBackendStatus
QueuePosition
QuitSignalReason
+RPRNavExpr
+RPRNavKind
RBTNode
RBTOrderControl
RBTree
--
2.50.1 (Apple Git-155)
Attachments:
[text/plain] nocfbot-0001-Fix-mergeGroupPrefixSuffix-max-increment.txt (6.8K, 3-nocfbot-0001-Fix-mergeGroupPrefixSuffix-max-increment.txt)
download | inline diff:
From a9c7017524a14820f07955366cca5e6a2a154319 Mon Sep 17 00:00:00 2001
From: Henson Choi <[email protected]>
Date: Thu, 19 Mar 2026 11:37:34 +0900
Subject: [PATCH] Fix mergeGroupPrefixSuffix() to also increment max when
absorbing prefix/suffix
When mergeGroupPrefixSuffix() found a prefix or suffix that matched a GROUP
node's children, it incremented GROUP's min quantifier but left max unchanged.
This produced invalid quantifier state (min > max) when the GROUP had a finite
max bound.
Fix by also incrementing max when it is finite. Guard both the prefix and
suffix cases with an overflow check (min < RPR_QUANTITY_INF - 1) to prevent
wraparound into the sentinel value.
Add Assert() calls in fillRPRPatternVar(), fillRPRPatternGroup(), and
finalizeRPRPattern() to catch invalid min/max combinations at pattern
compilation time.
---
src/backend/optimizer/plan/rpr.c | 33 ++++++++++++++++++++++++-----
src/test/regress/expected/rpr.out | 35 +++++++++++++++++++++++++++++++
src/test/regress/sql/rpr.sql | 23 ++++++++++++++++++++
3 files changed, 86 insertions(+), 5 deletions(-)
diff --git a/src/backend/optimizer/plan/rpr.c b/src/backend/optimizer/plan/rpr.c
index b958280e94c..c50cbdc18f1 100644
--- a/src/backend/optimizer/plan/rpr.c
+++ b/src/backend/optimizer/plan/rpr.c
@@ -541,13 +541,17 @@ mergeGroupPrefixSuffix(List *children)
/* Compare with GROUP's (possibly unwrapped) children */
if (rprPatternChildrenEqual(prefixElements, groupContent) &&
- child->min < RPR_QUANTITY_INF)
+ child->min < RPR_QUANTITY_INF - 1 &&
+ (child->max == RPR_QUANTITY_INF ||
+ child->max < RPR_QUANTITY_INF - 1))
{
/*
- * Match! Merge by incrementing GROUP's min. Remove the
- * prefix elements from output.
+ * Match! Merge by incrementing GROUP's quantifier. Remove
+ * the prefix elements from output.
*/
child->min += 1;
+ if (child->max != RPR_QUANTITY_INF)
+ child->max += 1;
/* Rebuild result without matched prefix */
trimmed = NIL;
@@ -595,12 +599,17 @@ mergeGroupPrefixSuffix(List *children)
/* Compare with GROUP's children */
if (list_length(suffixElements) == groupChildCount &&
rprPatternChildrenEqual(suffixElements, groupContent) &&
- child->min < RPR_QUANTITY_INF)
+ child->min < RPR_QUANTITY_INF - 1 &&
+ (child->max == RPR_QUANTITY_INF ||
+ child->max < RPR_QUANTITY_INF - 1))
{
/*
- * Match! Absorb suffix by incrementing min and skipping.
+ * Match! Absorb suffix by incrementing quantifier and
+ * skipping.
*/
child->min += 1;
+ if (child->max != RPR_QUANTITY_INF)
+ child->max += 1;
skipUntil = suffixStart + groupChildCount;
/*
@@ -1152,6 +1161,9 @@ fillRPRPatternVar(RPRPatternNode *node, RPRPattern *pat, int *idx, RPRDepth dept
elem->depth = depth;
elem->min = node->min;
elem->max = (node->max == INT_MAX) ? RPR_QUANTITY_INF : node->max;
+ Assert(elem->min >= 0 && elem->min < RPR_QUANTITY_INF &&
+ elem->max >= 1 &&
+ (elem->max == RPR_QUANTITY_INF || elem->min <= elem->max));
elem->next = RPR_ELEMIDX_INVALID;
elem->jump = RPR_ELEMIDX_INVALID;
if (node->reluctant)
@@ -1191,6 +1203,9 @@ fillRPRPatternGroup(RPRPatternNode *node, RPRPattern *pat, int *idx, RPRDepth de
elem->depth = depth;
elem->min = node->min;
elem->max = (node->max == INT_MAX) ? RPR_QUANTITY_INF : node->max;
+ Assert(elem->min >= 0 && elem->min < RPR_QUANTITY_INF &&
+ elem->max >= 1 &&
+ (elem->max == RPR_QUANTITY_INF || elem->min <= elem->max));
elem->next = RPR_ELEMIDX_INVALID; /* set by finalize */
elem->jump = RPR_ELEMIDX_INVALID; /* set after END */
if (node->reluctant)
@@ -1216,6 +1231,9 @@ fillRPRPatternGroup(RPRPatternNode *node, RPRPattern *pat, int *idx, RPRDepth de
endElem->depth = depth;
endElem->min = node->min;
endElem->max = (node->max == INT_MAX) ? RPR_QUANTITY_INF : node->max;
+ Assert(endElem->min >= 0 && endElem->min < RPR_QUANTITY_INF &&
+ endElem->max >= 1 &&
+ (endElem->max == RPR_QUANTITY_INF || endElem->min <= endElem->max));
endElem->next = RPR_ELEMIDX_INVALID;
endElem->jump = groupStartIdx; /* loop to first child */
if (node->reluctant)
@@ -1398,6 +1416,11 @@ finalizeRPRPattern(RPRPattern *result)
if (elem->next == RPR_ELEMIDX_INVALID)
elem->next = (i < finIdx - 1) ? i + 1 : finIdx;
+
+ /* Verify quantifier range is valid */
+ Assert(elem->min >= 0 && elem->min < RPR_QUANTITY_INF &&
+ elem->max >= 1 &&
+ (elem->max == RPR_QUANTITY_INF || elem->min <= elem->max));
}
/* Add FIN marker at the end */
diff --git a/src/test/regress/expected/rpr.out b/src/test/regress/expected/rpr.out
index ecbe835cbb4..e72171050c7 100644
--- a/src/test/regress/expected/rpr.out
+++ b/src/test/regress/expected/rpr.out
@@ -588,6 +588,41 @@ SELECT company, tdate, price, count(*) OVER w
company2 | 07-10-2023 | 1300 | 0
(20 rows)
+-- test prefix/suffix merge optimization with bounded quantifier
+-- Pattern A B (A B){1,2} A B should be optimized to (A B){3,4}
+CREATE TEMP TABLE rpr_t (id int, val text);
+INSERT INTO rpr_t VALUES
+ (1,'A'),(2,'B'),
+ (3,'A'),(4,'B'),
+ (5,'A'),(6,'B'),
+ (7,'A'),(8,'B'),
+ (9,'X');
+SELECT id, val, count(*) OVER w AS match_count
+FROM rpr_t
+WINDOW w AS (
+ ORDER BY id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ INITIAL
+ PATTERN (A B (A B){1,2} A B)
+ DEFINE
+ A AS val = 'A',
+ B AS val = 'B'
+);
+ id | val | match_count
+----+-----+-------------
+ 1 | A | 8
+ 2 | B | 0
+ 3 | A | 6
+ 4 | B | 0
+ 5 | A | 0
+ 6 | B | 0
+ 7 | A | 0
+ 8 | B | 0
+ 9 | X | 0
+(9 rows)
+
+DROP TABLE rpr_t;
-- last_value() should remain consistent
SELECT company, tdate, price, last_value(price) OVER w
FROM stock
diff --git a/src/test/regress/sql/rpr.sql b/src/test/regress/sql/rpr.sql
index b308f8d7cb4..95794d409e1 100644
--- a/src/test/regress/sql/rpr.sql
+++ b/src/test/regress/sql/rpr.sql
@@ -249,6 +249,29 @@ SELECT company, tdate, price, count(*) OVER w
A AS price > 100
);
+-- test prefix/suffix merge optimization with bounded quantifier
+-- Pattern A B (A B){1,2} A B should be optimized to (A B){3,4}
+CREATE TEMP TABLE rpr_t (id int, val text);
+INSERT INTO rpr_t VALUES
+ (1,'A'),(2,'B'),
+ (3,'A'),(4,'B'),
+ (5,'A'),(6,'B'),
+ (7,'A'),(8,'B'),
+ (9,'X');
+SELECT id, val, count(*) OVER w AS match_count
+FROM rpr_t
+WINDOW w AS (
+ ORDER BY id
+ ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
+ AFTER MATCH SKIP TO NEXT ROW
+ INITIAL
+ PATTERN (A B (A B){1,2} A B)
+ DEFINE
+ A AS val = 'A',
+ B AS val = 'B'
+);
+DROP TABLE rpr_t;
+
-- last_value() should remain consistent
SELECT company, tdate, price, last_value(price) OVER w
FROM stock
--
2.50.1 (Apple Git-155)
[text/plain] nocfbot-0002-Fix-RPR-error-codes-and-GROUPS-typo.txt (5.0K, 4-nocfbot-0002-Fix-RPR-error-codes-and-GROUPS-typo.txt)
download | inline diff:
From 9abc7a58b48d389e279d1a3676351f36b9ebc6c3 Mon Sep 17 00:00:00 2001
From: Henson Choi <[email protected]>
Date: Thu, 19 Mar 2026 16:40:35 +0900
Subject: [PATCH] Fix RPR error codes and GROUPS typo in frame validation
Frame validation errors in transformRPR() were reported with
ERRCODE_SYNTAX_ERROR. These are semantic errors detected after parsing,
so ERRCODE_WINDOWING_ERROR is the correct code per SQL standard.
Also fix a typo in the error message: "FRAME option GROUP" ->
"FRAME option GROUPS" to match the actual SQL keyword.
---
src/backend/parser/parse_rpr.c | 10 +++++-----
src/test/regress/expected/rpr_base.out | 8 ++++----
src/test/regress/sql/rpr_base.sql | 4 ++--
3 files changed, 11 insertions(+), 11 deletions(-)
diff --git a/src/backend/parser/parse_rpr.c b/src/backend/parser/parse_rpr.c
index e574b69d9b5..66252cd185e 100644
--- a/src/backend/parser/parse_rpr.c
+++ b/src/backend/parser/parse_rpr.c
@@ -72,15 +72,15 @@ transformRPR(ParseState *pstate, WindowClause *wc, WindowDef *windef,
/* Frame type must be "ROW" */
if (wc->frameOptions & FRAMEOPTION_GROUPS)
ereport(ERROR,
- (errcode(ERRCODE_SYNTAX_ERROR),
- errmsg("FRAME option GROUP is not permitted when row pattern recognition is used"),
+ (errcode(ERRCODE_WINDOWING_ERROR),
+ errmsg("FRAME option GROUPS is not permitted when row pattern recognition is used"),
errhint("Use: ROWS instead"),
parser_errposition(pstate,
windef->frameLocation >= 0 ?
windef->frameLocation : windef->location)));
if (wc->frameOptions & FRAMEOPTION_RANGE)
ereport(ERROR,
- (errcode(ERRCODE_SYNTAX_ERROR),
+ (errcode(ERRCODE_WINDOWING_ERROR),
errmsg("FRAME option RANGE is not permitted when row pattern recognition is used"),
errhint("Use: ROWS instead"),
parser_errposition(pstate,
@@ -107,7 +107,7 @@ transformRPR(ParseState *pstate, WindowClause *wc, WindowDef *windef,
(wc->frameOptions & FRAMEOPTION_START_OFFSET_FOLLOWING));
ereport(ERROR,
- (errcode(ERRCODE_SYNTAX_ERROR),
+ (errcode(ERRCODE_WINDOWING_ERROR),
errmsg("FRAME must start at CURRENT ROW when row pattern recognition is used"),
errdetail("Current frame starts with %s.", startBound),
errhint("Use: %s BETWEEN CURRENT ROW AND ...", frameType),
@@ -133,7 +133,7 @@ transformRPR(ParseState *pstate, WindowClause *wc, WindowDef *windef,
(wc->frameOptions & FRAMEOPTION_EXCLUDE_TIES));
ereport(ERROR,
- (errcode(ERRCODE_SYNTAX_ERROR),
+ (errcode(ERRCODE_WINDOWING_ERROR),
errmsg("EXCLUDE options are not permitted when row pattern recognition is used"),
errdetail("Frame definition includes %s.", excludeType),
errhint("Remove the EXCLUDE clause from the window definition."),
diff --git a/src/test/regress/expected/rpr_base.out b/src/test/regress/expected/rpr_base.out
index 7931ad07d7d..50a9e7daea9 100644
--- a/src/test/regress/expected/rpr_base.out
+++ b/src/test/regress/expected/rpr_base.out
@@ -483,11 +483,11 @@ WINDOW w AS (
PATTERN (A+)
DEFINE A AS val > 0
);
-ERROR: FRAME option GROUP is not permitted when row pattern recognition is used
+ERROR: FRAME option GROUPS is not permitted when row pattern recognition is used
LINE 5: GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWI...
^
HINT: Use: ROWS instead
--- Expected: ERROR: FRAME option GROUP is not permitted when row pattern recognition is used
+-- Expected: ERROR: FRAME option GROUPS is not permitted when row pattern recognition is used
-- Starting with N PRECEDING
SELECT COUNT(*) OVER w
FROM rpr_frame
@@ -656,11 +656,11 @@ WINDOW w AS (
DEFINE A AS val >= 0, B AS val >= 0
)
ORDER BY id;
-ERROR: FRAME option GROUP is not permitted when row pattern recognition is used
+ERROR: FRAME option GROUPS is not permitted when row pattern recognition is used
LINE 5: GROUPS BETWEEN CURRENT ROW AND 1 FOLLOWING
^
HINT: Use: ROWS instead
--- Expected: ERROR: FRAME option GROUP is not permitted when row pattern recognition is used
+-- Expected: ERROR: FRAME option GROUPS is not permitted when row pattern recognition is used
DROP TABLE rpr_frame;
-- ============================================================
-- PARTITION BY + FRAME Tests
diff --git a/src/test/regress/sql/rpr_base.sql b/src/test/regress/sql/rpr_base.sql
index f8815c7376a..e54d54e400a 100644
--- a/src/test/regress/sql/rpr_base.sql
+++ b/src/test/regress/sql/rpr_base.sql
@@ -397,7 +397,7 @@ WINDOW w AS (
PATTERN (A+)
DEFINE A AS val > 0
);
--- Expected: ERROR: FRAME option GROUP is not permitted when row pattern recognition is used
+-- Expected: ERROR: FRAME option GROUPS is not permitted when row pattern recognition is used
-- Starting with N PRECEDING
SELECT COUNT(*) OVER w
@@ -517,7 +517,7 @@ WINDOW w AS (
DEFINE A AS val >= 0, B AS val >= 0
)
ORDER BY id;
--- Expected: ERROR: FRAME option GROUP is not permitted when row pattern recognition is used
+-- Expected: ERROR: FRAME option GROUPS is not permitted when row pattern recognition is used
DROP TABLE rpr_frame;
--
2.50.1 (Apple Git-155)
[text/plain] nocfbot-0003-Add-check_stack_depth-to-RPR-engine.txt (5.7K, 5-nocfbot-0003-Add-check_stack_depth-to-RPR-engine.txt)
download | inline diff:
From 0bea151eb70ae266cf29ee24395e8d178bece917 Mon Sep 17 00:00:00 2001
From: Henson Choi <[email protected]>
Date: Sat, 14 Mar 2026 09:44:40 +0900
Subject: [PATCH] Add check_stack_depth() and CHECK_FOR_INTERRUPTS() to RPR
engine
Several recursive functions in the RPR implementation lacked stack
overflow protection, and the NFA evaluation loop lacked interrupt
handling.
Add check_stack_depth() to all recursive RPR functions:
- execRPR.c: nfa_advance_state() (NFA epsilon-closure DFS)
- rpr.c: optimizeRPRPattern(), scanRPRPatternRecursive(),
fillRPRPattern(), computeAbsorbabilityRecursive(),
collectPatternVariablesRecursive()
- parse_rpr.c: validateRPRPatternVarCount()
Add CHECK_FOR_INTERRUPTS() in the main NFA processing loops in
execRPR.c to allow query cancellation during long pattern matches.
---
src/backend/executor/execRPR.c | 17 ++++++++++++++++-
src/backend/optimizer/plan/rpr.c | 11 +++++++++++
src/backend/parser/parse_rpr.c | 3 +++
3 files changed, 30 insertions(+), 1 deletion(-)
diff --git a/src/backend/executor/execRPR.c b/src/backend/executor/execRPR.c
index 0d5ba7516e9..06934b95da3 100644
--- a/src/backend/executor/execRPR.c
+++ b/src/backend/executor/execRPR.c
@@ -24,6 +24,7 @@
#include "executor/execRPR.h"
#include "executor/executor.h"
+#include "miscadmin.h"
#include "optimizer/rpr.h"
#include "utils/memutils.h"
@@ -1994,6 +1995,8 @@ nfa_update_absorption_flags(RPRNFAContext *ctx)
*/
for (state = ctx->states; state != NULL; state = state->next)
{
+ CHECK_FOR_INTERRUPTS();
+
if (state->isAbsorbable)
hasAbsorbable = true;
else
@@ -2035,6 +2038,8 @@ nfa_states_covered(RPRPattern *pattern, RPRNFAContext *older, RPRNFAContext *new
for (olderState = older->states; olderState != NULL; olderState = olderState->next)
{
+ CHECK_FOR_INTERRUPTS();
+
/* Covering state must also be absorbable */
if (olderState->isAbsorbable &&
olderState->elemIdx == newerState->elemIdx &&
@@ -2195,6 +2200,8 @@ nfa_match(WindowAggState *winstate, RPRNFAContext *ctx, bool *varMatched)
{
RPRPatternElement *elem = &elements[state->elemIdx];
+ CHECK_FOR_INTERRUPTS();
+
nextState = state->next;
if (RPRElemIsVar(elem))
@@ -2670,6 +2677,9 @@ nfa_advance_state(WindowAggState *winstate, RPRNFAContext *ctx,
Assert(state->elemIdx >= 0 && state->elemIdx < pattern->numElements);
+ /* Protect against stack overflow for deeply complex patterns */
+ check_stack_depth();
+
/* Cycle detection: if this elemIdx was already visited in this DFS, bail */
if (winstate->nfaVisitedElems[WORDNUM(state->elemIdx)] &
((bitmapword) 1 << BITNUM(state->elemIdx)))
@@ -2722,13 +2732,15 @@ nfa_advance(WindowAggState *winstate, RPRNFAContext *ctx, int64 currentPos)
{
RPRNFAState *states = ctx->states;
RPRNFAState *state;
+ RPRNFAState *savedMatchedState;
ctx->states = NULL; /* Will rebuild */
/* Process each state in lexical order (DFS order from previous advance) */
while (states != NULL)
{
- RPRNFAState *savedMatchedState = ctx->matchedState;
+ CHECK_FOR_INTERRUPTS();
+ savedMatchedState = ctx->matchedState;
/* Clear visited bitmap before each state's DFS expansion */
memset(winstate->nfaVisitedElems, 0,
@@ -2912,6 +2924,9 @@ ExecRPRProcessRow(WindowAggState *winstate, int64 currentPos,
RPRNFAContext *ctx;
bool *varMatched = winstate->nfaVarMatched;
+ /* Allow query cancellation once per row for simple/low-state patterns */
+ CHECK_FOR_INTERRUPTS();
+
/*
* Phase 1: Match all contexts (convergence). Evaluate VAR elements,
* update counts, remove dead states.
diff --git a/src/backend/optimizer/plan/rpr.c b/src/backend/optimizer/plan/rpr.c
index c50cbdc18f1..0b4d93b933e 100644
--- a/src/backend/optimizer/plan/rpr.c
+++ b/src/backend/optimizer/plan/rpr.c
@@ -37,6 +37,7 @@
#include "postgres.h"
+#include "miscadmin.h"
#include "nodes/makefuncs.h"
#include "optimizer/rpr.h"
@@ -933,6 +934,8 @@ optimizeRPRPattern(RPRPatternNode *pattern)
/* Pattern nodes from parser are never NULL */
Assert(pattern != NULL);
+ check_stack_depth();
+
switch (pattern->nodeType)
{
case RPR_PATTERN_VAR:
@@ -991,6 +994,8 @@ scanRPRPatternRecursive(RPRPatternNode *node, char **varNames, int *numVars,
/* Pattern nodes from parser are never NULL */
Assert(node != NULL);
+ check_stack_depth();
+
/* Check recursion depth limit before overflow occurs */
if (depth >= RPR_DEPTH_MAX)
ereport(ERROR,
@@ -1367,6 +1372,8 @@ fillRPRPattern(RPRPatternNode *node, RPRPattern *pat, int *idx, RPRDepth depth)
/* Pattern nodes from parser are never NULL */
Assert(node != NULL);
+ check_stack_depth();
+
switch (node->nodeType)
{
case RPR_PATTERN_SEQ:
@@ -1609,6 +1616,8 @@ computeAbsorbabilityRecursive(RPRPattern *pattern, RPRElemIdx startIdx,
{
RPRPatternElement *elem = &pattern->elements[startIdx];
+ check_stack_depth();
+
if (RPRElemIsAlt(elem))
{
/* ALT: recursively check each branch */
@@ -1716,6 +1725,8 @@ collectPatternVariablesRecursive(RPRPatternNode *node, List **varNames)
Assert(node != NULL);
+ check_stack_depth();
+
switch (node->nodeType)
{
case RPR_PATTERN_VAR:
diff --git a/src/backend/parser/parse_rpr.c b/src/backend/parser/parse_rpr.c
index 66252cd185e..92fef2d9ba7 100644
--- a/src/backend/parser/parse_rpr.c
+++ b/src/backend/parser/parse_rpr.c
@@ -25,6 +25,7 @@
#include "postgres.h"
+#include "miscadmin.h"
#include "nodes/makefuncs.h"
#include "nodes/nodeFuncs.h"
#include "optimizer/rpr.h"
@@ -175,6 +176,8 @@ validateRPRPatternVarCount(ParseState *pstate, RPRPatternNode *node,
/* Pattern node must exist - parser always provides non-NULL root */
Assert(node != NULL);
+ check_stack_depth();
+
switch (node->nodeType)
{
case RPR_PATTERN_VAR:
--
2.50.1 (Apple Git-155)
[text/plain] nocfbot-0004-Fix-window_last_value-set_mark-during-RPR.txt (2.2K, 6-nocfbot-0004-Fix-window_last_value-set_mark-during-RPR.txt)
download | inline diff:
From ddf11ac02969b003d1f9b4296d2806dd64c88469 Mon Sep 17 00:00:00 2001
From: Henson Choi <[email protected]>
Date: Fri, 20 Mar 2026 13:39:20 +0900
Subject: [PATCH] Fix window_last_value set_mark to not advance mark during RPR
window_last_value passes set_mark=true to WinGetFuncArgInFrame, which is
correct for normal use: advancing the mark allows the tuplestore to discard
rows no longer needed, reducing memory usage.
However, when RPR is active, the reduced frame changes row by row. Advancing
the mark would prevent revisiting earlier rows that still fall within a future
row's reduced frame, producing incorrect results.
Rather than requiring each caller to know about RPR, add an override in
WinGetFuncArgInFrame: if RPR is defined, force set_mark=false regardless
of what the caller passed. This keeps the fix localized and transparent
to existing callers.
---
src/backend/executor/nodeWindowAgg.c | 7 +++++++
src/backend/utils/adt/windowfuncs.c | 2 +-
2 files changed, 8 insertions(+), 1 deletion(-)
diff --git a/src/backend/executor/nodeWindowAgg.c b/src/backend/executor/nodeWindowAgg.c
index 0a9ba5bd4e7..d3ce9897a4f 100644
--- a/src/backend/executor/nodeWindowAgg.c
+++ b/src/backend/executor/nodeWindowAgg.c
@@ -4700,6 +4700,13 @@ WinGetFuncArgInFrame(WindowObject winobj, int argno,
econtext = winstate->ss.ps.ps_ExprContext;
slot = winstate->temp_slot_1;
+ /*
+ * When RPR is active, the reduced frame changes row by row, so we must
+ * not advance the mark — doing so would prevent revisiting earlier rows.
+ */
+ if (rpr_is_defined(winstate))
+ set_mark = false;
+
if (winobj->ignore_nulls == IGNORE_NULLS)
return ignorenulls_getfuncarginframe(winobj, argno, relpos, seektype,
set_mark, isnull, isout);
diff --git a/src/backend/utils/adt/windowfuncs.c b/src/backend/utils/adt/windowfuncs.c
index efb60c99052..74ef109f72e 100644
--- a/src/backend/utils/adt/windowfuncs.c
+++ b/src/backend/utils/adt/windowfuncs.c
@@ -682,7 +682,7 @@ window_last_value(PG_FUNCTION_ARGS)
WinCheckAndInitializeNullTreatment(winobj, true, fcinfo);
result = WinGetFuncArgInFrame(winobj, 0,
- 0, WINDOW_SEEK_TAIL, false,
+ 0, WINDOW_SEEK_TAIL, true,
&isnull, NULL);
if (isnull)
PG_RETURN_NULL();
--
2.50.1 (Apple Git-155)
[text/plain] nocfbot-0005-Fix-row_is_in_reduced_frame-SEEK_TAIL.txt (1.9K, 7-nocfbot-0005-Fix-row_is_in_reduced_frame-SEEK_TAIL.txt)
download | inline diff:
From 40a5d3ae0b3f76ffb2ad73100d83d4efb2a3fcc6 Mon Sep 17 00:00:00 2001
From: Henson Choi <[email protected]>
Date: Fri, 20 Mar 2026 14:14:17 +0900
Subject: [PATCH] Fix row_is_in_reduced_frame argument and add bounds check in
WINDOW_SEEK_TAIL
In WinGetSlotInFrame, the WINDOW_SEEK_TAIL case passed frameheadpos+relpos
to row_is_in_reduced_frame to determine the reduced frame size. Since relpos
is non-positive, this queries a position at or before the frame head. When
relpos is negative the queried position falls outside the frame entirely,
which is invalid and could trigger internal Assert failures.
The correct argument is frameheadpos: the reduced frame always starts there,
and its size N gives the tail position as frameheadpos+N-1. Pass frameheadpos
directly instead.
Also add a bounds check (-relpos >= N -> out_of_frame) to handle future
callers that may pass negative relpos values. Currently only last_value()
calls this path with relpos=0, so the check has no effect at present.
---
src/backend/executor/nodeWindowAgg.c | 6 +++++-
1 file changed, 5 insertions(+), 1 deletion(-)
diff --git a/src/backend/executor/nodeWindowAgg.c b/src/backend/executor/nodeWindowAgg.c
index d3ce9897a4f..c92235c54c7 100644
--- a/src/backend/executor/nodeWindowAgg.c
+++ b/src/backend/executor/nodeWindowAgg.c
@@ -4906,12 +4906,16 @@ WinGetSlotInFrame(WindowObject winobj, TupleTableSlot *slot,
}
num_reduced_frame = row_is_in_reduced_frame(winobj,
- winstate->frameheadpos + relpos);
+ winstate->frameheadpos);
if (num_reduced_frame < 0)
goto out_of_frame;
else if (num_reduced_frame > 0)
+ {
+ if (-relpos >= num_reduced_frame)
+ goto out_of_frame;
abs_pos = winstate->frameheadpos + relpos +
num_reduced_frame - 1;
+ }
break;
default:
elog(ERROR, "unrecognized window seek type: %d", seektype);
--
2.50.1 (Apple Git-155)
[text/plain] nocfbot-0006-Clarify-ST_NONE-intent.txt (1.8K, 8-nocfbot-0006-Clarify-ST_NONE-intent.txt)
download | inline diff:
From e267e4f1e40bd6919efb4ebef769e60da3a7ac4e Mon Sep 17 00:00:00 2001
From: Henson Choi <[email protected]>
Date: Fri, 20 Mar 2026 16:59:21 +0900
Subject: [PATCH] Clarify ST_NONE intent in RPSkipTo enum and WindowClause
initialization
ST_NONE serves as the sentinel value (0) indicating that a WindowClause
has no RPR AFTER MATCH clause. This was implicit via palloc0 zero-filling
but not documented.
Add a comment to the ST_NONE enum value explaining its role as the default
for non-RPR windows, and add an explicit assignment in
transformWindowDefinitions() to make the intent clear in code.
---
src/backend/parser/parse_clause.c | 2 ++
src/include/nodes/parsenodes.h | 3 ++-
2 files changed, 4 insertions(+), 1 deletion(-)
diff --git a/src/backend/parser/parse_clause.c b/src/backend/parser/parse_clause.c
index b30c22933ec..3796a69ac3a 100644
--- a/src/backend/parser/parse_clause.c
+++ b/src/backend/parser/parse_clause.c
@@ -2889,6 +2889,8 @@ transformWindowDefinitions(ParseState *pstate,
* And prepare the new WindowClause.
*/
wc = makeNode(WindowClause);
+ wc->rpSkipTo = ST_NONE; /* ST_NONE marks this as a non-RPR window;
+ * overridden by transformRPR() if RPR is used */
wc->name = windef->name;
wc->refname = windef->refname;
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 432b0573990..c5bf2ce80bf 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -583,7 +583,8 @@ typedef struct SortBy
*/
typedef enum RPSkipTo
{
- ST_NONE, /* AFTER MATCH omitted */
+ ST_NONE, /* no AFTER MATCH clause; default for non-RPR
+ * windows */
ST_NEXT_ROW, /* SKIP TO NEXT ROW */
ST_PAST_LAST_ROW, /* SKIP TO PAST LAST ROW */
} RPSkipTo;
--
2.50.1 (Apple Git-155)
[text/plain] nocfbot-0007-Clarify-RPR-inverse-transition-comment.txt (1.5K, 9-nocfbot-0007-Clarify-RPR-inverse-transition-comment.txt)
download | inline diff:
From 124119ef649d05bcd3e20f5e5a1c353a186f99db Mon Sep 17 00:00:00 2001
From: Henson Choi <[email protected]>
Date: Fri, 20 Mar 2026 19:01:43 +0900
Subject: [PATCH] Clarify why RPR disables inverse transition optimization
When RPR is active, the reduced frame depends on pattern matching results
which can differ entirely from row to row. This makes it impossible to
use the inverse transition function to incrementally remove rows from the
aggregate state, so a full restart is required for every row.
Add a comment explaining this reasoning at the restart decision logic.
---
src/backend/executor/nodeWindowAgg.c | 4 +++-
1 file changed, 3 insertions(+), 1 deletion(-)
diff --git a/src/backend/executor/nodeWindowAgg.c b/src/backend/executor/nodeWindowAgg.c
index c92235c54c7..d144fa39375 100644
--- a/src/backend/executor/nodeWindowAgg.c
+++ b/src/backend/executor/nodeWindowAgg.c
@@ -853,7 +853,9 @@ eval_windowaggregates(WindowAggState *winstate)
* transition function, or
* - we have an EXCLUSION clause, or
* - if the new frame doesn't overlap the old one
- * - if RPR is enabled
+ * - if RPR (Row Pattern Recognition) is enabled, because the reduced
+ * frame depends on pattern matching results which can differ entirely
+ * from row to row, making inverse transition optimization inapplicable
*
* Note that we don't strictly need to restart in the last case, but if
* we're going to remove all rows from the aggregation anyway, a restart
--
2.50.1 (Apple Git-155)
[text/plain] nocfbot-0008-Reject-unused-DEFINE-variables.txt (21.4K, 10-nocfbot-0008-Reject-unused-DEFINE-variables.txt)
download | inline diff:
From 213650dbee9962492bfdd65ce3ed61fee674e5b4 Mon Sep 17 00:00:00 2001
From: Henson Choi <[email protected]>
Date: Fri, 20 Mar 2026 19:42:27 +0900
Subject: [PATCH] Reject DEFINE variables not used in PATTERN
DEFINE variables that do not appear in PATTERN are now rejected with
an error at parse time. When DEFINE supports qualified column references
(e.g. A.price), a DEFINE expression may reference other pattern variables.
If the planner silently removes unused variables, those references become
dangling, leading to incorrect evaluation. Rejecting at parse time avoids
this class of bugs entirely.
Remove filterDefineClause() from the planner and replace it with
buildDefineVariableList(), which builds the variable name list without
filtering. Update regression tests accordingly.
---
src/backend/executor/execRPR.c | 5 +-
src/backend/optimizer/plan/createplan.c | 15 ++---
src/backend/optimizer/plan/rpr.c | 34 ++--------
src/backend/parser/parse_rpr.c | 9 ++-
src/include/optimizer/rpr.h | 4 +-
src/test/regress/expected/rpr_base.out | 82 +++++--------------------
src/test/regress/sql/rpr_base.sql | 46 ++------------
7 files changed, 43 insertions(+), 152 deletions(-)
diff --git a/src/backend/executor/execRPR.c b/src/backend/executor/execRPR.c
index 06934b95da3..a0a462256ad 100644
--- a/src/backend/executor/execRPR.c
+++ b/src/backend/executor/execRPR.c
@@ -204,8 +204,7 @@
* IV-1. Entry Point
*
* create_windowagg_plan() (createplan.c)
- * +-- collectPatternVariables() Collect variable names
- * +-- filterDefineClause() Remove unused DEFINE entries
+ * +-- buildDefineVariableList() Build variable name list from DEFINE
* +-- buildRPRPattern() NFA compilation (6 phases)
*
* IV-2. The 6 Phases of buildRPRPattern()
@@ -1297,7 +1296,7 @@
* transformRPR parse_rpr.c Parser entry point
* transformDefineClause parse_rpr.c DEFINE transformation
* collectPatternVariables rpr.c Variable collection
- * filterDefineClause rpr.c DEFINE filtering
+ * buildDefineVariableList rpr.c DEFINE variable list
* buildRPRPattern rpr.c NFA compilation main
* optimizeRPRPattern rpr.c AST optimization
* fillRPRPattern rpr.c NFA element generation
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
index da71e7f3d64..9ac24cc222d 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -2539,19 +2539,16 @@ create_windowagg_plan(PlannerInfo *root, WindowAggPath *best_path)
ordNumCols++;
}
- /* Build RPR pattern and filter defineClause */
+ /* Build RPR pattern and defineVariableList */
if (wc->rpPattern)
{
- List *patternVars;
-
/*
- * Filter defineClause to include only variables used in PATTERN. This
- * eliminates unnecessary DEFINE evaluations at runtime.
+ * Build defineVariableList from defineClause. The parser already
+ * rejects DEFINE variables not used in PATTERN, so no filtering is
+ * needed.
*/
- patternVars = collectPatternVariables(wc->rpPattern);
- filteredDefineClause = filterDefineClause(wc->defineClause,
- patternVars,
- &defineVariableList);
+ buildDefineVariableList(wc->defineClause, &defineVariableList);
+ filteredDefineClause = wc->defineClause;
/* Compile and optimize RPR patterns */
compiledPattern = buildRPRPattern(wc->rpPattern,
diff --git a/src/backend/optimizer/plan/rpr.c b/src/backend/optimizer/plan/rpr.c
index 0b4d93b933e..85b1a00d095 100644
--- a/src/backend/optimizer/plan/rpr.c
+++ b/src/backend/optimizer/plan/rpr.c
@@ -1771,50 +1771,28 @@ collectPatternVariables(RPRPatternNode *pattern)
}
/*
- * filterDefineClause
- * Filter defineClause to include only variables used in PATTERN.
+ * buildDefineVariableList
+ * Build defineVariableList from defineClause.
*
- * This eliminates unnecessary DEFINE evaluations at runtime.
- * Also builds defineVariableList from the filtered result.
+ * The parser already ensures that all DEFINE variables appear in PATTERN,
+ * so no filtering is needed here.
*
- * Returns filtered defineClause (list of TargetEntry).
* Sets *defineVariableList to list of variable names (String nodes).
*/
-List *
-filterDefineClause(List *defineClause, List *patternVars,
- List **defineVariableList)
+void
+buildDefineVariableList(List *defineClause, List **defineVariableList)
{
- List *filteredDefineClause = NIL;
ListCell *lc;
- ListCell *lc2;
*defineVariableList = NIL;
- /* Filter defineClause: keep only variables used in PATTERN */
foreach(lc, defineClause)
{
TargetEntry *te = (TargetEntry *) lfirst(lc);
- foreach(lc2, patternVars)
- {
- if (strcmp(strVal(lfirst(lc2)), te->resname) == 0)
- {
- filteredDefineClause = lappend(filteredDefineClause, te);
- break;
- }
- }
- }
-
- /* Build defineVariableList from filtered defineClause */
- foreach(lc, filteredDefineClause)
- {
- TargetEntry *te = (TargetEntry *) lfirst(lc);
-
*defineVariableList = lappend(*defineVariableList,
makeString(pstrdup(te->resname)));
}
-
- return filteredDefineClause;
}
/*
diff --git a/src/backend/parser/parse_rpr.c b/src/backend/parser/parse_rpr.c
index 92fef2d9ba7..55283ab4bbe 100644
--- a/src/backend/parser/parse_rpr.c
+++ b/src/backend/parser/parse_rpr.c
@@ -244,8 +244,11 @@ validateRPRPatternVarCount(ParseState *pstate, RPRPatternNode *node,
}
}
if (!found)
- *varNames = lappend(*varNames,
- makeString(pstrdup(rt->name)));
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("DEFINE variable \"%s\" is not used in PATTERN",
+ rt->name),
+ parser_errposition(pstate, rt->location)));
}
}
}
@@ -265,7 +268,7 @@ validateRPRPatternVarCount(ParseState *pstate, RPRPatternNode *node,
* 6. Marks column origins and assigns collation information
*
* Note: Variables not in DEFINE are evaluated as TRUE by the executor.
- * Variables in DEFINE but not in PATTERN are filtered out by the planner.
+ * Variables in DEFINE but not in PATTERN are rejected as an error.
*
* XXX Pattern variable qualified column references in DEFINE (e.g.
* "A.price") are not yet supported. Currently rejected by
diff --git a/src/include/optimizer/rpr.h b/src/include/optimizer/rpr.h
index fa2d075925c..f93a128096b 100644
--- a/src/include/optimizer/rpr.h
+++ b/src/include/optimizer/rpr.h
@@ -52,8 +52,8 @@
#define RPRElemCanSkip(e) ((e)->min == 0)
extern List *collectPatternVariables(RPRPatternNode *pattern);
-extern List *filterDefineClause(List *defineClause, List *patternVars,
- List **defineVariableList);
+extern void buildDefineVariableList(List *defineClause,
+ List **defineVariableList);
extern RPRPattern *buildRPRPattern(RPRPatternNode *pattern, List *defineVariableList,
RPSkipTo rpSkipTo, int frameOptions);
diff --git a/src/test/regress/expected/rpr_base.out b/src/test/regress/expected/rpr_base.out
index 50a9e7daea9..3168468d0ae 100644
--- a/src/test/regress/expected/rpr_base.out
+++ b/src/test/regress/expected/rpr_base.out
@@ -353,12 +353,9 @@ WINDOW w AS (
DEFINE A AS id > 0, B AS id > 5 -- B not in pattern
)
ORDER BY id;
- id | cnt
-----+-----
- 1 | 2
- 2 | 0
-(2 rows)
-
+ERROR: DEFINE variable "b" is not used in PATTERN
+LINE 7: DEFINE A AS id > 0, B AS id > 5 -- B not in pattern
+ ^
DROP TABLE rpr_unused;
-- ============================================================
-- FRAME Options Tests
@@ -2860,9 +2857,9 @@ WINDOW w AS (
PATTERN (A+)
DEFINE A AS val > 0, B AS B.val > 0
);
-ERROR: pattern variable qualified column reference "b.val" is not supported in DEFINE clause
+ERROR: DEFINE variable "b" is not used in PATTERN
LINE 7: DEFINE A AS val > 0, B AS B.val > 0
- ^
+ ^
-- Expected: ERROR: pattern variable qualified column reference "b.val" is not supported
-- FROM-clause range variable qualified name: not allowed (prohibited by §6.5)
SELECT COUNT(*) OVER w
@@ -2941,12 +2938,9 @@ WINDOW w AS (
DEFINE A AS val > 0, B AS val > 5, C AS val > 10
)
ORDER BY id;
- id | val | cnt
-----+-----+-----
- 1 | 10 | 2
- 2 | 20 | 0
-(2 rows)
-
+ERROR: DEFINE variable "b" is not used in PATTERN
+LINE 7: DEFINE A AS val > 0, B AS val > 5, C AS val > 10
+ ^
DROP TABLE rpr_err;
-- NULL handling
CREATE TABLE rpr_null (id INT, val INT);
@@ -5670,7 +5664,7 @@ DROP TABLE rpr_stress;
-- Tests for error conditions in rpr.c
CREATE TABLE rpr_errors (id INT, val INT);
INSERT INTO rpr_errors VALUES (1, 10), (2, 20);
--- Test: PATTERN variable without DEFINE (A), DEFINE variable not in PATTERN (B)
+-- Test: DEFINE variable not in PATTERN (error)
SELECT id, val, COUNT(*) OVER w FROM rpr_errors
WINDOW w AS (
ORDER BY id
@@ -5679,55 +5673,11 @@ WINDOW w AS (
DEFINE
B AS TRUE
);
- id | val | count
-----+-----+-------
- 1 | 10 | 0
- 2 | 20 | 0
-(2 rows)
-
--- Expected: Success - A is implicitly TRUE, B is filtered out
--- Test: 3 variables in PATTERN, 253 in DEFINE (DEFINE filtering test)
-SELECT COUNT(*) OVER w FROM rpr_errors
-WINDOW w AS (
- ORDER BY id
- ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
- PATTERN (V1 V2 V3)
- DEFINE
- V1 AS val > 0, V2 AS val > 0, V3 AS val > 0, V4 AS val > 0, V5 AS val > 0, V6 AS val > 0, V7 AS val > 0, V8 AS val > 0, V9 AS val > 0, V10 AS val > 0,
- V11 AS val > 0, V12 AS val > 0, V13 AS val > 0, V14 AS val > 0, V15 AS val > 0, V16 AS val > 0, V17 AS val > 0, V18 AS val > 0, V19 AS val > 0, V20 AS val > 0,
- V21 AS val > 0, V22 AS val > 0, V23 AS val > 0, V24 AS val > 0, V25 AS val > 0, V26 AS val > 0, V27 AS val > 0, V28 AS val > 0, V29 AS val > 0, V30 AS val > 0,
- V31 AS val > 0, V32 AS val > 0, V33 AS val > 0, V34 AS val > 0, V35 AS val > 0, V36 AS val > 0, V37 AS val > 0, V38 AS val > 0, V39 AS val > 0, V40 AS val > 0,
- V41 AS val > 0, V42 AS val > 0, V43 AS val > 0, V44 AS val > 0, V45 AS val > 0, V46 AS val > 0, V47 AS val > 0, V48 AS val > 0, V49 AS val > 0, V50 AS val > 0,
- V51 AS val > 0, V52 AS val > 0, V53 AS val > 0, V54 AS val > 0, V55 AS val > 0, V56 AS val > 0, V57 AS val > 0, V58 AS val > 0, V59 AS val > 0, V60 AS val > 0,
- V61 AS val > 0, V62 AS val > 0, V63 AS val > 0, V64 AS val > 0, V65 AS val > 0, V66 AS val > 0, V67 AS val > 0, V68 AS val > 0, V69 AS val > 0, V70 AS val > 0,
- V71 AS val > 0, V72 AS val > 0, V73 AS val > 0, V74 AS val > 0, V75 AS val > 0, V76 AS val > 0, V77 AS val > 0, V78 AS val > 0, V79 AS val > 0, V80 AS val > 0,
- V81 AS val > 0, V82 AS val > 0, V83 AS val > 0, V84 AS val > 0, V85 AS val > 0, V86 AS val > 0, V87 AS val > 0, V88 AS val > 0, V89 AS val > 0, V90 AS val > 0,
- V91 AS val > 0, V92 AS val > 0, V93 AS val > 0, V94 AS val > 0, V95 AS val > 0, V96 AS val > 0, V97 AS val > 0, V98 AS val > 0, V99 AS val > 0, V100 AS val > 0,
- V101 AS val > 0, V102 AS val > 0, V103 AS val > 0, V104 AS val > 0, V105 AS val > 0, V106 AS val > 0, V107 AS val > 0, V108 AS val > 0, V109 AS val > 0, V110 AS val > 0,
- V111 AS val > 0, V112 AS val > 0, V113 AS val > 0, V114 AS val > 0, V115 AS val > 0, V116 AS val > 0, V117 AS val > 0, V118 AS val > 0, V119 AS val > 0, V120 AS val > 0,
- V121 AS val > 0, V122 AS val > 0, V123 AS val > 0, V124 AS val > 0, V125 AS val > 0, V126 AS val > 0, V127 AS val > 0, V128 AS val > 0, V129 AS val > 0, V130 AS val > 0,
- V131 AS val > 0, V132 AS val > 0, V133 AS val > 0, V134 AS val > 0, V135 AS val > 0, V136 AS val > 0, V137 AS val > 0, V138 AS val > 0, V139 AS val > 0, V140 AS val > 0,
- V141 AS val > 0, V142 AS val > 0, V143 AS val > 0, V144 AS val > 0, V145 AS val > 0, V146 AS val > 0, V147 AS val > 0, V148 AS val > 0, V149 AS val > 0, V150 AS val > 0,
- V151 AS val > 0, V152 AS val > 0, V153 AS val > 0, V154 AS val > 0, V155 AS val > 0, V156 AS val > 0, V157 AS val > 0, V158 AS val > 0, V159 AS val > 0, V160 AS val > 0,
- V161 AS val > 0, V162 AS val > 0, V163 AS val > 0, V164 AS val > 0, V165 AS val > 0, V166 AS val > 0, V167 AS val > 0, V168 AS val > 0, V169 AS val > 0, V170 AS val > 0,
- V171 AS val > 0, V172 AS val > 0, V173 AS val > 0, V174 AS val > 0, V175 AS val > 0, V176 AS val > 0, V177 AS val > 0, V178 AS val > 0, V179 AS val > 0, V180 AS val > 0,
- V181 AS val > 0, V182 AS val > 0, V183 AS val > 0, V184 AS val > 0, V185 AS val > 0, V186 AS val > 0, V187 AS val > 0, V188 AS val > 0, V189 AS val > 0, V190 AS val > 0,
- V191 AS val > 0, V192 AS val > 0, V193 AS val > 0, V194 AS val > 0, V195 AS val > 0, V196 AS val > 0, V197 AS val > 0, V198 AS val > 0, V199 AS val > 0, V200 AS val > 0,
- V201 AS val > 0, V202 AS val > 0, V203 AS val > 0, V204 AS val > 0, V205 AS val > 0, V206 AS val > 0, V207 AS val > 0, V208 AS val > 0, V209 AS val > 0, V210 AS val > 0,
- V211 AS val > 0, V212 AS val > 0, V213 AS val > 0, V214 AS val > 0, V215 AS val > 0, V216 AS val > 0, V217 AS val > 0, V218 AS val > 0, V219 AS val > 0, V220 AS val > 0,
- V221 AS val > 0, V222 AS val > 0, V223 AS val > 0, V224 AS val > 0, V225 AS val > 0, V226 AS val > 0, V227 AS val > 0, V228 AS val > 0, V229 AS val > 0, V230 AS val > 0,
- V231 AS val > 0, V232 AS val > 0, V233 AS val > 0, V234 AS val > 0, V235 AS val > 0, V236 AS val > 0, V237 AS val > 0, V238 AS val > 0, V239 AS val > 0, V240 AS val > 0,
- V241 AS val > 0, V242 AS val > 0, V243 AS val > 0, V244 AS val > 0, V245 AS val > 0, V246 AS val > 0, V247 AS val > 0, V248 AS val > 0, V249 AS val > 0, V250 AS val > 0,
- V251 AS val > 0, V252 AS val > 0, V253 AS val > 0
-);
- count
--------
- 0
- 0
-(2 rows)
-
--- Expected: Success - unused DEFINE variables are filtered out
--- Test: 251 variables in PATTERN, 252 in DEFINE (boundary - should succeed)
+ERROR: DEFINE variable "b" is not used in PATTERN
+LINE 7: B AS TRUE
+ ^
+-- Expected: Error - B is not used in PATTERN
+-- Test: 251 variables in PATTERN and DEFINE (boundary - should succeed)
SELECT COUNT(*) OVER w FROM rpr_errors
WINDOW w AS (
ORDER BY id
@@ -5759,7 +5709,7 @@ WINDOW w AS (
V221 AS val > 0, V222 AS val > 0, V223 AS val > 0, V224 AS val > 0, V225 AS val > 0, V226 AS val > 0, V227 AS val > 0, V228 AS val > 0, V229 AS val > 0, V230 AS val > 0,
V231 AS val > 0, V232 AS val > 0, V233 AS val > 0, V234 AS val > 0, V235 AS val > 0, V236 AS val > 0, V237 AS val > 0, V238 AS val > 0, V239 AS val > 0, V240 AS val > 0,
V241 AS val > 0, V242 AS val > 0, V243 AS val > 0, V244 AS val > 0, V245 AS val > 0, V246 AS val > 0, V247 AS val > 0, V248 AS val > 0, V249 AS val > 0, V250 AS val > 0,
- V251 AS val > 0, V252 AS val > 0
+ V251 AS val > 0
);
count
-------
@@ -5767,7 +5717,7 @@ WINDOW w AS (
0
(2 rows)
--- Expected: Success - unused DEFINE variables are filtered out
+-- Expected: Success - exactly at RPR_VARID_MAX boundary
-- Test: 252 variables in PATTERN, 251 in DEFINE (exceeds limit with implicit TRUE)
SELECT COUNT(*) OVER w FROM rpr_errors
WINDOW w AS (
diff --git a/src/test/regress/sql/rpr_base.sql b/src/test/regress/sql/rpr_base.sql
index e54d54e400a..cf6c062ae85 100644
--- a/src/test/regress/sql/rpr_base.sql
+++ b/src/test/regress/sql/rpr_base.sql
@@ -3639,7 +3639,7 @@ DROP TABLE rpr_stress;
CREATE TABLE rpr_errors (id INT, val INT);
INSERT INTO rpr_errors VALUES (1, 10), (2, 20);
--- Test: PATTERN variable without DEFINE (A), DEFINE variable not in PATTERN (B)
+-- Test: DEFINE variable not in PATTERN (error)
SELECT id, val, COUNT(*) OVER w FROM rpr_errors
WINDOW w AS (
ORDER BY id
@@ -3648,45 +3648,9 @@ WINDOW w AS (
DEFINE
B AS TRUE
);
--- Expected: Success - A is implicitly TRUE, B is filtered out
+-- Expected: Error - B is not used in PATTERN
--- Test: 3 variables in PATTERN, 253 in DEFINE (DEFINE filtering test)
-SELECT COUNT(*) OVER w FROM rpr_errors
-WINDOW w AS (
- ORDER BY id
- ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
- PATTERN (V1 V2 V3)
- DEFINE
- V1 AS val > 0, V2 AS val > 0, V3 AS val > 0, V4 AS val > 0, V5 AS val > 0, V6 AS val > 0, V7 AS val > 0, V8 AS val > 0, V9 AS val > 0, V10 AS val > 0,
- V11 AS val > 0, V12 AS val > 0, V13 AS val > 0, V14 AS val > 0, V15 AS val > 0, V16 AS val > 0, V17 AS val > 0, V18 AS val > 0, V19 AS val > 0, V20 AS val > 0,
- V21 AS val > 0, V22 AS val > 0, V23 AS val > 0, V24 AS val > 0, V25 AS val > 0, V26 AS val > 0, V27 AS val > 0, V28 AS val > 0, V29 AS val > 0, V30 AS val > 0,
- V31 AS val > 0, V32 AS val > 0, V33 AS val > 0, V34 AS val > 0, V35 AS val > 0, V36 AS val > 0, V37 AS val > 0, V38 AS val > 0, V39 AS val > 0, V40 AS val > 0,
- V41 AS val > 0, V42 AS val > 0, V43 AS val > 0, V44 AS val > 0, V45 AS val > 0, V46 AS val > 0, V47 AS val > 0, V48 AS val > 0, V49 AS val > 0, V50 AS val > 0,
- V51 AS val > 0, V52 AS val > 0, V53 AS val > 0, V54 AS val > 0, V55 AS val > 0, V56 AS val > 0, V57 AS val > 0, V58 AS val > 0, V59 AS val > 0, V60 AS val > 0,
- V61 AS val > 0, V62 AS val > 0, V63 AS val > 0, V64 AS val > 0, V65 AS val > 0, V66 AS val > 0, V67 AS val > 0, V68 AS val > 0, V69 AS val > 0, V70 AS val > 0,
- V71 AS val > 0, V72 AS val > 0, V73 AS val > 0, V74 AS val > 0, V75 AS val > 0, V76 AS val > 0, V77 AS val > 0, V78 AS val > 0, V79 AS val > 0, V80 AS val > 0,
- V81 AS val > 0, V82 AS val > 0, V83 AS val > 0, V84 AS val > 0, V85 AS val > 0, V86 AS val > 0, V87 AS val > 0, V88 AS val > 0, V89 AS val > 0, V90 AS val > 0,
- V91 AS val > 0, V92 AS val > 0, V93 AS val > 0, V94 AS val > 0, V95 AS val > 0, V96 AS val > 0, V97 AS val > 0, V98 AS val > 0, V99 AS val > 0, V100 AS val > 0,
- V101 AS val > 0, V102 AS val > 0, V103 AS val > 0, V104 AS val > 0, V105 AS val > 0, V106 AS val > 0, V107 AS val > 0, V108 AS val > 0, V109 AS val > 0, V110 AS val > 0,
- V111 AS val > 0, V112 AS val > 0, V113 AS val > 0, V114 AS val > 0, V115 AS val > 0, V116 AS val > 0, V117 AS val > 0, V118 AS val > 0, V119 AS val > 0, V120 AS val > 0,
- V121 AS val > 0, V122 AS val > 0, V123 AS val > 0, V124 AS val > 0, V125 AS val > 0, V126 AS val > 0, V127 AS val > 0, V128 AS val > 0, V129 AS val > 0, V130 AS val > 0,
- V131 AS val > 0, V132 AS val > 0, V133 AS val > 0, V134 AS val > 0, V135 AS val > 0, V136 AS val > 0, V137 AS val > 0, V138 AS val > 0, V139 AS val > 0, V140 AS val > 0,
- V141 AS val > 0, V142 AS val > 0, V143 AS val > 0, V144 AS val > 0, V145 AS val > 0, V146 AS val > 0, V147 AS val > 0, V148 AS val > 0, V149 AS val > 0, V150 AS val > 0,
- V151 AS val > 0, V152 AS val > 0, V153 AS val > 0, V154 AS val > 0, V155 AS val > 0, V156 AS val > 0, V157 AS val > 0, V158 AS val > 0, V159 AS val > 0, V160 AS val > 0,
- V161 AS val > 0, V162 AS val > 0, V163 AS val > 0, V164 AS val > 0, V165 AS val > 0, V166 AS val > 0, V167 AS val > 0, V168 AS val > 0, V169 AS val > 0, V170 AS val > 0,
- V171 AS val > 0, V172 AS val > 0, V173 AS val > 0, V174 AS val > 0, V175 AS val > 0, V176 AS val > 0, V177 AS val > 0, V178 AS val > 0, V179 AS val > 0, V180 AS val > 0,
- V181 AS val > 0, V182 AS val > 0, V183 AS val > 0, V184 AS val > 0, V185 AS val > 0, V186 AS val > 0, V187 AS val > 0, V188 AS val > 0, V189 AS val > 0, V190 AS val > 0,
- V191 AS val > 0, V192 AS val > 0, V193 AS val > 0, V194 AS val > 0, V195 AS val > 0, V196 AS val > 0, V197 AS val > 0, V198 AS val > 0, V199 AS val > 0, V200 AS val > 0,
- V201 AS val > 0, V202 AS val > 0, V203 AS val > 0, V204 AS val > 0, V205 AS val > 0, V206 AS val > 0, V207 AS val > 0, V208 AS val > 0, V209 AS val > 0, V210 AS val > 0,
- V211 AS val > 0, V212 AS val > 0, V213 AS val > 0, V214 AS val > 0, V215 AS val > 0, V216 AS val > 0, V217 AS val > 0, V218 AS val > 0, V219 AS val > 0, V220 AS val > 0,
- V221 AS val > 0, V222 AS val > 0, V223 AS val > 0, V224 AS val > 0, V225 AS val > 0, V226 AS val > 0, V227 AS val > 0, V228 AS val > 0, V229 AS val > 0, V230 AS val > 0,
- V231 AS val > 0, V232 AS val > 0, V233 AS val > 0, V234 AS val > 0, V235 AS val > 0, V236 AS val > 0, V237 AS val > 0, V238 AS val > 0, V239 AS val > 0, V240 AS val > 0,
- V241 AS val > 0, V242 AS val > 0, V243 AS val > 0, V244 AS val > 0, V245 AS val > 0, V246 AS val > 0, V247 AS val > 0, V248 AS val > 0, V249 AS val > 0, V250 AS val > 0,
- V251 AS val > 0, V252 AS val > 0, V253 AS val > 0
-);
--- Expected: Success - unused DEFINE variables are filtered out
-
--- Test: 251 variables in PATTERN, 252 in DEFINE (boundary - should succeed)
+-- Test: 251 variables in PATTERN and DEFINE (boundary - should succeed)
SELECT COUNT(*) OVER w FROM rpr_errors
WINDOW w AS (
ORDER BY id
@@ -3718,9 +3682,9 @@ WINDOW w AS (
V221 AS val > 0, V222 AS val > 0, V223 AS val > 0, V224 AS val > 0, V225 AS val > 0, V226 AS val > 0, V227 AS val > 0, V228 AS val > 0, V229 AS val > 0, V230 AS val > 0,
V231 AS val > 0, V232 AS val > 0, V233 AS val > 0, V234 AS val > 0, V235 AS val > 0, V236 AS val > 0, V237 AS val > 0, V238 AS val > 0, V239 AS val > 0, V240 AS val > 0,
V241 AS val > 0, V242 AS val > 0, V243 AS val > 0, V244 AS val > 0, V245 AS val > 0, V246 AS val > 0, V247 AS val > 0, V248 AS val > 0, V249 AS val > 0, V250 AS val > 0,
- V251 AS val > 0, V252 AS val > 0
+ V251 AS val > 0
);
--- Expected: Success - unused DEFINE variables are filtered out
+-- Expected: Success - exactly at RPR_VARID_MAX boundary
-- Test: 252 variables in PATTERN, 251 in DEFINE (exceeds limit with implicit TRUE)
SELECT COUNT(*) OVER w FROM rpr_errors
--
2.50.1 (Apple Git-155)
[text/plain] nocfbot-0009-Clarify-RPR-documentation-advanced-sgml.txt (2.5K, 11-nocfbot-0009-Clarify-RPR-documentation-advanced-sgml.txt)
download | inline diff:
From c4943f2f4fbe264238b806e49e3ab486e15534dc Mon Sep 17 00:00:00 2001
From: Henson Choi <[email protected]>
Date: Fri, 20 Mar 2026 20:45:20 +0900
Subject: [PATCH] Clarify RPR documentation in advanced.sgml
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
Make the absorption optimization paragraph explicitly reference the
O(n²) complexity it mitigates, rather than using an ambiguous "this"
that could be misread as referring to the pattern simplification
paragraph immediately above.
Also clarify the aggregate behavior description for non-starting rows:
replace the vague "NULL or 0 depending on its aggregation definition"
with concrete examples (count() returns 0, sum() returns NULL).
---
doc/src/sgml/advanced.sgml | 12 +++++++-----
1 file changed, 7 insertions(+), 5 deletions(-)
diff --git a/doc/src/sgml/advanced.sgml b/doc/src/sgml/advanced.sgml
index 3e696eefc66..1336f2daa14 100644
--- a/doc/src/sgml/advanced.sgml
+++ b/doc/src/sgml/advanced.sgml
@@ -585,10 +585,11 @@ DEFINE
rows which satisfies the PATTERN is found, in the starting row all columns
or functions are shown in the target list. Note that aggregations only
look into the matched rows, rather than the whole frame. On the second or
- subsequent rows all window functions are shown as NULL. Aggregates are
- NULL or 0 depending on its aggregation definition. A count() aggregate
- shows 0. For rows that do not match on the PATTERN, columns are shown AS
- NULL too. Example of a <literal>SELECT</literal> using
+ subsequent rows all window functions are shown as NULL. Aggregates on
+ non-starting rows return their initial value: for example,
+ <function>count()</function> returns 0 and <function>sum()</function>
+ returns NULL. For rows that do not match the PATTERN, columns are shown
+ as NULL too. Example of a <literal>SELECT</literal> using
the <literal>DEFINE</literal> and <literal>PATTERN</literal> clause is as
follows.
@@ -653,7 +654,8 @@ FROM stock
</para>
<para>
- To mitigate this, <productname>PostgreSQL</productname> employs
+ To mitigate the O(n<superscript>2</superscript>) complexity described
+ above, <productname>PostgreSQL</productname> also employs
a context absorption optimization. When a pattern starts with a greedy
unbounded element, newer matching contexts cannot produce longer matches
than older contexts. By detecting and eliminating these redundant
--
2.50.1 (Apple Git-155)
[text/plain] nocfbot-0010-Fix-typos-in-RPR-comments.txt (2.6K, 12-nocfbot-0010-Fix-typos-in-RPR-comments.txt)
download | inline diff:
From ce9d9867146c9d1fade273ef5b3a9fe15279200c Mon Sep 17 00:00:00 2001
From: Henson Choi <[email protected]>
Date: Fri, 20 Mar 2026 21:15:03 +0900
Subject: [PATCH] Fix typos in RPR comments and parser README
Fix "do lopp" to "do loop" and "successfullt" to "successfully" in
nodeWindowAgg.c. Also fix tab-space mix for the parse_rpr.c entry
in parser/README.
---
src/backend/executor/nodeWindowAgg.c | 7 ++++---
src/backend/parser/README | 2 +-
2 files changed, 5 insertions(+), 4 deletions(-)
diff --git a/src/backend/executor/nodeWindowAgg.c b/src/backend/executor/nodeWindowAgg.c
index d144fa39375..942f071b457 100644
--- a/src/backend/executor/nodeWindowAgg.c
+++ b/src/backend/executor/nodeWindowAgg.c
@@ -3729,7 +3729,7 @@ ignorenulls_getfuncarginframe(WindowObject winobj, int argno,
* contiguous. So we can do the check followings safely. Note,
* however, it is possible that a row is out of reduced frame if
* there's a NULL in the middle. So we need to check it in the
- * following do lopp.
+ * following do loop.
*/
if (seektype == WINDOW_SEEK_HEAD && relpos >= num_reduced_frame)
goto out_of_frame;
@@ -4704,7 +4704,8 @@ WinGetFuncArgInFrame(WindowObject winobj, int argno,
/*
* When RPR is active, the reduced frame changes row by row, so we must
- * not advance the mark — doing so would prevent revisiting earlier rows.
+ * not advance the mark — doing so would prevent revisiting earlier
+ * rows.
*/
if (rpr_is_defined(winstate))
set_mark = false;
@@ -4739,7 +4740,7 @@ WinGetFuncArgInFrame(WindowObject winobj, int argno,
* isout: output argument, set to indicate whether target row position
* is out of frame (can pass NULL if caller doesn't care about this)
*
- * Returns 0 if we successfullt got the slot. false if out of frame.
+ * Returns 0 if we successfully got the slot. false if out of frame.
* (also isout is set)
*/
static int
diff --git a/src/backend/parser/README b/src/backend/parser/README
index 2baffa9517e..22a5e91c8cf 100644
--- a/src/backend/parser/README
+++ b/src/backend/parser/README
@@ -26,7 +26,7 @@ parse_node.c create nodes for various structures
parse_oper.c handle operators in expressions
parse_param.c handle Params (for the cases used in the core backend)
parse_relation.c support routines for tables and column handling
-parse_rpr.c handle Row Pattern Recognition
+parse_rpr.c handle Row Pattern Recognition
parse_target.c handle the result list of the query
parse_type.c support routines for data type handling
parse_utilcmd.c parse analysis for utility commands (done at execution time)
--
2.50.1 (Apple Git-155)
[text/plain] nocfbot-0011-Clarify-excludeLocation-and-empty-quantifier.txt (2.2K, 13-nocfbot-0011-Clarify-excludeLocation-and-empty-quantifier.txt)
download | inline diff:
From 7b9d7fb735a467ff391898ed4ebc11bd6ef315fa Mon Sep 17 00:00:00 2001
From: Henson Choi <[email protected]>
Date: Fri, 20 Mar 2026 21:21:58 +0900
Subject: [PATCH] Add clarifying comments for excludeLocation and empty
quantifier in gram.y
Add comments explaining that excludeLocation is set to -1 when no
EXCLUDE clause is present (opt_window_exclusion_clause returns 0),
and that the EMPTY quantifier rule's @$ location is unused since
min=max=1 never produces an error.
---
src/backend/parser/gram.y | 7 ++++++-
1 file changed, 6 insertions(+), 1 deletion(-)
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 9dcac63bdc7..6d390258532 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -16757,6 +16757,7 @@ opt_frame_clause:
n->frameOptions |= FRAMEOPTION_NONDEFAULT | FRAMEOPTION_RANGE;
n->frameOptions |= $3;
n->frameLocation = @1;
+ /* -1 when no EXCLUDE clause (opt_window_exclusion_clause returns 0) */
n->excludeLocation = ($3 != 0) ? @3 : -1;
$$ = n;
}
@@ -16767,6 +16768,7 @@ opt_frame_clause:
n->frameOptions |= FRAMEOPTION_NONDEFAULT | FRAMEOPTION_ROWS;
n->frameOptions |= $3;
n->frameLocation = @1;
+ /* -1 when no EXCLUDE clause (opt_window_exclusion_clause returns 0) */
n->excludeLocation = ($3 != 0) ? @3 : -1;
$$ = n;
}
@@ -16777,6 +16779,7 @@ opt_frame_clause:
n->frameOptions |= FRAMEOPTION_NONDEFAULT | FRAMEOPTION_GROUPS;
n->frameOptions |= $3;
n->frameLocation = @1;
+ /* -1 when no EXCLUDE clause (opt_window_exclusion_clause returns 0) */
n->excludeLocation = ($3 != 0) ? @3 : -1;
$$ = n;
}
@@ -17069,7 +17072,9 @@ row_pattern_primary:
;
row_pattern_quantifier_opt:
- /* EMPTY */ { $$ = (Node *) makeRPRQuantifier(1, 1, -1, @$, yyscanner); }
+ /* EMPTY - no quantifier means exactly once; @$ is unused since
+ * min=max=1 never produces an error */
+ { $$ = (Node *) makeRPRQuantifier(1, 1, -1, @$, yyscanner); }
| '*' { $$ = (Node *) makeRPRQuantifier(0, INT_MAX, -1, @1, yyscanner); }
| '+' { $$ = (Node *) makeRPRQuantifier(1, INT_MAX, -1, @1, yyscanner); }
| Op
--
2.50.1 (Apple Git-155)
[text/plain] nocfbot-0012-Clarify-RPR_VARID_MAX-definition.txt (1.2K, 14-nocfbot-0012-Clarify-RPR_VARID_MAX-definition.txt)
download | inline diff:
From f7d0cd30573e7ca2ab72c80a4ed6537b5451cdb4 Mon Sep 17 00:00:00 2001
From: Henson Choi <[email protected]>
Date: Fri, 20 Mar 2026 21:27:33 +0900
Subject: [PATCH] Clarify RPR_VARID_MAX definition in rpr.h
RPR_VARID_MAX = 251 allows varId 0 through 250, giving a maximum of
251 unique pattern variables. Values from 252 onward are reserved for
control elements (BEGIN, END, ALT, FIN). Add a comment explaining this
layout.
---
src/include/optimizer/rpr.h | 6 +++++-
1 file changed, 5 insertions(+), 1 deletion(-)
diff --git a/src/include/optimizer/rpr.h b/src/include/optimizer/rpr.h
index f93a128096b..e78092678bb 100644
--- a/src/include/optimizer/rpr.h
+++ b/src/include/optimizer/rpr.h
@@ -17,7 +17,11 @@
#include "nodes/plannodes.h"
/* Limits and special values */
-#define RPR_VARID_MAX 251 /* max pattern variables: 251 */
+/*
+ * Maximum number of unique pattern variables (varId 0 to RPR_VARID_MAX - 1).
+ * Values from RPR_VARID_BEGIN (252) onward are reserved for control elements.
+ */
+#define RPR_VARID_MAX 251
#define RPR_QUANTITY_INF INT32_MAX /* unbounded quantifier */
#define RPR_COUNT_MAX INT32_MAX /* max runtime count (NFA state) */
#define RPR_ELEMIDX_MAX INT16_MAX /* max pattern elements */
--
2.50.1 (Apple Git-155)
[text/plain] nocfbot-0013-Move-variables-to-function-scope.txt (1.2K, 15-nocfbot-0013-Move-variables-to-function-scope.txt)
download | inline diff:
From 0e944668568a73c3387636e29dc52bd056907437 Mon Sep 17 00:00:00 2001
From: Henson Choi <[email protected]>
Date: Fri, 20 Mar 2026 21:40:05 +0900
Subject: [PATCH] Move local variables to function scope in
row_is_in_reduced_frame
Variables i and num_reduced_rows were declared inside the switch block
before the first case label. Move them to the function top to follow
PostgreSQL coding conventions.
---
src/backend/executor/nodeWindowAgg.c | 5 ++---
1 file changed, 2 insertions(+), 3 deletions(-)
diff --git a/src/backend/executor/nodeWindowAgg.c b/src/backend/executor/nodeWindowAgg.c
index 942f071b457..6575cf9dd96 100644
--- a/src/backend/executor/nodeWindowAgg.c
+++ b/src/backend/executor/nodeWindowAgg.c
@@ -3969,6 +3969,8 @@ row_is_in_reduced_frame(WindowObject winobj, int64 pos)
WindowAggState *winstate = winobj->winstate;
int state;
int rtn;
+ int64 i;
+ int num_reduced_rows;
if (!rpr_is_defined(winstate))
{
@@ -3996,9 +3998,6 @@ row_is_in_reduced_frame(WindowObject winobj, int64 pos)
switch (state)
{
- int64 i;
- int num_reduced_rows;
-
case RF_FRAME_HEAD:
num_reduced_rows = 1;
for (i = pos + 1;
--
2.50.1 (Apple Git-155)
[text/plain] nocfbot-0014-Reset-reduced_frame_map-in-release_partition.txt (1.1K, 16-nocfbot-0014-Reset-reduced_frame_map-in-release_partition.txt)
download | inline diff:
From 6fbd1e03665f9c65d20d8159cd2923e46f25bff9 Mon Sep 17 00:00:00 2001
From: Henson Choi <[email protected]>
Date: Fri, 20 Mar 2026 21:48:36 +0900
Subject: [PATCH] Reset reduced_frame_map pointer in release_partition
After MemoryContextReset(partcontext), reduced_frame_map becomes a
dangling pointer. Set it to NULL and reset alloc_sz to zero so that
create_reduced_frame_map starts fresh for the next partition.
---
src/backend/executor/nodeWindowAgg.c | 4 ++++
1 file changed, 4 insertions(+)
diff --git a/src/backend/executor/nodeWindowAgg.c b/src/backend/executor/nodeWindowAgg.c
index 6575cf9dd96..f1f9d60b39d 100644
--- a/src/backend/executor/nodeWindowAgg.c
+++ b/src/backend/executor/nodeWindowAgg.c
@@ -1580,6 +1580,10 @@ release_partition(WindowAggState *winstate)
winstate->partition_spooled = false;
winstate->next_partition = true;
+ /* Reset RPR reduced frame map */
+ winstate->reduced_frame_map = NULL;
+ winstate->alloc_sz = 0;
+
/* Reset NFA state for new partition */
winstate->nfaContext = NULL;
winstate->nfaContextTail = NULL;
--
2.50.1 (Apple Git-155)
[text/plain] nocfbot-0015-Remove-redundant-nfa_add_matched_state.txt (1.6K, 17-nocfbot-0015-Remove-redundant-nfa_add_matched_state.txt)
download | inline diff:
From a224ed18261a09d5b1e65aba3c97fc5741234589 Mon Sep 17 00:00:00 2001
From: Henson Choi <[email protected]>
Date: Fri, 20 Mar 2026 22:11:30 +0900
Subject: [PATCH] Remove redundant list manipulation in nfa_add_matched_state
nfa_add_matched_state() manually unlinked pruned contexts before
calling ExecRPRFreeContext(), which internally calls nfa_unlink_context()
to perform the same list operations. Remove the manual forward-link
update and the post-loop tail fixup, letting nfa_unlink_context()
handle forward link, backward link, and tail update consistently.
---
src/backend/executor/execRPR.c | 6 +-----
1 file changed, 1 insertion(+), 5 deletions(-)
diff --git a/src/backend/executor/execRPR.c b/src/backend/executor/execRPR.c
index a0a462256ad..bab5257f68f 100644
--- a/src/backend/executor/execRPR.c
+++ b/src/backend/executor/execRPR.c
@@ -1808,14 +1808,12 @@ nfa_add_matched_state(WindowAggState *winstate, RPRNFAContext *ctx,
/* Prune contexts that started within this match's range */
if (winstate->rpSkipTo == ST_PAST_LAST_ROW)
{
- RPRNFAContext *nextCtx;
int64 skippedLen;
while (ctx->next != NULL &&
ctx->next->matchStartRow <= matchEndRow)
{
- nextCtx = ctx->next;
- ctx->next = ctx->next->next;
+ RPRNFAContext *nextCtx = ctx->next;
Assert(nextCtx->lastProcessedRow >= nextCtx->matchStartRow);
skippedLen = nextCtx->lastProcessedRow - nextCtx->matchStartRow + 1;
@@ -1823,8 +1821,6 @@ nfa_add_matched_state(WindowAggState *winstate, RPRNFAContext *ctx,
ExecRPRFreeContext(winstate, nextCtx);
}
- if (ctx->next == NULL)
- winstate->nfaContextTail = ctx;
}
}
--
2.50.1 (Apple Git-155)
[text/plain] nocfbot-experimental-Implement-1-slot-PREV-NEXT-navigation.txt (85.3K, 18-nocfbot-experimental-Implement-1-slot-PREV-NEXT-navigation.txt)
download
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: Row pattern recognition
In-Reply-To: <CAAAe_zBbrnx2fjK2s+Jgx6TSOdnKAPawXbHeX49WqmX9ji+Hdg@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox