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: Sun, 29 Mar 2026 21:13:47 +0900
Message-ID: <CAAAe_zBCF3dwSjStmG0kJqw_y1z8QD73Rf1G58QTKEvd9tScwA@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CAAAe_zDmjV-R6M0xeMDzZN+eRVUhsrt6NA=xjvj9BQMog9tqTQ@mail.gmail.com>
<[email protected]>
<CAAAe_zDneX+WWEgoM+rXLg+H2OK_VuXoiipzJu2Q0rCSSCzY8g@mail.gmail.com>
<[email protected]>
Hi Tatsuo,
Attached are 6 incremental patches on top of v46.
0001-0005 are small independent fixes. 0006 is the main
PREV/NEXT navigation redesign that was discussed as the
"experimental" implementation in prior messages.
Here is a summary of each patch:
0001: Remove unused regex/regex.h include from nodeWindowAgg.c
The regex header was left over from an earlier implementation
that used the regex engine for pattern matching. The current
NFA engine in execRPR.c does not use it.
0002: Add CHECK_FOR_INTERRUPTS() to nfa_add_state_unique()
This function iterates through a linked list of NFA states
to check for duplicates. In state explosion scenarios
(complex patterns with alternations and quantifiers), this
list can grow significantly. Without an interrupt check,
the loop is unresponsive to query cancellation.
0003: Add CHECK_FOR_INTERRUPTS() to nfa_try_absorb_context()
Similar to 0002. The absorption loop iterates through the
context chain, which can grow unbounded with streaming
window patterns. Each iteration also calls
nfa_states_covered() which has its own loop.
0004: Fix in-place modification of defineClause TargetEntry
In set_upper_references(), the defineClause TargetEntry
was modified in-place by fix_upper_expr() without making
a copy first. This follows the same flatCopyTargetEntry()
pattern already used for the main targetlist in the same
function.
0005: Fix mark handling for last_value() under RPR
This is a revised version of the v45 0004 patch that you
commented on [1]. You pointed out that suppressing
WinSetMarkPosition() entirely under RPR was too strong a
limitation, and we agreed to drop it and revisit together
with the experimental PREV/NEXT patch.
The RPR executor patch changed set_mark from true to
false in window_last_value() to avoid mark advancement
problems under RPR. But this also penalizes non-RPR
queries by preventing tuplestore memory reclamation.
The revised approach: restore set_mark=true (upstream
default), and add a targeted guard in
WinGetFuncArgInFrame() that only suppresses mark
advancement for SEEK_TAIL under RPR -- not for all
seek types as in v45 0004. Only SEEK_TAIL needs
suppression because advancing the mark from the tail
could prevent revisiting earlier rows that still fall
within a future row's reduced frame.
[1]
https://www.postgresql.org/message-id/20260321.140232.1947157589839395257.ishii%40postgresql.org
0006: Implement 1-slot PREV/NEXT navigation for RPR
This is the main patch. It redesigns PREV/NEXT navigation
from the 3-slot model (outer/scan/inner with varno
rewriting) to a 1-slot model using expression opcodes.
Key changes:
- RPRNavExpr: a new expression node type that replaces
the previous approach of identifying PREV/NEXT by
funcid. The parser transforms PREV/NEXT function calls
into RPRNavExpr nodes in ParseFuncOrColumn().
- EEOP_RPR_NAV_SET/RESTORE: two new opcodes that
temporarily swap ecxt_outertuple to the target row
during expression evaluation. The argument expression
evaluates against the swapped slot, then the original
slot is restored. This eliminates varno rewriting and
naturally supports arbitrary offsets.
- 2-argument form: PREV(value, offset) and
NEXT(value, offset) are now supported. The offset
defaults to 1 if omitted; offset=0 refers to the
current row. The offset must be a run-time constant
per the SQL standard.
- nav_winobj: a dedicated WindowObject with its own
tuplestore read pointer, separate from aggregate
processing. A mark pointer pinned at position 0
prevents tuplestore truncation so that PREV(expr, N)
can reach any prior row. This is conservative -- it
retains the entire partition -- but since the standard
requires offsets to be run-time constants, we could
advance the mark to (currentpos - max_offset) in a
future optimization. I'd prefer to defer that until
the basic navigation is stable.
- Documentation and tests updated.
Changes from the experimental version of 0006:
- NULL/negative offset error rationale changed from "matching
Oracle behavior" to the SQL standard (ISO/IEC 9075-2,
Subclause 5.6.2: "There is an exception if the value of
the offset is negative or null"). The behavior is the same;
only the comment was corrected.
- RPRNavKind changed from -1/+1 values to plain enum values.
The previous version used kind as an arithmetic multiplier
(offset * kind), but this pattern cannot extend to FIRST/LAST
which have different position semantics. The new version uses
a switch statement with pg_sub/add_s64_overflow instead.
- Parser validation walkers consolidated from three separate
walkers into a single NavCheckResult struct + nav_check_walker,
reducing tree traversals from up to 4 per RPRNavExpr to 1-2.
- JIT changed from attempting to compile to explicit interpreter
fallback. See item 2 below for the rationale.
- Additional test cases for subquery offset (caught by
DEFINE-level restriction), first-arg subquery, and first-arg
volatile function (allowed per standard).
I've reviewed and revised 0006 since the experimental version.
I think it is now ready to be included in the next version of
the patch set. It passes all existing regression tests (rpr,
rpr_base, rpr_explain, rpr_nfa) and adds comprehensive tests
for the new functionality.
Two items I'd like your opinion on:
1. 0005 (mark handling): The revised approach only suppresses
mark advancement for SEEK_TAIL under RPR, unlike v45 0004
which suppressed it for all seek types. Does narrowing to
SEEK_TAIL seem reasonable to you, or do you see cases where
other seek types could also be affected?
2. LLVM JIT fallback (in 0006): The mid-expression slot swap
conflicts with how JIT caches tuple data pointers. The
experimental version produced wrong results under
jit_above_cost=0, and I have not found a clean fix within
the current JIT framework. For now, DEFINE expressions
containing PREV/NEXT fall back to the interpreter; other
expressions in the same query are still JIT-compiled.
I'd like to keep this as-is for now and look for a proper
JIT solution over time. Does that sound reasonable?
Best regards,
Henson
From da0ce590d45e35be96ddf74f3eb9b657bfca4a81 Mon Sep 17 00:00:00 2001
From: Henson Choi <[email protected]>
Date: Tue, 24 Mar 2026 19:04:19 +0900
Subject: [PATCH 1/6] Remove unused regex/regex.h include from nodeWindowAgg.c
---
src/backend/executor/nodeWindowAgg.c | 1 -
1 file changed, 1 deletion(-)
diff --git a/src/backend/executor/nodeWindowAgg.c b/src/backend/executor/nodeWindowAgg.c
index 4f882b877b1..185d7a0d5ae 100644
--- a/src/backend/executor/nodeWindowAgg.c
+++ b/src/backend/executor/nodeWindowAgg.c
@@ -50,7 +50,6 @@
#include "optimizer/rpr.h"
#include "parser/parse_agg.h"
#include "parser/parse_coerce.h"
-#include "regex/regex.h"
#include "utils/acl.h"
#include "utils/builtins.h"
#include "utils/datum.h"
--
2.50.1 (Apple Git-155)
From 8ab2e3b303422c36185210042bbe6831d50009cc Mon Sep 17 00:00:00 2001
From: Henson Choi <[email protected]>
Date: Wed, 25 Mar 2026 00:25:40 +0900
Subject: [PATCH 2/6] Add CHECK_FOR_INTERRUPTS() to nfa_add_state_unique() for
state explosion patterns
---
src/backend/executor/execRPR.c | 2 ++
1 file changed, 2 insertions(+)
diff --git a/src/backend/executor/execRPR.c b/src/backend/executor/execRPR.c
index bab5257f68f..cf54e0c76c3 100644
--- a/src/backend/executor/execRPR.c
+++ b/src/backend/executor/execRPR.c
@@ -1763,6 +1763,8 @@ nfa_add_state_unique(WindowAggState *winstate, RPRNFAContext *ctx, RPRNFAState *
/* Check for duplicate and find tail */
for (s = ctx->states; s != NULL; s = s->next)
{
+ CHECK_FOR_INTERRUPTS();
+
if (nfa_states_equal(winstate, s, state))
{
/*
--
2.50.1 (Apple Git-155)
From dd30851691ade32ea18b50501b06da72839687e3 Mon Sep 17 00:00:00 2001
From: Henson Choi <[email protected]>
Date: Wed, 25 Mar 2026 11:03:39 +0900
Subject: [PATCH 3/6] Add CHECK_FOR_INTERRUPTS() to nfa_try_absorb_context()
loop
---
src/backend/executor/execRPR.c | 2 ++
1 file changed, 2 insertions(+)
diff --git a/src/backend/executor/execRPR.c b/src/backend/executor/execRPR.c
index cf54e0c76c3..58f9da0b814 100644
--- a/src/backend/executor/execRPR.c
+++ b/src/backend/executor/execRPR.c
@@ -2084,6 +2084,8 @@ nfa_try_absorb_context(WindowAggState *winstate, RPRNFAContext *ctx)
for (older = ctx->prev; older != NULL; older = older->prev)
{
+ CHECK_FOR_INTERRUPTS();
+
/*
* By invariant: ctx->prev chain is in creation order (oldest first),
* and each row creates at most one context. So all contexts in this
--
2.50.1 (Apple Git-155)
From fb824d76d6635e624da5cdee1a481d61707e73bd Mon Sep 17 00:00:00 2001
From: Henson Choi <[email protected]>
Date: Wed, 25 Mar 2026 00:20:05 +0900
Subject: [PATCH 4/6] Fix in-place modification of defineClause TargetEntry in
setrefs.c
---
src/backend/optimizer/plan/setrefs.c | 2 ++
1 file changed, 2 insertions(+)
diff --git a/src/backend/optimizer/plan/setrefs.c b/src/backend/optimizer/plan/setrefs.c
index 69cd1861e9b..9cdb217a047 100644
--- a/src/backend/optimizer/plan/setrefs.c
+++ b/src/backend/optimizer/plan/setrefs.c
@@ -2646,6 +2646,7 @@ set_upper_references(PlannerInfo *root, Plan *plan, int rtoffset)
{
TargetEntry *tle = (TargetEntry *) lfirst(l);
+ tle = flatCopyTargetEntry(tle);
tle->expr = (Expr *)
fix_upper_expr(root,
(Node *) tle->expr,
@@ -2654,6 +2655,7 @@ set_upper_references(PlannerInfo *root, Plan *plan, int rtoffset)
rtoffset,
NRM_EQUAL,
NUM_EXEC_QUAL(plan));
+ lfirst(l) = tle;
}
}
}
--
2.50.1 (Apple Git-155)
From 344fbb8a5d39a53f7ce6ff924a06d4e4d3ec2b57 Mon Sep 17 00:00:00 2001
From: Henson Choi <[email protected]>
Date: Wed, 25 Mar 2026 00:37:50 +0900
Subject: [PATCH 5/6] Fix mark handling for last_value() under RPR
Enable mark advancement in window_last_value() for
better tuplestore memory usage in non-RPR cases, while
adding a guard in WinGetFuncArgInFrame to suppress it
for RPR SEEK_TAIL to prevent position invalidation
from reduced frame shifts.
---
src/backend/executor/nodeWindowAgg.c | 8 ++++++++
src/backend/utils/adt/windowfuncs.c | 2 +-
2 files changed, 9 insertions(+), 1 deletion(-)
diff --git a/src/backend/executor/nodeWindowAgg.c b/src/backend/executor/nodeWindowAgg.c
index 185d7a0d5ae..6d696af6fd4 100644
--- a/src/backend/executor/nodeWindowAgg.c
+++ b/src/backend/executor/nodeWindowAgg.c
@@ -4703,6 +4703,14 @@ WinGetFuncArgInFrame(WindowObject winobj, int argno,
Assert(WindowObjectIsValid(winobj));
winstate = winobj->winstate;
+
+ /*
+ * RPR reduced frames may shift; don't advance mark from tail seeks to
+ * avoid invalidating tuplestore read positions.
+ */
+ if (set_mark && winstate->rpPattern != NULL && seektype == WINDOW_SEEK_TAIL)
+ set_mark = false;
+
econtext = winstate->ss.ps.ps_ExprContext;
slot = winstate->temp_slot_1;
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 faff160b05eb81d0f419a396e564a2323e535c43 Mon Sep 17 00:00:00 2001
From: Henson Choi <[email protected]>
Date: Fri, 20 Mar 2026 23:52:42 +0900
Subject: [PATCH 6/6] 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 partition, returning NULL
if the target row is outside the partition. The offset defaults
to 1 if omitted; offset=0 refers to the current row itself;
NULL or negative offset raises an error.
Key design: instead of the previous 3-slot model (outer/scan/inner),
a single-slot swap model is used. EEOP_RPR_NAV_SET temporarily
replaces ecxt_outertuple with the target row, the argument
expression evaluates against it, and EEOP_RPR_NAV_RESTORE restores
the original slot. This eliminates varno rewriting and naturally
supports arbitrary offsets.
A dedicated nav_winobj with its own tuplestore read pointer avoids
interference with aggregate processing. A mark pointer pinned at
position 0 prevents tuplestore truncation so that PREV(expr, N)
can reach any prior row.
RPRNavExpr is a new expression node that replaces the previous
approach of identifying PREV/NEXT by funcid. The parser transforms
PREV/NEXT function calls into RPRNavExpr nodes in ParseFuncOrColumn().
Validation in parse_rpr.c rejects nested PREV/NEXT, requires at
least one column reference in the first argument, and ensures
the offset is a run-time constant.
RPRNavKind uses plain enum values (not -1/+1) so that FIRST/LAST
can be added later without arithmetic tricks.
LLVM JIT falls back to the interpreter for expressions containing
RPR navigation opcodes, because JIT code caches the outertuple's
tts_values/tts_isnull pointers in the entry block and the
mid-expression slot swap leaves them stale. Only DEFINE clause
expressions with PREV/NEXT are affected; other expressions in
the same query are still JIT-compiled normally.
---
doc/src/sgml/func/func-window.sgml | 22 +-
src/backend/executor/execExpr.c | 56 +++
src/backend/executor/execExprInterp.c | 110 +++++
src/backend/executor/nodeWindowAgg.c | 243 ++++------
src/backend/jit/llvm/llvmjit_expr.c | 40 ++
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 | 87 ++++
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 | 18 +
src/include/executor/nodeWindowAgg.h | 3 +
src/include/nodes/execnodes.h | 10 +-
src/include/nodes/primnodes.h | 31 ++
src/test/regress/expected/rpr.out | 551 +++++++++++++++++++++-
src/test/regress/expected/rpr_explain.out | 114 ++++-
src/test/regress/sql/rpr.sql | 322 ++++++++++++-
src/test/regress/sql/rpr_explain.sql | 84 +++-
src/tools/pgindent/typedefs.list | 3 +-
21 files changed, 1638 insertions(+), 195 deletions(-)
diff --git a/doc/src/sgml/func/func-window.sgml b/doc/src/sgml/func/func-window.sgml
index ae36e0f3135..1b9b993a817 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.
+ <parameter>offset</parameter> must not be NULL.
</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.
+ <parameter>offset</parameter> must not be NULL.
</para></entry>
</row>
diff --git a/src/backend/executor/execExpr.c b/src/backend/executor/execExpr.c
index 77229141b38..dbed4f48a0f 100644
--- a/src/backend/executor/execExpr.c
+++ b/src/backend/executor/execExpr.c
@@ -1222,6 +1222,62 @@ 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.kind = nav->kind;
+
+ 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 43116431edf..39db11301c4 100644
--- a/src/backend/executor/execExprInterp.c
+++ b/src/backend/executor/execExprInterp.c
@@ -56,12 +56,14 @@
*/
#include "postgres.h"
+#include "common/int.h"
#include "access/heaptoast.h"
#include "access/tupconvert.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"
@@ -578,6 +580,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,
@@ -2005,6 +2009,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,91 @@ 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 unsigned offset. For 2-arg PREV/NEXT the offset
+ * expression has already been evaluated into offset_value. NULL or
+ * negative offsets are errors per the SQL standard (ISO/IEC 9075-2,
+ * Subclause 5.6.2).
+ */
+ if (op->d.rpr_nav.offset_value != NULL)
+ {
+ if (*op->d.rpr_nav.offset_isnull)
+ ereport(ERROR,
+ (errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
+ errmsg("PREV/NEXT offset must not be null")));
+
+ offset = DatumGetInt64(*op->d.rpr_nav.offset_value);
+
+ if (offset < 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("PREV/NEXT offset must not be negative")));
+ }
+ else
+ offset = 1;
+
+ /*
+ * Calculate target position based on navigation direction. On overflow,
+ * use -1 so that ExecRPRNavGetSlot treats it as out of range.
+ */
+ switch (op->d.rpr_nav.kind)
+ {
+ case RPR_NAV_PREV:
+ if (pg_sub_s64_overflow(winstate->currentpos, offset, &target_pos))
+ target_pos = -1;
+ break;
+ case RPR_NAV_NEXT:
+ if (pg_add_s64_overflow(winstate->currentpos, offset, &target_pos))
+ target_pos = -1;
+ break;
+ }
+
+ /* 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 6d696af6fd4..084c745e6ca 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"
@@ -177,14 +176,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,
@@ -243,9 +234,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);
@@ -256,9 +244,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);
@@ -1302,6 +1287,25 @@ 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.
+ *
+ * XXX This retains the entire partition in the tuplestore. If the
+ * DEFINE clause only uses PREV/NEXT with small constant offsets, we
+ * could advance the mark to (currentpos - max_offset) instead.
+ */
+ 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
@@ -1412,6 +1416,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++)
{
@@ -2757,15 +2768,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
@@ -2935,6 +2949,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;
@@ -2975,7 +3006,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)
{
@@ -2992,7 +3025,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);
@@ -3027,107 +3059,38 @@ 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.
*/
-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;
}
@@ -3188,8 +3151,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)
@@ -4278,6 +4241,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)
@@ -4288,37 +4255,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)
{
@@ -4336,6 +4291,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..d158e37e7b5 100644
--- a/src/backend/jit/llvm/llvmjit_expr.c
+++ b/src/backend/jit/llvm/llvmjit_expr.c
@@ -296,6 +296,40 @@ llvm_compile_expr(ExprState *state)
FIELDNO_EXPRCONTEXT_AGGNULLS,
"v.econtext.aggnulls");
+ /*
+ * RPR navigation opcodes (PREV/NEXT) swap ecxt_outertuple to a different
+ * row mid-expression. The JIT code loads v_outervalues and v_outernulls
+ * once in the entry block and reuses them for all EEOP_OUTER_VAR steps.
+ * After a slot swap, these pointers become stale because the new slot has
+ * its own tts_values/tts_isnull arrays. Fall back to the interpreter for
+ * these expressions.
+ *
+ * XXX To JIT-compile these expressions properly, the NAV_SET and
+ * NAV_RESTORE handlers would need to reload the tts_values and tts_isnull
+ * pointers from the new slot. However, LLVM uses SSA (Static Single
+ * Assignment) form where each value is defined exactly once. When
+ * different basic blocks produce different values for the same pointer,
+ * LLVM requires PHI nodes at the merge point to select the correct one.
+ * Without that plumbing, OUTER_VAR steps after a slot swap would read
+ * from the wrong pointer.
+ */
+ if (parent && IsA(parent, WindowAggState) &&
+ ((WindowAgg *) parent->plan)->rpPattern != NULL)
+ {
+ for (int opno = 0; opno < state->steps_len; opno++)
+ {
+ ExprEvalOp opcode = ExecEvalStepOp(state, &state->steps[opno]);
+
+ if (opcode == EEOP_RPR_NAV_SET ||
+ opcode == EEOP_RPR_NAV_RESTORE)
+ {
+ LLVMDeleteFunction(eval_fn);
+ LLVMDisposeBuilder(b);
+ return false;
+ }
+ }
+ }
+
/* allocate blocks for each op upfront, so we can do jumps easily */
opblocks = palloc_array(LLVMBasicBlockRef, state->steps_len);
for (int opno = 0; opno < state->steps_len; opno++)
@@ -2432,6 +2466,12 @@ llvm_compile_expr(ExprState *state)
LLVMBuildBr(b, opblocks[opno + 1]);
break;
+ case EEOP_RPR_NAV_SET:
+ case EEOP_RPR_NAV_RESTORE:
+ /* unreachable: filtered out by the pre-scan above */
+ Assert(false);
+ return false;
+
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 1adda7c5d84..d2f19584070 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;
@@ -853,6 +856,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;
@@ -1154,6 +1160,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;
@@ -1426,6 +1435,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);
@@ -2187,6 +2199,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;
@@ -3116,6 +3138,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 23b02fb3bc0..e14ff4dc494 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..1355989e66c 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,8 @@ static void validateRPRPatternVarCount(ParseState *pstate, RPRPatternNode *node,
List *rpDefs, List **varNames);
static List *transformDefineClause(ParseState *pstate, WindowClause *wc,
WindowDef *windef, List **targetlist);
+static void check_rpr_nav_expr(RPRNavExpr *nav, ParseState *pstate);
+static bool check_rpr_nav_nesting_walker(Node *node, void *context);
/*
* transformRPR
@@ -400,6 +403,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 +415,83 @@ transformDefineClause(ParseState *pstate, WindowClause *wc, WindowDef *windef,
return defineClause;
}
+
+/*
+ * check_rpr_nav_expr
+ * Validate a single RPRNavExpr node by walking its arg and offset_arg
+ * subtrees in a single pass each. Checks for nested PREV/NEXT, missing
+ * column references, and non-constant offset expressions.
+ */
+typedef struct
+{
+ bool has_nav; /* RPRNavExpr found (nesting) */
+ bool has_column_ref; /* Var found */
+} NavCheckResult;
+
+static bool
+nav_check_walker(Node *node, void *context)
+{
+ NavCheckResult *result = (NavCheckResult *) context;
+
+ if (node == NULL)
+ return false;
+ if (IsA(node, RPRNavExpr))
+ result->has_nav = true;
+ if (IsA(node, Var))
+ result->has_column_ref = true;
+
+ return expression_tree_walker(node, nav_check_walker, context);
+}
+
+static void
+check_rpr_nav_expr(RPRNavExpr *nav, ParseState *pstate)
+{
+ NavCheckResult result;
+
+ /* Check arg subtree: nesting + column reference in one walk */
+ memset(&result, 0, sizeof(result));
+ (void) nav_check_walker((Node *) nav->arg, &result);
+
+ if (result.has_nav)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("PREV and NEXT cannot be nested"),
+ parser_errposition(pstate, nav->location)));
+ if (!result.has_column_ref)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("argument of row pattern navigation operation must include at least one column reference"),
+ parser_errposition(pstate, nav->location)));
+
+ /* Check offset_arg: column ref + volatile in one walk */
+ if (nav->offset_arg != NULL)
+ {
+ memset(&result, 0, sizeof(result));
+ (void) nav_check_walker((Node *) nav->offset_arg, &result);
+
+ if (result.has_column_ref ||
+ 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(pstate, nav->location)));
+ }
+}
+
+/*
+ * check_rpr_nav_nesting_walker
+ * Walk the DEFINE clause expression tree and validate each RPRNavExpr.
+ */
+static bool
+check_rpr_nav_nesting_walker(Node *node, void *context)
+{
+ if (node == NULL)
+ return false;
+ if (IsA(node, RPRNavExpr))
+ {
+ check_rpr_nav_expr((RPRNavExpr *) node, (ParseState *) context);
+ /* 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 25bc7dd7779..72f4f263533 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -10106,6 +10106,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 55749d3e517..34e4b375a62 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -10975,9 +10975,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..fac37c96896 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,16 @@ typedef struct ExprEvalStep
SubPlanState *sstate;
} subplan;
+ /* for EEOP_RPR_NAV_SET / EEOP_RPR_NAV_RESTORE */
+ struct
+ {
+ WindowAggState *winstate;
+ RPRNavKind kind; /* PREV or NEXT */
+ 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 +912,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 33028c3f10b..30ee769e45d 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -2689,10 +2689,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 f5b6b45664a..94723a3b909 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 or RPR_NAV_NEXT
+ * 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,
+ RPR_NAV_NEXT,
+} 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..d586e17e0a1 100644
--- a/src/test/regress/expected/rpr.out
+++ b/src/test/regress/expected/rpr.out
@@ -1018,6 +1018,555 @@ 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
+ ^
+-- Non-constant offset: subquery 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, (SELECT 1)) > 0
+);
+ERROR: cannot use subquery in DEFINE expression
+LINE 7: DEFINE A AS PREV(price, (SELECT 1)) > 0
+ ^
+-- First arg: subquery (caught by DEFINE-level subquery restriction)
+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 + (SELECT 1)) > 0
+);
+ERROR: cannot use subquery in DEFINE expression
+LINE 7: DEFINE A AS PREV(price + (SELECT 1)) > 0
+ ^
+-- First arg: volatile function is allowed (evaluated on target row)
+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 + random() * 0) >= 0
+);
+ company | tdate | price | first_value | last_value | count
+----------+------------+-------+-------------+------------+-------
+ company1 | 07-01-2023 | 100 | | | 0
+ company1 | 07-02-2023 | 200 | 200 | 130 | 9
+ 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 | | | 0
+ company2 | 07-02-2023 | 2000 | 2000 | 1300 | 9
+ 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: 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 +2028,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..504476a2b02 100644
--- a/src/test/regress/sql/rpr.sql
+++ b/src/test/regress/sql/rpr.sql
@@ -440,6 +440,326 @@ 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
+);
+
+-- Non-constant offset: subquery 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, (SELECT 1)) > 0
+);
+
+-- First arg: subquery (caught by DEFINE-level subquery restriction)
+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 + (SELECT 1)) > 0
+);
+
+-- First arg: volatile function is allowed (evaluated on target row)
+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 + random() * 0) >= 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 +991,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 71ea925d77a..24856cf8478 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -1799,7 +1799,6 @@ NamedLWLockTrancheRequest
NamedTuplestoreScan
NamedTuplestoreScanState
NamespaceInfo
-NavigationInfo
NestLoop
NestLoopParam
NestLoopState
@@ -2476,6 +2475,8 @@ QuerySource
QueueBackendStatus
QueuePosition
QuitSignalReason
+RPRNavExpr
+RPRNavKind
RBTNode
RBTOrderControl
RBTree
--
2.50.1 (Apple Git-155)
Attachments:
[text/plain] nocfbot-0001-Remove-unused-regex-regex.h-include-from-nodeWindowA.txt (764B, 3-nocfbot-0001-Remove-unused-regex-regex.h-include-from-nodeWindowA.txt)
download | inline diff:
From da0ce590d45e35be96ddf74f3eb9b657bfca4a81 Mon Sep 17 00:00:00 2001
From: Henson Choi <[email protected]>
Date: Tue, 24 Mar 2026 19:04:19 +0900
Subject: [PATCH 1/6] Remove unused regex/regex.h include from nodeWindowAgg.c
---
src/backend/executor/nodeWindowAgg.c | 1 -
1 file changed, 1 deletion(-)
diff --git a/src/backend/executor/nodeWindowAgg.c b/src/backend/executor/nodeWindowAgg.c
index 4f882b877b1..185d7a0d5ae 100644
--- a/src/backend/executor/nodeWindowAgg.c
+++ b/src/backend/executor/nodeWindowAgg.c
@@ -50,7 +50,6 @@
#include "optimizer/rpr.h"
#include "parser/parse_agg.h"
#include "parser/parse_coerce.h"
-#include "regex/regex.h"
#include "utils/acl.h"
#include "utils/builtins.h"
#include "utils/datum.h"
--
2.50.1 (Apple Git-155)
[text/plain] nocfbot-0002-Add-CHECK_FOR_INTERRUPTS-to-nfa_add_state_unique-for.txt (830B, 4-nocfbot-0002-Add-CHECK_FOR_INTERRUPTS-to-nfa_add_state_unique-for.txt)
download | inline diff:
From 8ab2e3b303422c36185210042bbe6831d50009cc Mon Sep 17 00:00:00 2001
From: Henson Choi <[email protected]>
Date: Wed, 25 Mar 2026 00:25:40 +0900
Subject: [PATCH 2/6] Add CHECK_FOR_INTERRUPTS() to nfa_add_state_unique() for
state explosion patterns
---
src/backend/executor/execRPR.c | 2 ++
1 file changed, 2 insertions(+)
diff --git a/src/backend/executor/execRPR.c b/src/backend/executor/execRPR.c
index bab5257f68f..cf54e0c76c3 100644
--- a/src/backend/executor/execRPR.c
+++ b/src/backend/executor/execRPR.c
@@ -1763,6 +1763,8 @@ nfa_add_state_unique(WindowAggState *winstate, RPRNFAContext *ctx, RPRNFAState *
/* Check for duplicate and find tail */
for (s = ctx->states; s != NULL; s = s->next)
{
+ CHECK_FOR_INTERRUPTS();
+
if (nfa_states_equal(winstate, s, state))
{
/*
--
2.50.1 (Apple Git-155)
[text/plain] nocfbot-0003-Add-CHECK_FOR_INTERRUPTS-to-nfa_try_absorb_context-l.txt (864B, 5-nocfbot-0003-Add-CHECK_FOR_INTERRUPTS-to-nfa_try_absorb_context-l.txt)
download | inline diff:
From dd30851691ade32ea18b50501b06da72839687e3 Mon Sep 17 00:00:00 2001
From: Henson Choi <[email protected]>
Date: Wed, 25 Mar 2026 11:03:39 +0900
Subject: [PATCH 3/6] Add CHECK_FOR_INTERRUPTS() to nfa_try_absorb_context()
loop
---
src/backend/executor/execRPR.c | 2 ++
1 file changed, 2 insertions(+)
diff --git a/src/backend/executor/execRPR.c b/src/backend/executor/execRPR.c
index cf54e0c76c3..58f9da0b814 100644
--- a/src/backend/executor/execRPR.c
+++ b/src/backend/executor/execRPR.c
@@ -2084,6 +2084,8 @@ nfa_try_absorb_context(WindowAggState *winstate, RPRNFAContext *ctx)
for (older = ctx->prev; older != NULL; older = older->prev)
{
+ CHECK_FOR_INTERRUPTS();
+
/*
* By invariant: ctx->prev chain is in creation order (oldest first),
* and each row creates at most one context. So all contexts in this
--
2.50.1 (Apple Git-155)
[text/plain] nocfbot-0004-Fix-in-place-modification-of-defineClause-TargetEntr.txt (1.0K, 6-nocfbot-0004-Fix-in-place-modification-of-defineClause-TargetEntr.txt)
download | inline diff:
From fb824d76d6635e624da5cdee1a481d61707e73bd Mon Sep 17 00:00:00 2001
From: Henson Choi <[email protected]>
Date: Wed, 25 Mar 2026 00:20:05 +0900
Subject: [PATCH 4/6] Fix in-place modification of defineClause TargetEntry in
setrefs.c
---
src/backend/optimizer/plan/setrefs.c | 2 ++
1 file changed, 2 insertions(+)
diff --git a/src/backend/optimizer/plan/setrefs.c b/src/backend/optimizer/plan/setrefs.c
index 69cd1861e9b..9cdb217a047 100644
--- a/src/backend/optimizer/plan/setrefs.c
+++ b/src/backend/optimizer/plan/setrefs.c
@@ -2646,6 +2646,7 @@ set_upper_references(PlannerInfo *root, Plan *plan, int rtoffset)
{
TargetEntry *tle = (TargetEntry *) lfirst(l);
+ tle = flatCopyTargetEntry(tle);
tle->expr = (Expr *)
fix_upper_expr(root,
(Node *) tle->expr,
@@ -2654,6 +2655,7 @@ set_upper_references(PlannerInfo *root, Plan *plan, int rtoffset)
rtoffset,
NRM_EQUAL,
NUM_EXEC_QUAL(plan));
+ lfirst(l) = tle;
}
}
}
--
2.50.1 (Apple Git-155)
[text/plain] nocfbot-0005-Fix-mark-handling-for-last_value-under-RPR.txt (1.8K, 7-nocfbot-0005-Fix-mark-handling-for-last_value-under-RPR.txt)
download | inline diff:
From 344fbb8a5d39a53f7ce6ff924a06d4e4d3ec2b57 Mon Sep 17 00:00:00 2001
From: Henson Choi <[email protected]>
Date: Wed, 25 Mar 2026 00:37:50 +0900
Subject: [PATCH 5/6] Fix mark handling for last_value() under RPR
Enable mark advancement in window_last_value() for
better tuplestore memory usage in non-RPR cases, while
adding a guard in WinGetFuncArgInFrame to suppress it
for RPR SEEK_TAIL to prevent position invalidation
from reduced frame shifts.
---
src/backend/executor/nodeWindowAgg.c | 8 ++++++++
src/backend/utils/adt/windowfuncs.c | 2 +-
2 files changed, 9 insertions(+), 1 deletion(-)
diff --git a/src/backend/executor/nodeWindowAgg.c b/src/backend/executor/nodeWindowAgg.c
index 185d7a0d5ae..6d696af6fd4 100644
--- a/src/backend/executor/nodeWindowAgg.c
+++ b/src/backend/executor/nodeWindowAgg.c
@@ -4703,6 +4703,14 @@ WinGetFuncArgInFrame(WindowObject winobj, int argno,
Assert(WindowObjectIsValid(winobj));
winstate = winobj->winstate;
+
+ /*
+ * RPR reduced frames may shift; don't advance mark from tail seeks to
+ * avoid invalidating tuplestore read positions.
+ */
+ if (set_mark && winstate->rpPattern != NULL && seektype == WINDOW_SEEK_TAIL)
+ set_mark = false;
+
econtext = winstate->ss.ps.ps_ExprContext;
slot = winstate->temp_slot_1;
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-0006-Implement-1-slot-PREV-NEXT-navigation-for-RPR.txt (90.6K, 8-nocfbot-0006-Implement-1-slot-PREV-NEXT-navigation-for-RPR.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_zBCF3dwSjStmG0kJqw_y1z8QD73Rf1G58QTKEvd9tScwA@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