From 1bc9f7c448537ac6c778ae732b947b8fe29c19b1 Mon Sep 17 00:00:00 2001 From: jian he Date: Mon, 10 Aug 2026 12:42:59 +0900 Subject: [PATCH] Resolve RPR navigation offsets in the executor, not the plan tree Row pattern navigation (PREV, NEXT, FIRST, LAST and the compound forms) resolved its offset expressions in four places, and the planner wrote the resulting tuplestore trim bounds into the WindowAgg node. Resolve them once instead: build_define_offsets() records one RPRNavOffsets entry per navigation at executor init, the entry owns the RPRNavState that ExecEvalRPRNavSet() reads, and resolve_nav_offsets() settles the values. They live in executor state because the plan tree is read-only and may be shared between executions, so WindowAgg loses navMaxOffset and navFirstOffset, their two kinds, and hasFirstNav. RPRNavExpr gains navno instead, which compute_define_metadata() assigns in walk order and the executor fills rprNavOffsets in, so a compiled navigation reaches its own entry by index rather than by searching for its own plan node. The search checked what it found; the index has to as well, and not under Assert: list_nth() bounds-checks that way only, so a navno the list does not cover would read past its end in a production build instead of failing. Both checks are elog(ERROR) for that reason. An offset is a run-time constant, not a plan-time one. Only a Const is settled at init; a PARAM_EXEC from a function inline or LATERAL changes per outer row, as does a bind parameter under a generic plan. The values are settled per scan instead, at the top of ExecWindowAgg() and again after a rescan, which is also where eval_nav_offset() rejects a null or negative offset -- before the first row is fetched, so an empty partition still rejects an illegal one. RPRNavOffsetKind has three states: FIXED, NEEDS_EVAL for an offset resolved per scan, and RETAIN_ALL for a backward reach that overflows int64. It moves to execnodes.h with the fields it qualifies. EXPLAIN reads them from the planstate, which ExecInitWindowAgg fills even without ANALYZE, and a dimension no navigation feeds prints nothing. The forward reach is recorded unconditionally, since minFirstOffset starts at PG_INT64_MAX and only ever decreases; hasFirstNav is what separates a FIRST whose offset is settled at execution from having no FIRST at all, which EXPLAIN prints differently. A foldable offset such as PREV(v, 1 + 1) has to arrive as a Const to count as fixed, which the expression tree mutator already sees to. A null or negative offset can never run, so it contributes no reach. extract_const_offset() substituted 0 for both, and 0 is a legal reach: it joined the Min() and won it, so a navigation no scan can run displaced the offset of one that can. FIRST(v, 7) beside PREV(FIRST(v, NULL), 2) displayed a lookahead of -2 and lost the 7. Both are now dropped from either reach, leaving the surviving navigation to answer. That removes the per-invocation machinery: the Datum arrays in the eval step and rpr_nav_get_compound_offset(). nav_traversal_walker() goes too, since both call sites assert an RPRNavExpr never nests inside another; each gets a static RPRNavExpr_walker(). The planner walk keeps its driver name, compute_define_metadata(), and classifies match_start dependency in compute_matchStartDependent(), which is all it does now. While here, let ExecEvalRPRNavRestore() return early where the slot swap was elided: the argument read the current row rather than nav_slot, so no re-fetch can invalidate a pass-by-reference result and the datumCopy() is not needed. Test the paths this opens: a correlated offset through SRF inlining and LATERAL, a bind parameter under a generic plan, an offset settled before the first row is fetched, the implicit outer offset of 1 that each compound arm combines differently, the EXPLAIN spelling of all three kinds, the hyphenated XML spelling of a fixed one, and FIRST(v, N), which rpr_explain lacked. All four compound arms resolve their outer offset through the same call, so the null and negative cases need one query per arm, not two. The kind a scan settles on is itself per scan: the same three offsets in a different order leave EXPLAIN ANALYZE reading retain all or a bound, depending on which rescan ran last. --- src/backend/commands/explain.c | 70 +- src/backend/executor/README.rpr | 31 +- src/backend/executor/execExpr.c | 88 +- src/backend/executor/execExprInterp.c | 101 +- src/backend/executor/nodeWindowAgg.c | 508 +++++--- src/backend/optimizer/plan/createplan.c | 359 +----- src/backend/optimizer/plan/rpr.c | 33 - src/backend/parser/parse_func.c | 1 + src/include/executor/execExpr.h | 8 +- src/include/nodes/execnodes.h | 73 +- src/include/nodes/parsenodes.h | 15 - src/include/nodes/plannodes.h | 28 +- src/include/nodes/primnodes.h | 22 +- src/include/optimizer/rpr.h | 22 - src/test/regress/expected/rpr.out | 134 ++- src/test/regress/expected/rpr_base.out | 339 +++--- src/test/regress/expected/rpr_explain.out | 1043 ++++++++++++----- src/test/regress/expected/rpr_integration.out | 124 +- src/test/regress/sql/rpr.sql | 98 +- src/test/regress/sql/rpr_base.sql | 46 +- src/test/regress/sql/rpr_explain.sql | 307 ++++- src/test/regress/sql/rpr_integration.sql | 63 + src/tools/pgindent/typedefs.list | 4 +- 23 files changed, 2239 insertions(+), 1278 deletions(-) diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c index 440053e2101..fa30b5ca0ad 100644 --- a/src/backend/commands/explain.c +++ b/src/backend/commands/explain.c @@ -3203,11 +3203,6 @@ show_window_def(WindowAggState *planstate, List *ancestors, ExplainState *es) /* Show Row Pattern Recognition pattern if present */ if (wagg->rpPattern != NULL) { - RPRNavOffsetKind maxKind = wagg->navMaxOffsetKind; - int64 maxOffset = wagg->navMaxOffset; - RPRNavOffsetKind firstKind = wagg->navFirstOffsetKind; - int64 firstOffset = wagg->navFirstOffset; - char *patternStr = deparse_rpr_pattern(wagg->rpPattern); ExplainPropertyText("Pattern", patternStr, es); @@ -3215,56 +3210,47 @@ show_window_def(WindowAggState *planstate, List *ancestors, ExplainState *es) pfree(patternStr); /* - * Show navigation offsets for tuplestore trim. For EXPLAIN ANALYZE, - * use the executor-resolved values (which may differ from the plan - * when NEEDS_EVAL was resolved to FIXED or RETAIN_ALL at init). + * Navigation offsets for tuplestore trim are resolved at executor + * init, which runs even for plain EXPLAIN, so read the resolved value + * and its kind from the planstate. */ - if (es->analyze) + if (planstate->hasMaxNav) { - maxKind = planstate->navMaxOffsetKind; - maxOffset = planstate->navMaxOffset; - firstKind = planstate->navFirstOffsetKind; - firstOffset = planstate->navFirstOffset; - } - - switch (maxKind) - { - case RPR_NAV_OFFSET_NEEDS_EVAL: - ExplainPropertyText("Nav Mark Lookback", "runtime", es); - break; - case RPR_NAV_OFFSET_RETAIN_ALL: - ExplainPropertyText("Nav Mark Lookback", "retain all", es); - break; - case RPR_NAV_OFFSET_FIXED: - ExplainPropertyInteger("Nav Mark Lookback", NULL, - maxOffset, es); - break; - default: - elog(ERROR, "unrecognized RPR nav offset kind: %d", - maxKind); - break; + switch (planstate->navMaxOffsetKind) + { + case RPR_NAV_OFFSET_NEEDS_EVAL: + ExplainPropertyText("Nav Mark Lookback", "runtime", es); + break; + case RPR_NAV_OFFSET_RETAIN_ALL: + ExplainPropertyText("Nav Mark Lookback", "retain all", es); + break; + case RPR_NAV_OFFSET_FIXED: + ExplainPropertyInteger("Nav Mark Lookback", NULL, + planstate->navMaxOffset, es); + break; + } } - if (wagg->hasFirstNav) + if (planstate->hasFirstNav) { - switch (firstKind) + switch (planstate->navFirstOffsetKind) { case RPR_NAV_OFFSET_NEEDS_EVAL: - ExplainPropertyText("Nav Mark Lookahead", "runtime", - es); + ExplainPropertyText("Nav Mark Lookahead", "runtime", es); break; case RPR_NAV_OFFSET_FIXED: - if (firstOffset == PG_INT64_MAX) - ExplainPropertyText("Nav Mark Lookahead", "infinite", - es); + if (planstate->navFirstOffset == PG_INT64_MAX) + ExplainPropertyText("Nav Mark Lookahead", "infinite", es); else ExplainPropertyInteger("Nav Mark Lookahead", NULL, - firstOffset, es); + planstate->navFirstOffset, es); break; default: - /* RPR_NAV_OFFSET_RETAIN_ALL is lookback-only, never here */ - elog(ERROR, "unrecognized RPR nav offset kind: %d", - firstKind); + /* a forward reach is unbounded, never retain all */ + Assert(planstate->navFirstOffsetKind == + RPR_NAV_OFFSET_NEEDS_EVAL || + planstate->navFirstOffsetKind == + RPR_NAV_OFFSET_FIXED); break; } } diff --git a/src/backend/executor/README.rpr b/src/backend/executor/README.rpr index 0111382f5c4..69277701744 100644 --- a/src/backend/executor/README.rpr +++ b/src/backend/executor/README.rpr @@ -731,9 +731,10 @@ variables are consumed. VI-4. Per-Context Invalidation (match_start_dependent variables) -DEFINE variables that depend on match_start (those containing FIRST, -LAST-with-offset, or compound PREV_FIRST/NEXT_FIRST/PREV_LAST/NEXT_LAST) -are identified at plan time via defineMatchStartDependent. For the head +DEFINE variables that depend on match_start -- those containing FIRST or a +compound PREV_FIRST/NEXT_FIRST, or a LAST that carries an offset of its own, +whether plain or inside a compound PREV_LAST/NEXT_LAST -- are identified at +plan time via defineMatchStartDependent. For the head context, advance_reduced_frame_nfa sets nav_match_start to its matchStartRow before matching, so lazy evaluation uses the correct FIRST/LAST base position. @@ -770,15 +771,16 @@ VI-5. Tuplestore Mark and Trim (nodeWindowAgg.c) Navigation functions require access to past rows via the tuplestore. To allow tuplestore_trim() to free rows that are no longer reachable, -the planner computes two offsets (see compute_define_metadata): +the executor computes two offsets at init (see build_define_offsets): navMaxOffset (Nav Mark Lookback): Maximum backward reach from currentpos. Contributed by PREV, - LAST-with-offset, and compound PREV_LAST/NEXT_LAST. + LAST (any offset, including the default 0), and compound + PREV_LAST/NEXT_LAST. Mark position: currentpos - navMaxOffset. navFirstOffset (Nav Mark Lookahead): - Minimum forward offset from match_start. Contributed by FIRST + Minimum forward reach from match_start. Contributed by FIRST and compound PREV_FIRST/NEXT_FIRST. Can be negative when compound PREV_FIRST looks before match_start. Mark position: oldest_context->matchStartRow + navFirstOffset. @@ -786,10 +788,18 @@ the planner computes two offsets (see compute_define_metadata): The actual mark is set to: min(lookback_mark, lookahead_mark). This ensures all rows reachable by any navigation function are retained. -When offsets contain non-constant expressions (Param), the planner sets -navMaxOffsetKind/navFirstOffsetKind to RPR_NAV_OFFSET_NEEDS_EVAL and the -executor evaluates them at init time. On overflow, the kind is set to -RPR_NAV_OFFSET_RETAIN_ALL, disabling trim for that dimension. +When offsets contain non-constant expressions (Param), the executor sets +navMaxOffsetKind/navFirstOffsetKind to RPR_NAV_OFFSET_NEEDS_EVAL. A constant +offset is resolved at init, as is a bind parameter the planner folded to a +Const for a custom plan; under a generic plan that parameter stays a Param and +resolves per scan, as a PARAM_EXEC offset does. Either way every navigation is +settled again per scan by resolve_nav_offsets(), which is where a null or +negative offset is rejected. On overflow, the kind is set to +RPR_NAV_OFFSET_RETAIN_ALL, disabling trim for that dimension. An offset that +resolves negative is rejected at execution, so that navigation can never run and +is left out of both reaches; it behaves exactly as if it were not in the DEFINE. +Each dimension is reported only when some navigation feeds it (hasMaxNav, +hasFirstNav), so an empty one prints nothing rather than a reach of zero. VI-6. ExecRPRProcessRow(): 3-Phase Processing @@ -1668,7 +1678,6 @@ Appendix A. Key Function Index ExecRPRFinalizeAllContexts execRPR.c Partition-end finalize ExecRPRRecordContextSuccess execRPR.c Stats: match success ExecRPRRecordContextFailure execRPR.c Stats: match failure - compute_define_metadata createplan.c Trim offset computation Appendix B. Data Structure Relationship Diagram ============================================================================ diff --git a/src/backend/executor/execExpr.c b/src/backend/executor/execExpr.c index 7a79a002111..4822b5795e1 100644 --- a/src/backend/executor/execExpr.c +++ b/src/backend/executor/execExpr.c @@ -1177,11 +1177,9 @@ ExecInitExprRec(Expr *node, ExprState *state, * 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. - * - * Default offset when offset_arg is NULL: PREV/NEXT: 1 - * (physical offset from currentpos) FIRST/LAST: 0 (logical - * offset from match boundary) */ + RPRNavState *rprnavstate; + RPRNavOffsets *entry; RPRNavExpr *nav = (RPRNavExpr *) node; WindowAggState *winstate; int skip_arg_step; @@ -1189,60 +1187,26 @@ ExecInitExprRec(Expr *node, ExprState *state, 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->kind >= RPR_NAV_PREV_FIRST) - { - /* - * Compound navigation: allocate array of 2 for inner [0] - * and outer [1] offsets. - */ - Datum *offset_values = palloc_array(Datum, 2); - bool *offset_isnulls = palloc_array(bool, 2); + /* + * The offsets live in executor state, not on the RPRNavExpr, + * because the plan tree is read-only. navno indexes the list + * build_define_offsets() filled at startup; the values in it + * are settled per scan by resolve_nav_offsets(). + */ + if (nav->navno < 0 || + nav->navno >= list_length(winstate->rprNavOffsets)) + elog(ERROR, "RPRNavExpr navno %d out of range for %d offsets entries", + nav->navno, list_length(winstate->rprNavOffsets)); - /* Inner offset (default 0 for FIRST/LAST) */ - if (nav->offset_arg != NULL) - ExecInitExprRec(nav->offset_arg, state, - &offset_values[0], &offset_isnulls[0]); - else - { - offset_values[0] = Int64GetDatum(0); - offset_isnulls[0] = false; - } + entry = list_nth(winstate->rprNavOffsets, nav->navno); + if (entry->nav != nav) + elog(ERROR, "offsets entry %d belongs to a different RPRNavExpr", + nav->navno); + rprnavstate = entry->rprnavstate; - /* Outer offset (default 1 for PREV/NEXT) */ - if (nav->compound_offset_arg != NULL) - ExecInitExprRec(nav->compound_offset_arg, state, - &offset_values[1], &offset_isnulls[1]); - else - { - offset_values[1] = Int64GetDatum(1); - offset_isnulls[1] = false; - } - - scratch.d.rpr_nav.offset_value = offset_values; - scratch.d.rpr_nav.offset_isnull = offset_isnulls; - } - else if (nav->offset_arg != NULL) - { - /* Simple navigation with explicit offset */ - Datum *offset_value = palloc_object(Datum); - bool *offset_isnull = palloc_object(bool); - - 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 - { - /* Simple navigation with default offset */ - scratch.d.rpr_nav.offset_value = NULL; - scratch.d.rpr_nav.offset_isnull = NULL; - } + /* Emit SET opcode: swap slot to target row */ + scratch.opcode = EEOP_RPR_NAV_SET; + scratch.d.rpr_nav.rprnavstate = rprnavstate; ExprEvalPushStep(state, &scratch); @@ -1270,10 +1234,16 @@ ExecInitExprRec(Expr *node, ExprState *state, scratch.opcode = EEOP_RPR_NAV_RESTORE; scratch.resvalue = resv; scratch.resnull = resnull; - scratch.d.rpr_nav.winstate = winstate; + scratch.d.rpr_nav.rprnavstate = rprnavstate; + + /* + * The state is shared with the offsets entry, but resulttype + * belongs to the plan node, so every compilation of this + * navigation writes the same pair. + */ get_typlenbyval(nav->resulttype, - &scratch.d.rpr_nav.resulttyplen, - &scratch.d.rpr_nav.resulttypbyval); + &rprnavstate->resulttyplen, + &rprnavstate->resulttypbyval); ExprEvalPushStep(state, &scratch); break; } diff --git a/src/backend/executor/execExprInterp.c b/src/backend/executor/execExprInterp.c index c7460fbfac9..57c5a587480 100644 --- a/src/backend/executor/execExprInterp.c +++ b/src/backend/executor/execExprInterp.c @@ -6011,34 +6011,6 @@ ExecAggPlainTransByRef(AggState *aggstate, AggStatePerTrans pertrans, MemoryContextSwitchTo(oldContext); } -/* - * Extract compound (outer) offset from step data. - * For compound nav, offset_value is an array: [0]=inner, [1]=outer. - * Returns the outer offset; errors on NULL or negative. - * Default is 1 (like PREV/NEXT implicit offset). - */ -static int64 -rpr_nav_get_compound_offset(ExprEvalStep *op) -{ - int64 val; - - Assert(op->d.rpr_nav.offset_value != NULL); - - if (op->d.rpr_nav.offset_isnull[1]) - ereport(ERROR, - errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED), - errmsg("row pattern navigation offset must not be null")); - - val = DatumGetInt64(op->d.rpr_nav.offset_value[1]); - - if (val < 0) - ereport(ERROR, - errcode(ERRCODE_INVALID_PARAMETER_VALUE), - errmsg("row pattern navigation offset must not be negative")); - - return val; -} - /* * Evaluate RPR navigation (PREV/NEXT/FIRST/LAST): swap slot to target row. * @@ -6052,50 +6024,36 @@ rpr_nav_get_compound_offset(ExprEvalStep *op) void ExecEvalRPRNavSet(ExprState *state, ExprEvalStep *op, ExprContext *econtext) { - WindowAggState *winstate = op->d.rpr_nav.winstate; + WindowAggState *winstate; int64 offset; + int64 compound_offset; int64 target_pos; TupleTableSlot *target_slot; + RPRNavState *rprnavstate = op->d.rpr_nav.rprnavstate; + + winstate = rprnavstate->winstate; /* Save current slot for later restore */ winstate->nav_saved_outertuple = econtext->ecxt_outertuple; /* - * Determine the inner offset. NULL or negative offsets are errors per - * the SQL standard. - * - * Default offset when offset_arg is NULL: PREV/NEXT: 1 (standard 5.6.2) - * FIRST/LAST and compound: 0 for inner, 1 for outer + * resolve_nav_offsets() settled both offsets for this scan: it writes + * them as non-null, and where either is negative it raises the error and + * never writes them at all. Assert the invariants rather than repeating + * those checks here. */ - if (op->d.rpr_nav.offset_value != NULL) - { - if (*op->d.rpr_nav.offset_isnull) - ereport(ERROR, - errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED), - errmsg("row pattern navigation offset must not be null")); + Assert(!rprnavstate->offset.isnull && !rprnavstate->compound_offset.isnull); - offset = DatumGetInt64(*op->d.rpr_nav.offset_value); + offset = DatumGetInt64(rprnavstate->offset.value); + compound_offset = DatumGetInt64(rprnavstate->compound_offset.value); - if (offset < 0) - ereport(ERROR, - errcode(ERRCODE_INVALID_PARAMETER_VALUE), - errmsg("row pattern navigation offset must not be negative")); - } - else - { - /* Default offset: 1 for simple PREV/NEXT, 0 otherwise */ - if (op->d.rpr_nav.kind == RPR_NAV_PREV || - op->d.rpr_nav.kind == RPR_NAV_NEXT) - offset = 1; - else - offset = 0; - } + Assert(offset >= 0 && compound_offset >= 0); /* * 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) + switch (rprnavstate->rprnavexpr->kind) { case RPR_NAV_PREV: @@ -6129,7 +6087,6 @@ ExecEvalRPRNavSet(ExprState *state, ExprEvalStep *op, ExprContext *econtext) case RPR_NAV_PREV_FIRST: case RPR_NAV_NEXT_FIRST: { - int64 compound_offset; int64 inner_pos; /* Inner: match_start + offset */ @@ -6144,11 +6101,8 @@ ExecEvalRPRNavSet(ExprState *state, ExprEvalStep *op, ExprContext *econtext) break; } - /* Outer offset */ - compound_offset = rpr_nav_get_compound_offset(op); - /* Apply outer: PREV subtracts, NEXT adds */ - if (op->d.rpr_nav.kind == RPR_NAV_PREV_FIRST) + if (rprnavstate->rprnavexpr->kind == RPR_NAV_PREV_FIRST) { /* * inner_pos is in [0, currentpos] and compound_offset is @@ -6168,7 +6122,6 @@ ExecEvalRPRNavSet(ExprState *state, ExprEvalStep *op, ExprContext *econtext) case RPR_NAV_PREV_LAST: case RPR_NAV_NEXT_LAST: { - int64 compound_offset; int64 inner_pos; /* Inner: currentpos - offset */ @@ -6183,11 +6136,8 @@ ExecEvalRPRNavSet(ExprState *state, ExprEvalStep *op, ExprContext *econtext) break; } - /* Outer offset */ - compound_offset = rpr_nav_get_compound_offset(op); - /* Apply outer: PREV subtracts, NEXT adds */ - if (op->d.rpr_nav.kind == RPR_NAV_PREV_LAST) + if (rprnavstate->rprnavexpr->kind == RPR_NAV_PREV_LAST) { /* * inner_pos is in [nav_match_start, currentpos] (>= 0) @@ -6206,7 +6156,7 @@ ExecEvalRPRNavSet(ExprState *state, ExprEvalStep *op, ExprContext *econtext) break; default: elog(ERROR, "unrecognized RPR navigation kind: %d", - op->d.rpr_nav.kind); + (int) rprnavstate->rprnavexpr->kind); break; } @@ -6256,8 +6206,6 @@ ExecEvalRPRNavSet(ExprState *state, ExprEvalStep *op, ExprContext *econtext) * Evaluate RPR navigation: restore slot to original row. * * Restores econtext->ecxt_outertuple from the saved slot in winstate. - * When slot swap was elided (target == currentpos), this is a harmless - * no-op since saved and current slots are identical. * The caller is responsible for updating any local slot cache. * * For pass-by-reference result types, the result datum points into @@ -6271,12 +6219,21 @@ void ExecEvalRPRNavRestore(ExprState *state, ExprEvalStep *op, ExprContext *econtext) { - WindowAggState *winstate = op->d.rpr_nav.winstate; + WindowAggState *winstate = op->d.rpr_nav.rprnavstate->winstate; + + /* + * When the slot swap was elided (target == currentpos), restoring is a + * no-op, and the argument read the current row's slot rather than + * nav_slot, so no re-fetch of nav_slot can invalidate a pass-by-ref + * result. + */ + if (econtext->ecxt_outertuple == winstate->nav_saved_outertuple) + return; econtext->ecxt_outertuple = winstate->nav_saved_outertuple; /* Stabilize pass-by-ref result against nav_slot re-fetch */ - if (!op->d.rpr_nav.resulttypbyval && + if (!op->d.rpr_nav.rprnavstate->resulttypbyval && !*op->resnull) { MemoryContext oldContext; @@ -6284,7 +6241,7 @@ ExecEvalRPRNavRestore(ExprState *state, ExprEvalStep *op, oldContext = MemoryContextSwitchTo(econtext->ecxt_per_tuple_memory); *op->resvalue = datumCopy(*op->resvalue, false, - op->d.rpr_nav.resulttyplen); + op->d.rpr_nav.rprnavstate->resulttyplen); MemoryContextSwitchTo(oldContext); } } diff --git a/src/backend/executor/nodeWindowAgg.c b/src/backend/executor/nodeWindowAgg.c index 972874b3514..b7c1be4cfb5 100644 --- a/src/backend/executor/nodeWindowAgg.c +++ b/src/backend/executor/nodeWindowAgg.c @@ -47,7 +47,6 @@ #include "nodes/plannodes.h" #include "optimizer/clauses.h" #include "optimizer/optimizer.h" -#include "optimizer/rpr.h" #include "parser/parse_agg.h" #include "parser/parse_coerce.h" #include "utils/acl.h" @@ -176,6 +175,21 @@ typedef struct WindowStatePerAggData bool restart; /* need to restart this agg in this cycle? */ } WindowStatePerAggData; +typedef struct +{ + WindowAggState *winstate; + int64 maxOffset; /* max backward-reach offset across all nav + * exprs */ + bool maxOverflow; /* true if backward-reach overflow detected */ + int64 minFirstOffset; /* min forward-from-match_start offset; may be + * negative (PREV_FIRST: inner - outer < 0) */ + bool hasMax; /* any backward-reach nav found */ + bool hasFirst; /* any FIRST-based nav found */ + bool validate; /* fail-closed on a null/negative offset? + * false at init (display only), true at + * execution */ +} EvalDefineOffsetsContext; + static void initialize_windowaggregate(WindowAggState *winstate, WindowStatePerFunc perfuncstate, WindowStatePerAgg peraggstate); @@ -247,9 +261,11 @@ static void update_reduced_frame(WindowObject winobj, int64 pos); /* Forward declarations - DEFINE row evaluation */ static bool rpr_prepare_row(WindowObject winobj, int64 pos, RPRVarMatch *varMatched); - -/* Forward declarations - navigation offset evaluation */ -static void eval_define_offsets(WindowAggState *winstate, List *defineClause); +static void build_define_offsets(WindowAggState *winstate, List *defineClause); +static void resolve_nav_offsets(WindowAggState *winstate); +static void resolve_one_nav(RPRNavOffsets *entry, EvalDefineOffsetsContext *context); +static bool RPRNavExpr_walker(Node *node, WindowAggState *winstate); +static void build_nav_offsets(RPRNavExpr *nav, WindowAggState *winstate); /* * Not null info bit array consists of 2-bit items @@ -1261,13 +1277,12 @@ prepare_tuplestore(WindowAggState *winstate) /* * Allocate mark and read pointers for RPR navigation. * - * If navMaxOffsetKind == RPR_NAV_OFFSET_FIXED, we advance the mark - * based on (currentpos - navMaxOffset) and optionally + * When the trim offset is FIXED we advance the mark based on + * (currentpos - navMaxOffset) and optionally * (nfaContext->matchStartRow + navFirstOffset), allowing - * tuplestore_trim() to free rows that are no longer reachable. - * - * RPR_NAV_OFFSET_NEEDS_EVAL is resolved at executor init; by this - * point it is either FIXED or RETAIN_ALL. + * tuplestore_trim() to free rows that are no longer reachable. A + * parameterized offset is still NEEDS_EVAL here and gets resolved at + * execution by resolve_nav_offsets(); RETAIN_ALL disables trim. */ winstate->nav_winobj->markptr = tuplestore_alloc_read_pointer(winstate->buffer, 0); @@ -2419,6 +2434,16 @@ ExecWindowAgg(PlanState *pstate) if (unlikely(winstate->all_first)) calculate_frame_offsets(pstate); + /* + * Resolve navigation offsets the same way, during first call (or after a + * rescan). Every RPR window holding a navigation comes through here: the + * pass at init resolved the constant offsets for EXPLAIN to display + * without validating them, so this is where a null or negative offset is + * rejected. + */ + if (unlikely(winstate->navResolvePending)) + resolve_nav_offsets(winstate); + /* We need to loop as the runCondition or qual may filter out tuples */ for (;;) { @@ -3030,13 +3055,8 @@ ExecInitWindowAgg(WindowAgg *node, EState *estate, int eflags) winstate->rpSkipTo = node->rpSkipTo; /* Set up row pattern recognition PATTERN clause (compiled NFA) */ winstate->rpPattern = node->rpPattern; - /* Set up nav offsets for tuplestore trim; resolve any NEEDS_EVAL kinds */ - winstate->navMaxOffsetKind = node->navMaxOffsetKind; - winstate->navMaxOffset = node->navMaxOffset; - winstate->hasFirstNav = node->hasFirstNav; - winstate->navFirstOffsetKind = node->navFirstOffsetKind; - winstate->navFirstOffset = node->navFirstOffset; - eval_define_offsets(winstate, node->defineClause); + /* Build nav offset bookkeeping; values are resolved per scan */ + build_define_offsets(winstate, node->defineClause); /* Copy match_start dependency bitmapset for per-context evaluation */ winstate->defineMatchStartDependent = bms_copy(node->defineMatchStartDependent); @@ -3096,6 +3116,12 @@ ExecInitWindowAgg(WindowAgg *node, EState *estate, int eflags) else winstate->nfaVarMatched = NULL; winstate->all_first = true; + + /* + * Nav offsets are resolved (and validated) at execution, like frame + * offsets: on the first scan and after each rescan, for every RPR window. + */ + winstate->navResolvePending = (winstate->rprNavOffsets != NIL); winstate->partition_spooled = false; winstate->more_partitions = false; winstate->next_partition = true; @@ -3186,6 +3212,8 @@ ExecReScanWindowAgg(WindowAggState *node) node->status = WINDOWAGG_RUN; node->all_first = true; + /* offsets are re-resolved and re-validated at the next scan */ + node->navResolvePending = (node->rprNavOffsets != NIL); /* release tuplestore et al */ release_partition(node); @@ -3970,231 +3998,379 @@ put_notnull_info(WindowObject winobj, int64 pos, int argno, bool isnull) } /* - * eval_nav_offset_helper - * Pre-evaluate a navigation offset expression at executor init time, to - * bound how far navigation can reach (which sizes the frame trim). - * Returns the offset value, or 0 for a NULL or negative offset. + * eval_nav_offset + * Evaluate a pre-built row pattern navigation offset ExprState. * - * The offset is not validated here. A NULL or negative value is caught later, - * per row, on the navigation path that consumes it (see EEOP_RPR_NAV_SET in - * execExprInterp.c), which errors out before navigation produces any result; - * the trim sizing computed from such an offset is therefore never used, and 0 - * is returned as a harmless placeholder. + * The offset is a run-time constant (the parser rejects column references in a + * navigation offset), so it is evaluated once per scan -- when any parameter + * is bound. Returns the offset as an int64; a NULL or negative result is an + * error per the SQL standard (fail-closed, re-checked on every scan). When + * not validating, a NULL is reported as -1 so that it takes the same path a + * negative offset takes. */ static int64 -eval_nav_offset_helper(WindowAggState *winstate, Expr *offset_expr, - int64 defaultOffset) +eval_nav_offset(WindowAggState *winstate, ExprState *estate, bool validate) { ExprContext *econtext = winstate->ss.ps.ps_ExprContext; - ExprState *estate; Datum val; bool isnull; int64 offset; - if (offset_expr == NULL) - return defaultOffset; - - estate = ExecInitExpr(offset_expr, (PlanState *) winstate); val = ExecEvalExprSwitchContext(estate, econtext, &isnull); if (isnull) - return 0; + { + if (validate) + ereport(ERROR, + errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED), + errmsg("row pattern navigation offset must not be null")); + return -1; /* the caller drops it from the reach */ + } offset = DatumGetInt64(val); - if (offset < 0) - return 0; + + if (offset < 0 && validate) + ereport(ERROR, + errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("row pattern navigation offset must not be negative")); return offset; } -typedef struct -{ - WindowAggState *winstate; - int64 maxOffset; /* max backward-reach offset across all nav - * exprs */ - bool maxOverflow; /* true if backward-reach overflow detected */ - int64 minFirstOffset; /* min forward-from-match_start offset; may be - * negative (PREV_FIRST: inner - outer < 0) */ -} EvalDefineOffsetsContext; - /* - * visit_nav_exec - * nav_traversal_walker callback (NavVisitFn) for the executor side. - * At each RPRNavExpr, evaluates the nav's offset expression(s) at - * runtime via eval_nav_offset_helper and accumulates: + * build_nav_offsets + * Create the per-navigation offset bookkeeping entry at executor init and + * compile its offset argument expression(s). * - * - maxOffset (backward reach): PREV, LAST-with-offset, compound - * PREV_LAST (sets maxOverflow on int64 overflow), compound - * NEXT_LAST (= max(inner - outer, 0)) - * - minFirstOffset (forward reach from match_start): FIRST, - * compound PREV_FIRST (= inner - outer, may be negative), - * compound NEXT_FIRST (= inner + outer, clamped to PG_INT64_MAX on - * overflow; always >= 0 so never updates minFirstOffset in practice) - * - * Counterpart of visit_nav_plan but using runtime evaluation instead of - * Const folding; runs only for offsets the planner marked NEEDS_EVAL. - * Match-start dependency is not recomputed here -- the planner's bitmapset - * is reused via winstate->defineMatchStartDependent. + * The offsets are not evaluated here: a PARAM_EXEC offset (function inlining or + * a LATERAL reference) has no value until the node is (re)scanned. The + * concrete value is resolved per scan by resolve_nav_offsets(), mirroring how + * calculate_frame_offsets() handles the window frame bounds. */ static void -visit_nav_exec(NavTraversal *t, RPRNavExpr *nav) +build_nav_offsets(RPRNavExpr *nav, WindowAggState *winstate) { - EvalDefineOffsetsContext *context = (EvalDefineOffsetsContext *) t->data; + RPRNavOffsets *entry = palloc0_object(RPRNavOffsets); /* - * Parser guarantee (mirrors visit_nav_plan): nav's direct children are - * never RPRNavExpr -- compound nesting is flattened in place and any - * other nesting is rejected. Outer-kind dispatch is sufficient. + * Parser guarantee (mirrors compute_matchStartDependent): nav's direct + * children are never RPRNavExpr -- compound nesting is flattened in place + * and any other nesting is rejected. Outer-kind dispatch is sufficient. */ Assert(nav->arg == NULL || !IsA(nav->arg, RPRNavExpr)); Assert(nav->offset_arg == NULL || !IsA(nav->offset_arg, RPRNavExpr)); Assert(nav->compound_offset_arg == NULL || !IsA(nav->compound_offset_arg, RPRNavExpr)); - /* Backward reach: PREV, LAST-with-offset */ - if (!context->maxOverflow) + entry->nav = nav; + if (nav->offset_arg != NULL) + entry->offset_state = ExecInitExpr(nav->offset_arg, + (PlanState *) winstate); + if (nav->compound_offset_arg != NULL) + entry->compound_offset_state = ExecInitExpr(nav->compound_offset_arg, + (PlanState *) winstate); + + /* + * Own the execution state of the compiled navigation. ExecInitExprRec() + * runs after this and reaches the entry by nav->navno; the offsets stay + * unset until resolve_nav_offsets() settles them for the scan. + */ + entry->rprnavstate = makeNode(RPRNavState); + entry->rprnavstate->winstate = winstate; + entry->rprnavstate->rprnavexpr = nav; + entry->rprnavstate->offset.isnull = true; + entry->rprnavstate->offset.value = (Datum) 0; + entry->rprnavstate->compound_offset.isnull = true; + entry->rprnavstate->compound_offset.value = (Datum) 0; + + winstate->rprNavOffsets = lappend(winstate->rprNavOffsets, entry); +} + +static bool +RPRNavExpr_walker(Node *node, WindowAggState *winstate) +{ + if (node == NULL) + return false; + if (IsA(node, RPRNavExpr)) + build_nav_offsets(castNode(RPRNavExpr, node), winstate); + + return expression_tree_walker(node, RPRNavExpr_walker, winstate); +} + +/* + * build_define_offsets + * At executor init, create one RPRNavOffsets entry per navigation in the + * DEFINE clause and compile its offset argument expressions. + * + * Entries are appended in walk order, the order compute_define_metadata() + * numbered them in, so entry i is the navigation with navno i. + * + * The concrete offset values -- and the tuplestore trim bounds derived from + * them -- are resolved later, per scan, by resolve_nav_offsets(). A non-RPR + * window has an empty DEFINE clause and falls through to the defaults. + */ +static void +build_define_offsets(WindowAggState *winstate, List *defineClause) +{ + EvalDefineOffsetsContext ctx; + + winstate->navMaxOffset = 0; + winstate->navMaxOffsetKind = RPR_NAV_OFFSET_FIXED; + winstate->hasMaxNav = false; + winstate->hasFirstNav = false; + winstate->navFirstOffset = 0; + winstate->navFirstOffsetKind = RPR_NAV_OFFSET_FIXED; + winstate->rprNavOffsets = NIL; + + if (defineClause == NIL) + return; + + foreach_node(TargetEntry, te, defineClause) { - int64 reach = 0; - bool gotReach = false; + RPRNavExpr_walker((Node *) te->expr, winstate); + } - if (nav->kind == RPR_NAV_PREV) + /* + * Resolve the offsets that are already constant at plan time, so EXPLAIN + * (which never executes, hence never reaches resolve_nav_offsets()) shows + * the real trim bounds. A parameterized offset (PARAM_EXTERN under a + * generic plan, or a PARAM_EXEC) has no value yet and is left for + * resolve_nav_offsets() to bound per scan. + */ + ctx.winstate = winstate; + ctx.maxOffset = 0; + ctx.maxOverflow = false; + ctx.minFirstOffset = PG_INT64_MAX; + ctx.hasMax = false; + ctx.hasFirst = false; + ctx.validate = false; /* init resolution is for EXPLAIN display only */ + + foreach_ptr(RPRNavOffsets, entry, winstate->rprNavOffsets) + { + RPRNavExpr *nav = entry->nav; + bool is_const; + + /* + * A foldable offset such as PREV(v, 1 + 1) counts as fixed only if + * eval_const_expressions() reached inside the navigation and left a + * Const here, which the expression tree mutator does for us. + */ + is_const = (nav->offset_arg == NULL || IsA(nav->offset_arg, Const)) && + (nav->compound_offset_arg == NULL || + IsA(nav->compound_offset_arg, Const)); + + if (is_const) { - reach = eval_nav_offset_helper(context->winstate, - nav->offset_arg, 1); - gotReach = true; + /* constant offset: resolvable now, for EXPLAIN and the scan */ + resolve_one_nav(entry, &ctx); } - else if (nav->kind == RPR_NAV_LAST && nav->offset_arg != NULL) + else { - reach = eval_nav_offset_helper(context->winstate, - nav->offset_arg, 0); - gotReach = true; + /* + * A parameterized offset (a bind PARAM_EXTERN or, via + * SRF/function inlining, a correlated PARAM_EXEC) has no + * dependable value at init. Like a window frame offset it is + * resolved at execution by resolve_nav_offsets(), and EXPLAIN + * shows "runtime". + */ + if (nav->kind == RPR_NAV_PREV || nav->kind == RPR_NAV_LAST || + nav->kind == RPR_NAV_PREV_LAST || nav->kind == RPR_NAV_NEXT_LAST) + { + ctx.hasMax = true; + winstate->navMaxOffsetKind = RPR_NAV_OFFSET_NEEDS_EVAL; + } + if (nav->kind == RPR_NAV_FIRST || nav->kind == RPR_NAV_PREV_FIRST || + nav->kind == RPR_NAV_NEXT_FIRST) + { + ctx.hasFirst = true; + winstate->navFirstOffsetKind = RPR_NAV_OFFSET_NEEDS_EVAL; + } } - else if (nav->kind == RPR_NAV_PREV_LAST || - nav->kind == RPR_NAV_NEXT_LAST) + } + + if (ctx.maxOverflow) + { + /* + * a const/bind overflow forces retain-all, unless a param already + * made this dimension "runtime" (NEEDS_EVAL wins for display) + */ + if (winstate->navMaxOffsetKind != RPR_NAV_OFFSET_NEEDS_EVAL) + winstate->navMaxOffsetKind = RPR_NAV_OFFSET_RETAIN_ALL; + } + else + winstate->navMaxOffset = ctx.maxOffset; + + winstate->hasMaxNav = ctx.hasMax; + + /* minFirstOffset is still PG_INT64_MAX when there is no FIRST */ + winstate->hasFirstNav = ctx.hasFirst; + winstate->navFirstOffset = ctx.minFirstOffset; +} + +/* + * resolve_one_nav + * Evaluate one navigation's offset(s) for the current scan, pin the + * resolved values into its RPRNavState, and accumulate the backward and + * forward reach used to size the tuplestore trim. + */ +static void +resolve_one_nav(RPRNavOffsets *entry, EvalDefineOffsetsContext *context) +{ + RPRNavExpr *nav = entry->nav; + int64 inner; + int64 outer; + + /* Inner offset */ + if (entry->offset_state != NULL) + inner = eval_nav_offset(context->winstate, entry->offset_state, + context->validate); + else if (nav->kind == RPR_NAV_PREV || nav->kind == RPR_NAV_NEXT) + inner = 1; + else + inner = 0; + + /* Outer (compound) offset */ + if (entry->compound_offset_state != NULL) + outer = eval_nav_offset(context->winstate, entry->compound_offset_state, + context->validate); + else + outer = 1; + + /* + * An offset that is negative, or null and therefore reported as -1, is + * rejected at execution, where eval_nav_offset() has already raised the + * error before we get here, so this navigation can never run and needs no + * rows retained. Leave it out of both reaches, which also keeps the + * arithmetic below on non-negative operands. + */ + if (inner < 0 || outer < 0) + { + Assert(!context->validate); + return; + } + + /* + * Pin the resolved values into the compiled navigation's RPRNavState, so + * ExecEvalRPRNavSet() reads this scan's constant instead of re-evaluating + * the offset per row. + */ + entry->rprnavstate->offset.isnull = false; + entry->rprnavstate->offset.value = Int64GetDatum(inner); + entry->rprnavstate->compound_offset.isnull = false; + entry->rprnavstate->compound_offset.value = Int64GetDatum(outer); + + /* + * Backward reach: PREV, LAST at any offset including the default 0, and + * compound PREV_LAST/NEXT_LAST. + */ + if (nav->kind == RPR_NAV_PREV || + nav->kind == RPR_NAV_LAST || + nav->kind == RPR_NAV_PREV_LAST || + nav->kind == RPR_NAV_NEXT_LAST) + { + context->hasMax = true; + + if (!context->maxOverflow) { - int64 inner = eval_nav_offset_helper(context->winstate, - nav->offset_arg, 0); - int64 outer = eval_nav_offset_helper(context->winstate, - nav->compound_offset_arg, 1); + int64 reach = 0; - if (nav->kind == RPR_NAV_PREV_LAST) + if (nav->kind == RPR_NAV_PREV || nav->kind == RPR_NAV_LAST) + reach = inner; + else if (nav->kind == RPR_NAV_PREV_LAST) { if (pg_add_s64_overflow(inner, outer, &reach)) context->maxOverflow = true; - else - gotReach = true; } else - { reach = Max(inner - outer, 0); - gotReach = true; - } - } - if (gotReach) - context->maxOffset = Max(context->maxOffset, reach); + if (!context->maxOverflow) + context->maxOffset = Max(context->maxOffset, reach); + } } /* Forward reach from match_start: FIRST, compound PREV_FIRST/NEXT_FIRST */ - if (nav->kind == RPR_NAV_FIRST) + if (nav->kind == RPR_NAV_FIRST || + nav->kind == RPR_NAV_PREV_FIRST || + nav->kind == RPR_NAV_NEXT_FIRST) { int64 reach; - reach = eval_nav_offset_helper(context->winstate, - nav->offset_arg, 0); - context->minFirstOffset = Min(context->minFirstOffset, reach); - } - else if (nav->kind == RPR_NAV_PREV_FIRST || - nav->kind == RPR_NAV_NEXT_FIRST) - { - int64 inner = eval_nav_offset_helper(context->winstate, - nav->offset_arg, 0); - int64 outer = eval_nav_offset_helper(context->winstate, - nav->compound_offset_arg, 1); - int64 reach; + context->hasFirst = true; - if (nav->kind == RPR_NAV_PREV_FIRST) - { - /* - * reach = inner - outer. Both are non-negative, so the result >= - * -PG_INT64_MAX, which cannot underflow int64. - */ - reach = inner - outer; - } + if (nav->kind == RPR_NAV_FIRST) + reach = inner; + else if (nav->kind == RPR_NAV_PREV_FIRST) + reach = inner - outer; /* both >= 0, cannot underflow int64 */ else { - /* - * NEXT_FIRST: reach = inner + outer. This can overflow, but the - * result is always >= 0, so it never updates minFirstOffset - * (which tracks the minimum). Clamp to PG_INT64_MAX on overflow. - */ + /* NEXT_FIRST: inner + outer, always >= 0; clamp on overflow */ if (pg_add_s64_overflow(inner, outer, &reach)) reach = PG_INT64_MAX; } + context->minFirstOffset = Min(context->minFirstOffset, reach); } } /* - * eval_define_offsets - * Evaluate non-constant nav offsets at executor init time. + * resolve_nav_offsets + * Resolve every navigation offset for the current scan and store the + * tuplestore trim bounds in the WindowAggState. * - * Called when the planner set navMaxOffsetKind and/or navFirstOffsetKind - * to RPR_NAV_OFFSET_NEEDS_EVAL because some offset contains a parameter - * or non-foldable expression. Updates only the fields whose kind was - * NEEDS_EVAL; FIXED kinds are left unchanged. - * - * On backward-reach overflow, sets navMaxOffsetKind to - * RPR_NAV_OFFSET_RETAIN_ALL so that tuplestore trim is disabled for - * backward navigation. + * Called from ExecWindowAgg on the first call and after every rescan -- the + * same place calculate_frame_offsets() resolves the window frame bounds. By + * then every parameter (PARAM_EXTERN and PARAM_EXEC alike) is bound, and the + * offset is a run-time constant, so a single evaluation per scan is correct. + * This keeps the trim finite for a parameterized offset (no retain-all) and + * revalidates it (fail-closed) on each scan. */ static void -eval_define_offsets(WindowAggState *winstate, List *defineClause) +resolve_nav_offsets(WindowAggState *winstate) { EvalDefineOffsetsContext ctx; - NavTraversal trav; - bool needsMax = (winstate->navMaxOffsetKind == RPR_NAV_OFFSET_NEEDS_EVAL); - bool needsFirst = (winstate->hasFirstNav && - winstate->navFirstOffsetKind == RPR_NAV_OFFSET_NEEDS_EVAL); - if (!needsMax && !needsFirst) + /* Servicing the request now; clear the per-scan pending flag */ + winstate->navResolvePending = false; + + winstate->navMaxOffset = 0; + winstate->navMaxOffsetKind = RPR_NAV_OFFSET_FIXED; + winstate->hasMaxNav = false; + winstate->hasFirstNav = false; + winstate->navFirstOffset = 0; + winstate->navFirstOffsetKind = RPR_NAV_OFFSET_FIXED; + + if (winstate->rprNavOffsets == NIL) return; ctx.winstate = winstate; ctx.maxOffset = 0; ctx.maxOverflow = false; ctx.minFirstOffset = PG_INT64_MAX; + ctx.hasMax = false; + ctx.hasFirst = false; + ctx.validate = true; /* execution: fail-closed on null/negative */ - trav.visit = visit_nav_exec; - trav.data = &ctx; - - foreach_node(TargetEntry, te, defineClause) + foreach_ptr(RPRNavOffsets, entry, winstate->rprNavOffsets) { - nav_traversal_walker((Node *) te->expr, &trav); + resolve_one_nav(entry, &ctx); } - if (needsMax) - { - if (ctx.maxOverflow) - { - winstate->navMaxOffsetKind = RPR_NAV_OFFSET_RETAIN_ALL; - winstate->navMaxOffset = 0; - } - else - { - winstate->navMaxOffsetKind = RPR_NAV_OFFSET_FIXED; - winstate->navMaxOffset = ctx.maxOffset; - } - } + /* + * Backward (PREV/LAST) reach. On int64 overflow the lookback cannot be + * bounded, so mark the dimension RETAIN_ALL; advance_nav_mark() reads it + * to disable tuplestore trim. + */ + if (ctx.maxOverflow) + winstate->navMaxOffsetKind = RPR_NAV_OFFSET_RETAIN_ALL; + else + winstate->navMaxOffset = ctx.maxOffset; - if (needsFirst) - { - winstate->navFirstOffsetKind = RPR_NAV_OFFSET_FIXED; - if (ctx.minFirstOffset < PG_INT64_MAX) - winstate->navFirstOffset = ctx.minFirstOffset; - else - winstate->navFirstOffset = PG_INT64_MAX; - } + winstate->hasMaxNav = ctx.hasMax; + + /* Forward (FIRST) reach; never needs a retain-all sentinel */ + winstate->hasFirstNav = ctx.hasFirst; + winstate->navFirstOffset = ctx.minFirstOffset; } /* @@ -4378,13 +4554,10 @@ advance_nav_mark(WindowAggState *winstate, int64 currentPos) if (winstate->nav_winobj == NULL) return; - /* RETAIN_ALL disables trim for the backward (PREV/LAST) dimension */ + /* RETAIN_ALL (offset overflow) disables trim for the backward dimension */ if (winstate->navMaxOffsetKind == RPR_NAV_OFFSET_RETAIN_ALL) return; - /* navMax is FIXED here: NEEDS_EVAL resolved, RETAIN_ALL returned */ - Assert(winstate->navMaxOffsetKind == RPR_NAV_OFFSET_FIXED); - if (currentPos > winstate->navMaxOffset) navmarkpos = currentPos - winstate->navMaxOffset; else @@ -4394,9 +4567,6 @@ advance_nav_mark(WindowAggState *winstate, int64 currentPos) { int64 firstreach; - /* navFirst is always FIXED; it never takes RETAIN_ALL */ - Assert(winstate->navFirstOffsetKind == RPR_NAV_OFFSET_FIXED); - /* * Head context has the smallest matchStartRow (contexts appended in * nondecreasing order), so bounding by it covers every FIRST reach. diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c index d614df9bb48..867fdbd77ab 100644 --- a/src/backend/optimizer/plan/createplan.c +++ b/src/backend/optimizer/plan/createplan.c @@ -19,7 +19,6 @@ #include "access/sysattr.h" #include "access/transam.h" #include "catalog/pg_class.h" -#include "common/int.h" #include "foreign/fdwapi.h" #include "miscadmin.h" #include "nodes/extensible.h" @@ -299,9 +298,6 @@ static WindowAgg *make_windowagg(List *tlist, WindowClause *wc, List *runCondition, RPRPattern *compiledPattern, Bitmapset *defineMatchStartDependent, - RPRNavOffsetKind navMaxOffsetKind, int64 navMaxOffset, - bool hasFirstNav, - RPRNavOffsetKind navFirstOffsetKind, int64 navFirstOffset, List *qual, bool topWindow, Plan *lefttree); static Group *make_group(List *tlist, List *qual, int numGroupCols, @@ -2473,96 +2469,39 @@ create_minmaxagg_plan(PlannerInfo *root, MinMaxAggPath *best_path) } /* - * DefineMetadataContext - context for compute_define_metadata walker. + * DefineMetadataContext - context for the DEFINE clause walk below. * - * Collects three pieces of metadata from the DEFINE clause in a single - * tree walk per variable: - * - backward reach (PREV, LAST-with-offset, compound PREV_LAST/NEXT_LAST) - * - forward-from-match-start reach (FIRST, compound PREV_FIRST/NEXT_FIRST) - * - per-variable match_start dependency (variables containing FIRST, - * LAST-with-offset, or compound PREV_FIRST/NEXT_FIRST/PREV_LAST/ - * NEXT_LAST-with-offset require per-context re-evaluation) + * The walk classifies one thing: which DEFINE variables depend on the match + * start, which is what buildRPRPattern() needs to decide context absorption. + * The trim offsets are not plan-time metadata; the executor records them at + * init (build_define_offsets) and settles their values per scan + * (resolve_nav_offsets), both in nodeWindowAgg.c. * - * The driver sets curVarIdx to the index of the variable being walked - * before each invocation; the walker uses it to populate matchStartDependent. + * The driver sets curVarIdx to the index of the variable being walked before + * each invocation; the walker uses it to populate matchStartDependent. */ typedef struct DefineMetadataContext { - int64 maxOffset; /* max PREV/LAST backward offset (>= 0) */ - bool maxNeedsEval; /* non-constant PREV/LAST offset found */ - bool maxOverflow; /* constant offset overflow detected */ - int64 firstOffset; /* min FIRST offset (may be negative for - * PREV_FIRST) */ - bool hasFirst; /* any FIRST node found */ - bool firstNeedsEval; /* non-constant FIRST offset found */ int curVarIdx; /* DEFINE variable currently being walked */ + int navno; /* next RPRNavExpr.navno to assign */ Bitmapset *matchStartDependent; /* variables that depend on * match_start */ } DefineMetadataContext; /* - * Helper: extract constant offset from an expression, handling NULL/negative. - * If expr is NULL, returns defaultOffset. - * Returns true if constant, false if non-constant (Param, cast, etc.). - */ -static bool -extract_const_offset(Expr *expr, int64 defaultOffset, int64 *result) -{ - if (expr == NULL) - { - *result = defaultOffset; - return true; - } - - if (IsA(expr, Const)) - { - Const *c = (Const *) expr; - - if (c->constisnull) - *result = 0; /* runtime error; safe placeholder */ - else - { - *result = DatumGetInt64(c->constvalue); - if (*result < 0) - *result = 0; /* runtime error; safe placeholder */ - } - return true; - } - - return false; /* non-constant */ -} - -/* - * visit_nav_plan - * nav_traversal_walker callback (NavVisitFn) for the planner side. - * At each RPRNavExpr in a DEFINE expression, computes: - * - * 1. backward reach (maxOffset) for tuplestore trim: - * - PREV(v, N), LAST(v, N) -> N (default 1) - * - compound PREV_LAST(v, N, M) -> N + M (overflow -> maxOverflow) - * - compound NEXT_LAST(v, N, M) -> max(N - M, 0) - * - * 2. forward reach (firstOffset) for tuplestore trim: - * - FIRST(v, N) -> N (default 0) - * - compound PREV_FIRST(v, N, M) -> N - M (may be negative) - * - compound NEXT_FIRST(v, N, M) -> N + M + * compute_matchStartDependent * - * 3. per-variable match_start dependency for absorption suppression: - * outer nav kinds that reach match_start (FIRST, LAST-with-offset, - * PREV_FIRST, NEXT_FIRST, PREV_LAST/NEXT_LAST-with-offset) add - * curVarIdx to matchStartDependent. + * per-variable match_start dependency for absorption suppression: outer nav + * kinds that reach match_start (FIRST, LAST-with-offset, PREV_FIRST, + * NEXT_FIRST, PREV_LAST/NEXT_LAST-with-offset) add curVarIdx to + * matchStartDependent. * - * Constant offsets are extracted via extract_const_offset; non-constant - * offsets set maxNeedsEval / firstNeedsEval so the executor can resolve - * them at init time (see visit_nav_exec). Classification uses only the - * outer nav kind: parser nesting restrictions prevent FIRST/LAST inside - * a PREV/NEXT value subexpression. + * Classification uses only the outer nav kind: parser nesting restrictions + * prevent FIRST/LAST inside a PREV/NEXT value subexpression. */ static void -visit_nav_plan(NavTraversal *t, RPRNavExpr *nav) +compute_matchStartDependent(RPRNavExpr *nav, DefineMetadataContext *context) { - DefineMetadataContext *context = (DefineMetadataContext *) t->data; - /* * Parser guarantee: by the time the planner sees a DEFINE expression, * compound nesting has been flattened into a single RPRNavExpr and any @@ -2575,130 +2514,6 @@ visit_nav_plan(NavTraversal *t, RPRNavExpr *nav) Assert(nav->compound_offset_arg == NULL || !IsA(nav->compound_offset_arg, RPRNavExpr)); - /* - * Simple PREV(v, N) and LAST(v, N): backward reach from currentpos. LAST - * without offset = currentpos, no backward reach. NEXT: forward only, - * irrelevant for trim. - */ - if (nav->kind == RPR_NAV_PREV || - (nav->kind == RPR_NAV_LAST && nav->offset_arg != NULL)) - { - if (!context->maxNeedsEval) - { - int64 offset; - - /* - * default 1 is for PREV; the guarded LAST sub-case never uses it. - */ - if (extract_const_offset(nav->offset_arg, 1, &offset)) - context->maxOffset = Max(context->maxOffset, offset); - else - context->maxNeedsEval = true; - } - } - - /* - * Simple FIRST(v, N): forward reach from match_start. Smaller N means - * older rows needed. - */ - if (nav->kind == RPR_NAV_FIRST) - { - context->hasFirst = true; - - if (!context->firstNeedsEval) - { - int64 offset; - - if (extract_const_offset(nav->offset_arg, 0, &offset)) - context->firstOffset = Min(context->firstOffset, offset); - else - context->firstNeedsEval = true; - } - } - - /* - * Compound PREV_LAST / NEXT_LAST: base = currentpos. PREV_LAST(v, N, M): - * target = currentpos - N - M -> lookback = N + M NEXT_LAST(v, N, M): - * target = currentpos - N + M -> lookback = max(N - M, 0) - */ - if (nav->kind == RPR_NAV_PREV_LAST || - nav->kind == RPR_NAV_NEXT_LAST) - { - if (!context->maxNeedsEval) - { - int64 inner; - int64 outer; - int64 reach; - - if (extract_const_offset(nav->offset_arg, 0, &inner) && - extract_const_offset(nav->compound_offset_arg, 1, &outer)) - { - if (nav->kind == RPR_NAV_PREV_LAST) - { - if (pg_add_s64_overflow(inner, outer, &reach)) - context->maxOverflow = true; - else - context->maxOffset = Max(context->maxOffset, reach); - } - else - { - reach = Max(inner - outer, 0); - context->maxOffset = Max(context->maxOffset, reach); - } - } - else - context->maxNeedsEval = true; - } - } - - /* - * Compound PREV_FIRST / NEXT_FIRST: base = match_start. PREV_FIRST(v, N, - * M): target = match_start + N - M NEXT_FIRST(v, N, M): target = - * match_start + N + M The combined offset (N+/-M) from match_start can be - * negative, meaning rows before match_start are needed. - */ - if (nav->kind == RPR_NAV_PREV_FIRST || - nav->kind == RPR_NAV_NEXT_FIRST) - { - context->hasFirst = true; - - if (!context->firstNeedsEval) - { - int64 inner; - int64 outer; - int64 reach; - - if (extract_const_offset(nav->offset_arg, 0, &inner) && - extract_const_offset(nav->compound_offset_arg, 1, &outer)) - { - if (nav->kind == RPR_NAV_PREV_FIRST) - { - /* - * reach = inner - outer. Both are non-negative, so the - * result >= -PG_INT64_MAX, which cannot underflow int64. - * No overflow check needed. - */ - reach = inner - outer; - } - else - { - /* - * NEXT_FIRST: reach = inner + outer. This can overflow, - * but the result is always >= 0, so it never updates - * firstOffset (which tracks the minimum). Clamp to - * PG_INT64_MAX on overflow. - */ - if (pg_add_s64_overflow(inner, outer, &reach)) - reach = PG_INT64_MAX; - } - - context->firstOffset = Min(context->firstOffset, reach); - } - else - context->firstNeedsEval = true; - } - } - /* * Match-start dependency: classify the outer nav kind. A constant * LAST(x, 0) is conservatively included (offset_arg is a non-NULL Const), @@ -2717,90 +2532,59 @@ visit_nav_plan(NavTraversal *t, RPRNavExpr *nav) context->curVarIdx); } +static bool +RPRNavExpr_walker(Node *node, DefineMetadataContext *ctx) +{ + if (node == NULL) + return false; + if (IsA(node, RPRNavExpr)) + { + RPRNavExpr *nav = castNode(RPRNavExpr, node); + + nav->navno = ctx->navno++; + compute_matchStartDependent(nav, ctx); + } + + return expression_tree_walker(node, RPRNavExpr_walker, ctx); +} + /* * compute_define_metadata - * Compute navigation offsets and match_start dependency for the - * DEFINE clause in a single pass per variable. + * Classify which DEFINE variables depend on the match start, and number + * the navigations. + * + * Walks each DEFINE variable expression once and returns the set of variable + * indices whose navigation reaches match_start: those containing FIRST or a + * compound PREV_FIRST/NEXT_FIRST, or a LAST that carries an offset of its + * own, whether plain or inside a compound PREV_LAST/NEXT_LAST. Such + * variables require per-context re-evaluation during NFA processing, and + * their presence disqualifies the pattern from context absorption. * - * Walks each DEFINE variable expression once, computing: - * - maxOffset: max backward reach from PREV, LAST-with-offset, - * compound PREV_LAST/NEXT_LAST - * - hasFirst/firstOffset: min forward-from-match-start reach from - * FIRST, compound PREV_FIRST/NEXT_FIRST - * - matchStartDependent: bitmapset of variable indices whose - * expressions contain navigation that depends on match_start - * (FIRST, LAST-with-offset, or compound PREV_FIRST/NEXT_FIRST/ - * PREV_LAST/NEXT_LAST-with-offset). Such variables require - * per-context re-evaluation during NFA processing. + * The same walk assigns RPRNavExpr.navno in visit order, which is the order + * the executor builds WindowAggState.rprNavOffsets in. + * + * Navigation offsets for tuplestore trim are not computed here; they are + * built at executor init (build_define_offsets) and settled per scan + * (resolve_nav_offsets), which can evaluate non-constant offsets that the + * planner cannot fold. */ static void -compute_define_metadata(List *defineClause, - RPRNavOffsetKind *maxKind, int64 *maxResult, - bool *hasFirst, - RPRNavOffsetKind *firstKind, int64 *firstResult, - Bitmapset **matchStartDependent) +compute_define_metadata(List *defineClause, Bitmapset **matchStartDependent) { DefineMetadataContext ctx; - NavTraversal trav; - - ctx.maxOffset = 0; - ctx.maxNeedsEval = false; - ctx.maxOverflow = false; - ctx.firstOffset = PG_INT64_MAX; /* sentinel: no FIRST found yet */ - ctx.hasFirst = false; - ctx.firstNeedsEval = false; + ctx.curVarIdx = 0; + ctx.navno = 0; ctx.matchStartDependent = NULL; - trav.visit = visit_nav_plan; - trav.data = &ctx; - foreach_node(TargetEntry, te, defineClause) { - nav_traversal_walker((Node *) te->expr, &trav); - ctx.curVarIdx++; - } - - *matchStartDependent = ctx.matchStartDependent; + ctx.curVarIdx = foreach_current_index(te); - /* Max backward offset */ - if (ctx.maxOverflow) - { - *maxKind = RPR_NAV_OFFSET_RETAIN_ALL; - *maxResult = 0; - } - else if (ctx.maxNeedsEval) - { - *maxKind = RPR_NAV_OFFSET_NEEDS_EVAL; - *maxResult = 0; - } - else - { - *maxKind = RPR_NAV_OFFSET_FIXED; - *maxResult = ctx.maxOffset; + RPRNavExpr_walker((Node *) te->expr, &ctx); } - /* First offset (can be negative for compound PREV_FIRST) */ - *hasFirst = ctx.hasFirst; - if (ctx.hasFirst) - { - if (ctx.firstNeedsEval) - { - *firstKind = RPR_NAV_OFFSET_NEEDS_EVAL; - *firstResult = 0; - } - else - { - *firstKind = RPR_NAV_OFFSET_FIXED; - *firstResult = ctx.firstOffset; /* may be negative; PG_INT64_MAX - * if overflowed */ - } - } - else - { - *firstKind = RPR_NAV_OFFSET_FIXED; - *firstResult = 0; - } + *matchStartDependent = ctx.matchStartDependent; } /* @@ -2829,12 +2613,6 @@ create_windowagg_plan(PlannerInfo *root, WindowAggPath *best_path) ListCell *lc; RPRPattern *compiledPattern = NULL; Bitmapset *matchStartDependent = NULL; - RPRNavOffsetKind navMaxOffsetKind = RPR_NAV_OFFSET_FIXED; - int64 navMaxOffset = 0; - bool hasFirstNav = false; - RPRNavOffsetKind navFirstOffsetKind = RPR_NAV_OFFSET_FIXED; - int64 navFirstOffset = 0; - /* * Choice of tlist here is motivated by the fact that WindowAgg will be @@ -2889,15 +2667,11 @@ create_windowagg_plan(PlannerInfo *root, WindowAggPath *best_path) if (wc->rpPattern) { /* - * Walk DEFINE once: collect nav offsets (for tuplestore trim) and the - * bitmapset of match_start-dependent variables (for absorption - * suppression in buildRPRPattern). + * Classify which DEFINE variables depend on match_start (for + * absorption suppression in buildRPRPattern). Nav offsets for + * tuplestore trim are resolved later, at executor init. */ - compute_define_metadata(wc->defineClause, - &navMaxOffsetKind, &navMaxOffset, - &hasFirstNav, - &navFirstOffsetKind, &navFirstOffset, - &matchStartDependent); + compute_define_metadata(wc->defineClause, &matchStartDependent); /* Compile and optimize RPR patterns */ compiledPattern = buildRPRPattern(wc->rpPattern, @@ -2921,11 +2695,6 @@ create_windowagg_plan(PlannerInfo *root, WindowAggPath *best_path) best_path->runCondition, compiledPattern, matchStartDependent, - navMaxOffsetKind, - navMaxOffset, - hasFirstNav, - navFirstOffsetKind, - navFirstOffset, best_path->qual, best_path->topwindow, subplan); @@ -7026,9 +6795,6 @@ make_windowagg(List *tlist, WindowClause *wc, List *runCondition, RPRPattern *compiledPattern, Bitmapset *defineMatchStartDependent, - RPRNavOffsetKind navMaxOffsetKind, int64 navMaxOffset, - bool hasFirstNav, - RPRNavOffsetKind navFirstOffsetKind, int64 navFirstOffset, List *qual, bool topWindow, Plan *lefttree) { WindowAgg *node = makeNode(WindowAgg); @@ -7066,13 +6832,6 @@ make_windowagg(List *tlist, WindowClause *wc, /* Store pre-computed match_start dependency bitmapset */ node->defineMatchStartDependent = defineMatchStartDependent; - /* Store pre-computed nav offsets for tuplestore trim optimization */ - node->navMaxOffsetKind = navMaxOffsetKind; - node->navMaxOffset = navMaxOffset; - node->hasFirstNav = hasFirstNav; - node->navFirstOffsetKind = navFirstOffsetKind; - node->navFirstOffset = navFirstOffset; - plan->targetlist = tlist; plan->lefttree = lefttree; plan->righttree = NULL; diff --git a/src/backend/optimizer/plan/rpr.c b/src/backend/optimizer/plan/rpr.c index b13b2db7384..dbfc71ab788 100644 --- a/src/backend/optimizer/plan/rpr.c +++ b/src/backend/optimizer/plan/rpr.c @@ -2163,36 +2163,3 @@ buildRPRPattern(RPRPatternNode *pattern, List *defineClause, return result; } - -/* - * nav_traversal_walker - * Shared expression-tree walker that locates RPRNavExpr nodes in a - * DEFINE expression and dispatches each one to a caller-supplied - * visitor. Used by: - * - planner (visit_nav_plan in createplan.c) to collect tuplestore - * trim offsets and per-variable match_start dependency - * - executor (visit_nav_exec in nodeWindowAgg.c) to evaluate - * non-constant nav offsets at WindowAggState init time - * - * The driver wraps a mode-specific context in a NavTraversal and passes - * it as ctx; the visitor casts t->data to its own context type. Children - * of an RPRNavExpr are not walked: the parser's nesting restrictions - * ensure offsets and dependencies are fully captured by the outer nav - * kind, so the visitor only needs to inspect the RPRNavExpr itself. - */ -bool -nav_traversal_walker(Node *node, void *ctx) -{ - if (node == NULL) - return false; - - if (IsA(node, RPRNavExpr)) - { - NavTraversal *t = (NavTraversal *) ctx; - - t->visit(t, (RPRNavExpr *) node); - return false; - } - - return expression_tree_walker(node, nav_traversal_walker, ctx); -} diff --git a/src/backend/parser/parse_func.c b/src/backend/parser/parse_func.c index 364e751f282..d753f101964 100644 --- a/src/backend/parser/parse_func.c +++ b/src/backend/parser/parse_func.c @@ -2195,6 +2195,7 @@ ParseRPRNavCall(ParseState *pstate, List *funcname, List *fargs, navexpr = makeNode(RPRNavExpr); navexpr->kind = kind; navexpr->arg = (Expr *) arg; + navexpr->navno = -1; /* assigned while planning */ /* an explicit offset is coerced to int8, which the executor reads */ if (nargs == 2) diff --git a/src/include/executor/execExpr.h b/src/include/executor/execExpr.h index db66ebe313c..ac1b0be0c2a 100644 --- a/src/include/executor/execExpr.h +++ b/src/include/executor/execExpr.h @@ -702,13 +702,7 @@ typedef struct ExprEvalStep /* for EEOP_RPR_NAV_SET / EEOP_RPR_NAV_RESTORE */ struct { - WindowAggState *winstate; - RPRNavKind kind; /* navigation kind (simple or compound) */ - Datum *offset_value; /* offset value(s), or NULL */ - bool *offset_isnull; /* offset null flag(s) */ - /* For compound nav: offset_value[0] = inner, [1] = outer */ - int16 resulttyplen; /* RESTORE: result type length */ - bool resulttypbyval; /* RESTORE: result pass-by-value? */ + RPRNavState *rprnavstate; } rpr_nav; /* for EEOP_AGG_*DESERIALIZE */ diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h index 01c2355b576..09ebe60be78 100644 --- a/src/include/nodes/execnodes.h +++ b/src/include/nodes/execnodes.h @@ -70,6 +70,7 @@ typedef struct TupleTableSlot TupleTableSlot; typedef struct TupleTableSlotOps TupleTableSlotOps; typedef struct WalUsage WalUsage; typedef struct WorkerNodeInstrumentation WorkerNodeInstrumentation; +typedef struct WindowAggState WindowAggState; /* ---------------- @@ -1073,6 +1074,52 @@ typedef struct SubPlanState ExprState *cur_eq_comp; /* equality comparator for LHS vs. table */ } SubPlanState; +typedef struct RPRNavState +{ + NodeTag type; + + WindowAggState *winstate; + RPRNavExpr *rprnavexpr; + + /* + * Resolved navigation offsets for this execution, captured from + * winstate->rprNavOffsets at expression compile time. These live in + * executor state (not on the RPRNavExpr) because plan trees are read-only + * and may be shared by concurrent executions. + */ + NullableDatum offset; /* inner offset */ + NullableDatum compound_offset; /* outer offset for compound nav */ + int16 resulttyplen; /* RESTORE: result type length */ + bool resulttypbyval; /* RESTORE: result pass-by-value? */ +} RPRNavState; + +/* + * RPRNavOffsetKind - status of a resolved navigation trim offset + * (WindowAggState.navMaxOffset / navFirstOffset) + */ +typedef enum RPRNavOffsetKind +{ + RPR_NAV_OFFSET_FIXED, /* resolved constant; use the offset value */ + RPR_NAV_OFFSET_NEEDS_EVAL, /* non-constant offset; shows "runtime", + * resolved per scan */ + RPR_NAV_OFFSET_RETAIN_ALL, /* offset overflow; retain all rows (no trim) */ +} RPRNavOffsetKind; + +/* + * RPRNavOffsets - one entry of WindowAggState.rprNavOffsets + * + * Associates an RPRNavExpr from the (read-only) plan tree with its offsets, + * built by build_define_offsets() at executor startup and settled once per + * scan by resolve_nav_offsets(). The list is in RPRNavExpr.navno order. + */ +typedef struct RPRNavOffsets +{ + RPRNavExpr *nav; /* plan-tree node this entry belongs to */ + ExprState *offset_state; /* inner offset expr, evaluated once per scan */ + ExprState *compound_offset_state; /* outer (compound) offset expr */ + RPRNavState *rprnavstate; /* execution state; holds the resolved values */ +} RPRNavOffsets; + /* * DomainConstraintState - one item to check during CoerceToDomain * @@ -2567,7 +2614,8 @@ typedef struct RPRNFAContext RPRNFAState *states; /* active states (linked list) */ int64 matchStartRow; /* row where match started */ - int64 matchEndRow; /* row where match ended (-1 = no match) */ + int64 matchEndRow; /* last row of the match; below matchStartRow + * for an empty one, -1 before any */ int64 lastProcessedRow; /* last row processed (for fail depth) */ RPRNFAState *matchedState; /* this context's match candidate, or NULL */ bool matchUpdated; /* matchedState was set or replaced during the @@ -2742,14 +2790,31 @@ typedef struct WindowAggState TupleTableSlot *temp_slot_2; /* RPR navigation */ + + /* + * per-execution resolved nav offsets: list of RPRNavOffsets, indexed by + * RPRNavExpr.navno; built by build_define_offsets() + */ + List *rprNavOffsets; + bool navResolvePending; /* nav offsets need (re)resolving at the + * next ExecWindowAgg call; set at init + * and rescan, cleared by + * resolve_nav_offsets() */ + bool hasMaxNav; /* backward nav in DEFINE: PREV, LAST, + * compound PREV_LAST/NEXT_LAST */ + bool hasFirstNav; /* forward nav in DEFINE: FIRST, compound + * PREV_FIRST/NEXT_FIRST */ RPRNavOffsetKind navMaxOffsetKind; /* status of navMaxOffset */ int64 navMaxOffset; /* max backward nav offset (when FIXED) */ - bool hasFirstNav; /* FIRST() present in DEFINE */ RPRNavOffsetKind navFirstOffsetKind; /* status of navFirstOffset */ - int64 navFirstOffset; /* min FIRST() offset (when FIXED) */ + int64 navFirstOffset; /* min forward reach from match_start (when + * FIXED); negative when a compound PREV_FIRST + * reaches back past it */ struct WindowObjectData *nav_winobj; /* winobj for RPR */ int64 nav_slot_pos; /* position cached in nav_slot, or -1 */ - TupleTableSlot *nav_slot; /* slot for PREV/NEXT/FIRST/LAST target row */ + TupleTableSlot *nav_slot; /* slot holding the resolved navigation target + * row (simple or compound + * PREV/NEXT/FIRST/LAST) */ TupleTableSlot *nav_saved_outertuple; /* saved slot during nav swap */ int64 nav_match_start; /* match_start for FIRST/LAST nav */ diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index 9a7aba27171..5cf03292a02 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -593,21 +593,6 @@ typedef enum RPSkipTo ST_PAST_LAST_ROW, /* SKIP TO PAST LAST ROW */ } RPSkipTo; -/* - * RPRNavOffsetKind - status of navigation offset for tuplestore trim. - * - * The planner computes navMaxOffset/navFirstOffset for tuplestore mark - * optimization. This enum tracks whether the value is a resolved constant, - * needs runtime evaluation, or cannot be determined (retain all rows). - */ -typedef enum RPRNavOffsetKind -{ - RPR_NAV_OFFSET_FIXED, /* resolved constant; use the offset value */ - RPR_NAV_OFFSET_NEEDS_EVAL, /* non-constant offset; evaluate at executor - * init */ - RPR_NAV_OFFSET_RETAIN_ALL, /* cannot determine; retain all rows (no trim) */ -} RPRNavOffsetKind; - /* * RPRPatternNodeType - Row Pattern Recognition pattern node types */ diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h index d8fc3615246..c350df2eeeb 100644 --- a/src/include/nodes/plannodes.h +++ b/src/include/nodes/plannodes.h @@ -1409,33 +1409,13 @@ typedef struct WindowAgg /* * Bitmapset of DEFINE variable indices whose expressions depend on - * match_start (contain FIRST, LAST-with-offset, or compound - * PREV_FIRST/NEXT_FIRST/PREV_LAST/NEXT_LAST with offset). Variables in - * this set require per-context re-evaluation during NFA processing. + * match_start: they contain FIRST or a compound PREV_FIRST/NEXT_FIRST, or + * a LAST that carries an offset of its own, whether plain or inside a + * compound PREV_LAST/NEXT_LAST. Variables in this set require per-context + * re-evaluation during NFA processing. */ Bitmapset *defineMatchStartDependent; - /* - * Navigation offset status and values for tuplestore mark optimization. - * See RPRNavOffsetKind in nodes/parsenodes.h. - * - * navMaxOffset: maximum backward reach from currentpos (contributed by - * PREV, LAST-with-offset, compound PREV_LAST/NEXT_LAST). Only valid when - * navMaxOffsetKind == RPR_NAV_OFFSET_FIXED. - * - * navFirstOffset: minimum forward offset from match_start (contributed by - * FIRST, compound PREV_FIRST/NEXT_FIRST). Can be negative for compound - * PREV_FIRST. Only valid when navFirstOffsetKind == RPR_NAV_OFFSET_FIXED - * and hasFirstNav == true. - */ - RPRNavOffsetKind navMaxOffsetKind; - int64 navMaxOffset; - - /* true if FIRST-based navigation (FIRST, PREV_FIRST, NEXT_FIRST) is used */ - bool hasFirstNav; - RPRNavOffsetKind navFirstOffsetKind; - int64 navFirstOffset; - /* * false for all apart from the WindowAgg that's closest to the root of * the plan diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h index e4ad880dea9..bd47555a737 100644 --- a/src/include/nodes/primnodes.h +++ b/src/include/nodes/primnodes.h @@ -668,15 +668,15 @@ typedef struct WindowFuncRunCondition */ typedef enum RPRNavKind { - RPR_NAV_PREV, - RPR_NAV_NEXT, - RPR_NAV_FIRST, - RPR_NAV_LAST, + RPR_NAV_PREV, /* offset default: 1 */ + RPR_NAV_NEXT, /* offset default: 1 */ + RPR_NAV_FIRST, /* offset default: 0 */ + RPR_NAV_LAST, /* offset default: 0 */ /* compound: outer(inner(arg)) */ - RPR_NAV_PREV_FIRST, - RPR_NAV_PREV_LAST, - RPR_NAV_NEXT_FIRST, - RPR_NAV_NEXT_LAST, + RPR_NAV_PREV_FIRST, /* (offset, compound_offset) default: (0, 1) */ + RPR_NAV_PREV_LAST, /* (offset, compound_offset) default: (0, 1) */ + RPR_NAV_NEXT_FIRST, /* (offset, compound_offset) default: (0, 1) */ + RPR_NAV_NEXT_LAST, /* (offset, compound_offset) default: (0, 1) */ } RPRNavKind; typedef struct RPRNavExpr @@ -686,7 +686,11 @@ typedef struct RPRNavExpr Expr *arg; /* argument expression */ Expr *offset_arg; /* offset expression, or NULL for default */ Expr *compound_offset_arg; /* outer offset for compound nav, or - * NULL if simple */ + * NULL for its default */ + + /* unique ID within the WindowAgg; -1 until the planner assigns it */ + int navno pg_node_attr(query_jumble_ignore); + /* result type (same as arg's type) */ Oid resulttype pg_node_attr(query_jumble_ignore); /* OID of collation of result */ diff --git a/src/include/optimizer/rpr.h b/src/include/optimizer/rpr.h index 800f547cbb1..20847d89a4a 100644 --- a/src/include/optimizer/rpr.h +++ b/src/include/optimizer/rpr.h @@ -84,26 +84,4 @@ extern RPRPattern *buildRPRPattern(RPRPatternNode *pattern, List *defineClause, RPSkipTo rpSkipTo, int frameOptions, bool hasMatchStartDependent); -/* - * Shared traversal walker for DEFINE clause RPRNavExpr collection. - * - * Both planner (nav-offset / match_start dependency analysis) and executor - * (runtime offset evaluation) need to walk DEFINE expressions and dispatch - * per RPRNavExpr. They differ only in what they do at each nav node, so - * the traversal frame is shared (nav_traversal_walker, defined in rpr.c) - * and the per-nav action is supplied as a callback. The driver allocates - * a mode-specific context, points NavTraversal.data at it, and casts - * inside its visitor. - */ -struct NavTraversal; -typedef void (*NavVisitFn) (struct NavTraversal *t, RPRNavExpr *nav); - -typedef struct NavTraversal -{ - NavVisitFn visit; - void *data; /* mode-specific context */ -} NavTraversal; - -extern bool nav_traversal_walker(Node *node, void *ctx); - #endif /* RPR_H */ diff --git a/src/test/regress/expected/rpr.out b/src/test/regress/expected/rpr.out index 6080398f583..82980c0e41d 100644 --- a/src/test/regress/expected/rpr.out +++ b/src/test/regress/expected/rpr.out @@ -1379,11 +1379,9 @@ LINE 7: DEFINE A AS (stock.*) IS NOT NULL -- -- 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! +-- PREV(price, 2): with A=any, B matches where the price beats the one two rows +-- back. On company1 (100, 200, 150, 140, 150, 90, 110, 130, 120, 130) that is +-- 200 -> 150, then 110 -> 130 -> 120, which stops where 130 only ties 130. SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w, count(*) OVER w FROM stock @@ -1419,8 +1417,9 @@ WINDOW w AS ( 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. +-- NEXT(price, 2): A matches while the price beats the one two rows ahead, so +-- company1 gives 200 on its own, since 150 only ties the 150 ahead of it, and +-- then 140, 150 up to where 90 falls short of 130. SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w, count(*) OVER w FROM stock @@ -1650,8 +1649,10 @@ ERROR: row pattern navigation offset must not be negative EXECUTE test_prev_offset(NULL); ERROR: row pattern navigation offset must not be null DEALLOCATE test_prev_offset; --- 2-arg PREV/NEXT: host variable with positive value --- Exercises RPR_NAV_OFFSET_NEEDS_EVAL -> eval_nav_max_offset() path +-- 2-arg PREV/NEXT: host variable with positive value. A generic plan keeps +-- the parameter as a Param, which is what reaches the RPR_NAV_OFFSET_NEEDS_EVAL +-- path; a custom plan would fold it to a Const and settle the reach at init. +SET plan_cache_mode = force_generic_plan; PREPARE test_prev_offset(int8) AS SELECT company, tdate, price, first_value(price) OVER w, count(*) OVER w FROM stock @@ -1713,6 +1714,7 @@ EXECUTE test_prev_offset(2); (20 rows) DEALLOCATE test_prev_offset; +RESET plan_cache_mode; -- 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, @@ -2326,6 +2328,120 @@ SELECT id, val, count(*) OVER w FROM rpr_nav WINDOW w AS ( DEFINE A AS NEXT(LAST(val), -1) IS NULL ); ERROR: row pattern navigation offset must not be negative +-- Compound: an out-of-range inner offset must not skip validation of the outer +-- one. All four arms resolve their outer offset through the same call, so each +-- appears once, and the negative and the null case take two arms apiece. +SELECT id, val, count(*) OVER w FROM rpr_nav WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE A AS TRUE, B AS PREV(FIRST(val, 99), -1) IS NULL +); +ERROR: row pattern navigation offset must not be negative +SELECT id, val, count(*) OVER w FROM rpr_nav WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE A AS TRUE, B AS PREV(LAST(val, 99), NULL::int8) IS NULL +); +ERROR: row pattern navigation offset must not be null +SELECT id, val, count(*) OVER w FROM rpr_nav WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE A AS TRUE, B AS NEXT(FIRST(val, 99), NULL::int8) IS NULL +); +ERROR: row pattern navigation offset must not be null +SELECT id, val, count(*) OVER w FROM rpr_nav WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE A AS TRUE, B AS NEXT(LAST(val, 99), -1) IS NULL +); +ERROR: row pattern navigation offset must not be negative +-- Same with a host variable, where the offset is not a Const the planner can +-- fold: one prepared statement, and only the outer offset decides the outcome. +-- The reach reads "runtime" here; a custom plan would fold it to 99 - 1 = 98. +SET plan_cache_mode = force_generic_plan; +PREPARE test_compound_illegal(int8, int8) AS +SELECT id, val, count(*) OVER w FROM rpr_nav WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE A AS TRUE, B AS PREV(FIRST(val, $1), $2) IS NULL +); +EXPLAIN (COSTS OFF) EXECUTE test_compound_illegal(99, 1); + QUERY PLAN +------------------------------------------------------------------------------- + WindowAgg + Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + Pattern: a b+ + Nav Mark Lookahead: runtime + -> Sort + Sort Key: id + -> Seq Scan on rpr_nav +(7 rows) + +EXECUTE test_compound_illegal(99, 1); + id | val | count +----+-----+------- + 1 | 10 | 6 + 2 | 20 | 0 + 3 | 30 | 0 + 4 | 10 | 0 + 5 | 50 | 0 + 6 | 10 | 0 +(6 rows) + +EXECUTE test_compound_illegal(99, -1); +ERROR: row pattern navigation offset must not be negative +EXECUTE test_compound_illegal(99, NULL); +ERROR: row pattern navigation offset must not be null +EXECUTE test_compound_illegal(0, -1); +ERROR: row pattern navigation offset must not be negative +DEALLOCATE test_compound_illegal; +RESET plan_cache_mode; +-- An offset is settled before the first row is fetched, so a partition with no +-- rows at all rejects an illegal one just the same, and a legal one returns no +-- rows rather than failing. +CREATE TABLE rpr_nav_empty (id int, val int); +SELECT id, count(*) OVER w FROM rpr_nav_empty WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS PREV(val, -1) IS NULL +); +ERROR: row pattern navigation offset must not be negative +SELECT id, count(*) OVER w FROM rpr_nav_empty WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS PREV(val, 1) IS NULL +); + id | count +----+------- +(0 rows) + +SET plan_cache_mode = force_generic_plan; +PREPARE test_empty_offset(int8) AS +SELECT id, count(*) OVER w FROM rpr_nav_empty WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS PREV(val, $1) IS NULL +); +EXECUTE test_empty_offset(-1); +ERROR: row pattern navigation offset must not be negative +EXECUTE test_empty_offset(NULL); +ERROR: row pattern navigation offset must not be null +EXECUTE test_empty_offset(1); + id | count +----+------- +(0 rows) + +DEALLOCATE test_empty_offset; +RESET plan_cache_mode; +DROP TABLE rpr_nav_empty; -- Outer offset overflows int64: target position out of range -> NULL. -- Plain NEXT(val, INT64_MAX): currentpos + INT64_MAX overflows. SELECT id, val, count(*) OVER w FROM rpr_nav WINDOW w AS ( diff --git a/src/test/regress/expected/rpr_base.out b/src/test/regress/expected/rpr_base.out index 6033272d1ce..554a77784fa 100644 --- a/src/test/regress/expected/rpr_base.out +++ b/src/test/regress/expected/rpr_base.out @@ -1743,6 +1743,57 @@ DETAIL: Pattern has 32768 elements, maximum is 32767. -- ============================================================ -- Navigation Functions Tests (PREV / NEXT / FIRST / LAST) -- ============================================================ +CREATE TEMP TABLE rpr_nav0 (id int, v int); +INSERT INTO rpr_nav0 SELECT g, g*10 FROM generate_series(1, 5) g; +-- Two concurrently open portals of the SAME cached generic plan, with different +-- offset parameters. +-- +-- The parameterized cursor 'c' compiles to one plpgsql statement -> one SPI +-- cached plan. The recursive call OPENs a second portal of that same plan +-- (with a different offset) while the outer portal is already started but has +-- not yet FETCHed. +CREATE OR REPLACE FUNCTION rpr_nested(p_off int, depth int) +RETURNS SETOF text LANGUAGE plpgsql AS $$ +DECLARE + c CURSOR (o int) FOR + SELECT id, count(*) OVER w AS cnt + FROM rpr_nav0 + WINDOW w AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A) + DEFINE A AS PREV(v, o) IS NULL); + r record; +BEGIN + OPEN c(p_off); + IF depth > 0 THEN + RETURN QUERY SELECT * FROM rpr_nested(p_off + 2, depth - 1); + END IF; + + LOOP + FETCH c INTO r; + EXIT WHEN NOT FOUND; + RETURN NEXT format('off=%s id=%s cnt=%s', p_off, r.id, r.cnt); + END LOOP; + CLOSE c; +END $$; +SET plan_cache_mode = force_generic_plan; +SELECT * FROM rpr_nested(1, 1); + rpr_nested +------------------ + off=3 id=1 cnt=1 + off=3 id=2 cnt=1 + off=3 id=3 cnt=1 + off=3 id=4 cnt=0 + off=3 id=5 cnt=0 + off=1 id=1 cnt=1 + off=1 id=2 cnt=0 + off=1 id=3 cnt=0 + off=1 id=4 cnt=0 + off=1 id=5 cnt=0 +(10 rows) + +RESET plan_cache_mode; +DROP FUNCTION rpr_nested(int, int); CREATE TABLE rpr_nav (id INT, val INT); INSERT INTO rpr_nav VALUES (1, 10), (2, 20), (3, 15), (4, 25), (5, 30); @@ -4511,11 +4562,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a{3} - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- Consecutive VAR merge: A{2} A{3} -> a{5} EXPLAIN (COSTS OFF) @@ -4527,11 +4577,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a{5} - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- Consecutive VAR merge: A+ A* -> a+ EXPLAIN (COSTS OFF) @@ -4543,11 +4592,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- Consecutive VAR merge: A A+ -> a{2,} -- where a finite prev (A{1,1}) meets an infinite child (A+). @@ -4560,11 +4608,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a{2,}" - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- Consecutive VAR merge at the boundary: A{1073741823,} A{1073741823,} -> -- a{2147483646,}. The min sum 2147483646 = INT32_MAX - 1 is the largest @@ -4579,11 +4626,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a{2147483646,}" - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- Consecutive GROUP merge with finite quantifiers: ((A B){5}) ((A B){10}) -> merged EXPLAIN (COSTS OFF) @@ -4595,11 +4641,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a b){15} - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- Consecutive GROUP merge with unbounded: (A B)+ (A B)+ -> (a b){2,} EXPLAIN (COSTS OFF) @@ -4611,11 +4656,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a' b'){2,}" - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- Consecutive GROUP merge: (A B){2} (A B)+ -> (a b){3,} -- Where a finite prev ((A B){2,2}) meets an infinite child ((A B)+). @@ -4628,11 +4672,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a' b'){3,}" - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- Consecutive GROUP merge at the boundary: (A B){1073741823,} (A B){1073741823,} -- -> (a b){2147483646,}. The min sum INT32_MAX - 1 is still finite, so the @@ -4647,11 +4690,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a' b'){2147483646,}" - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- PREFIX merge: A B (A B)+ -> (a b){2,} EXPLAIN (COSTS OFF) @@ -4663,11 +4705,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a' b'){2,}" - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- PREFIX and SUFFIX merge: A B (A B)+ A B -> (a b){3,} EXPLAIN (COSTS OFF) @@ -4679,11 +4720,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a' b'){3,}" - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- Flatten nested: A ((B) (C)) -> a b c EXPLAIN (COSTS OFF) @@ -4695,11 +4735,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a b c - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- Data execution: SEQ flatten produces correct results SELECT id, val, count(*) OVER w AS cnt @@ -4731,11 +4770,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a | b | c)+ - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- ALT deduplicate: (A | B | A) -> (a | b) EXPLAIN (COSTS OFF) @@ -4747,11 +4785,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a | b)+ - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- Data execution: ALT dedup produces correct results SELECT id, val, count(*) OVER w AS cnt @@ -4783,11 +4820,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a{6} - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- Quantifier NO multiply: reluctant GROUP child (((A B){2}?){3}) stays nested -- a reluctant quantifier on a GROUP is not subject to multiplication @@ -4800,11 +4836,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: ((a b){2}?){3} - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- Quantifier multiply control: greedy GROUP (((A B){2}){3}) -> (a b){6} EXPLAIN (COSTS OFF) @@ -4816,11 +4851,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a b){6} - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- Quantifier multiply with child range: (A{2,3}){3} -> a{6,9} -- outer exact, child range - optimization applies @@ -4833,11 +4867,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a{6,9} - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- Quantifier NO multiply: (A{2}){2,3} stays as (a{2}){2,3} -- outer range - gaps would occur (4,6 not 4,5,6), no optimization @@ -4850,11 +4883,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a{2}){2,3} - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- Quantifier NO multiply: (A{2}){2,} stays as (a{2}){2,} -- outer unbounded - gaps would occur (4,6,8,... not 4,5,6,...), no optimization @@ -4867,11 +4899,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a{2}'){2,}" - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- Quantifier multiply: (A){2,} -> a{2,} -- child exact 1 - no gaps, optimization applies @@ -4884,11 +4915,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a{2,}" - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- Quantifier multiply: (A)+ -> a+ -- child exact 1 - no gaps, optimization applies @@ -4901,11 +4931,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- Quantifier NO multiply: (A{2}){3,5} stays as (a{2}){3,5} -- outer range, child exact > 1 - gaps would occur (6,8,10 not 6,7,8,9,10) @@ -4918,11 +4947,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a{2}){3,5} - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- Quantifier multiply refused: (A{2,3}){2,3} stays nested. -- The counts [4,6] U [6,9] = [4,9] are contiguous, but a bounded child with a @@ -4937,11 +4965,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a{2,3}){2,3} - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- Quantifier NO multiply: (A{4,5}){2,3} stays as (a{4,5}){2,3} -- outer range, child range with a gap: [8,10] U [12,15] misses 11 @@ -4954,11 +4981,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a{4,5}){2,3} - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- Nested unbounded: (A*)* -> a* EXPLAIN (COSTS OFF) @@ -4970,11 +4996,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a*" - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- Nested unbounded: (A+)* -> a* EXPLAIN (COSTS OFF) @@ -4986,11 +5011,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a*" - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- Nested unbounded: (A+)+ -> a+ EXPLAIN (COSTS OFF) @@ -5002,11 +5026,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- Quantifier multiply with an unbounded child: an exact outer count (m == n) -- always folds regardless of the child's max - (A+){3} -> a{3,} @@ -5019,11 +5042,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a{3,}" - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- (A{2,}){3} -> a{6,} (m == n, unbounded child with min 2) EXPLAIN (COSTS OFF) @@ -5035,11 +5057,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a{6,}" - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- (A+){2,4} -> a{2,} (outer range, unbounded child: every interval reaches INF, -- so they always touch) @@ -5052,11 +5073,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a{2,}" - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- (A{2,3}){2,4} stays nested for the same reason, even though the counts -- [4,6] U [6,9] U [8,12] = [4,12] are contiguous. @@ -5069,11 +5089,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a{2,3}){2,4} - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- Skippable outer (min 0) folds only when the zero case connects to the child -- range: (A{1,3})? -> a{0,3} (child min <= 1, so {0} U [1,3] = [0,3] is contiguous) @@ -5086,11 +5105,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a{0,3} - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- Quantifier NO multiply: (A{2,3})? stays as (a{2,3})? -- min 0 with child min >= 2: {0} U [2,3] leaves 1 unreachable (intervals touch but @@ -5104,11 +5122,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a{2,3})? - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- Quantifier NO multiply: (A{3,4})? stays as (a{3,4})? -- min 0 with child min >= 2: {0} U [3,4] leaves 1,2 unreachable @@ -5121,11 +5138,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a{3,4})? - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- Unwrap GROUP{1,1}: (A) -> a EXPLAIN (COSTS OFF) @@ -5137,11 +5153,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- Unwrap GROUP{1,1}: (A B) -> a b EXPLAIN (COSTS OFF) @@ -5153,11 +5168,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a b - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- Combined optimization: A A (B B)+ B B C C C -> a{2} (b{2}){2,} c{3} EXPLAIN (COSTS OFF) @@ -5170,11 +5184,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a{2} (b{2}){2,} c{3} - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- Consecutive GROUP merge with unbounded: (A+) (A+) -> a{2,} EXPLAIN (COSTS OFF) @@ -5186,11 +5199,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a{2,}" - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- Consecutive GROUP merge finite: (A{10}){20} -> a{200} EXPLAIN (COSTS OFF) @@ -5202,11 +5214,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a{200} - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- Different GROUP prevents merge: (A B){2} (C D){3} EXPLAIN (COSTS OFF) @@ -5220,11 +5231,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a b){2} (c d){3} - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- Different children count prevents merge: (A B)+ (A B C)+ EXPLAIN (COSTS OFF) @@ -5237,11 +5247,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a' b')+" (a b c)+ - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- PREFIX only merge: A B (A B)+ -> (a b){2,} EXPLAIN (COSTS OFF) @@ -5253,11 +5262,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a' b'){2,}" - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- SUFFIX only merge: (A B)+ A B -> (a b){2,} EXPLAIN (COSTS OFF) @@ -5269,11 +5277,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a' b'){2,}" - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- Multiple SUFFIX absorption with skipUntil: (A B)+ A B A B C EXPLAIN (COSTS OFF) @@ -5286,11 +5293,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a' b'){3,}" c - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- PREFIX merge with remaining prefix: A B C D (C D)+ -> A B (C D) {2,} EXPLAIN (COSTS OFF) @@ -5304,11 +5310,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a b (c d){2,} - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- cannot merge, prefix is different EXPLAIN (COSTS OFF) @@ -5323,11 +5328,10 @@ DEFINE A AS val <= 25, B AS val > 25, WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a b c d c (c d)+ - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- PREFIX merge with quantifiers: A B* (A B*)+ -> (a b*){2,} EXPLAIN (COSTS OFF) @@ -5340,11 +5344,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a b*){2,} - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- PREFIX merge with multiple quantifiers: A+ B* C? (A+ B* C?)+ -> (a+ b* c?){2,} EXPLAIN (COSTS OFF) @@ -5357,11 +5360,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a+" b* c?){2,} - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- SUFFIX merge refused: (A B*)+ A B* stays as written. The body A B* has no -- fixed row count, so folding the trailing copy into the group would move the @@ -5378,11 +5380,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a b*)+ a b* - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- Unwrap GROUP{1,1}: ((A | B | C)) -> (a | b | c) EXPLAIN (COSTS OFF) @@ -5394,11 +5395,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a | b | c) - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- Data execution: GROUP unwrap produces correct results SELECT id, val, count(*) OVER w AS cnt @@ -5431,11 +5431,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+? a - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- Reluctant optimization bypass: GROUP merge -- (A B)+? (A B) stays separate (greedy merges to (a b){2,}) @@ -5448,11 +5447,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a b)+? a b - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- Reluctant optimization bypass: quantifier multiply (outer reluctant) -- (A{2}){3}? stays as (a{2}){3}? (greedy merges to a{6}) @@ -5465,11 +5463,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a{2}){3}? - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- Reluctant optimization bypass: quantifier multiply (inner reluctant) -- (A{2}?){3} stays as (a{2}?){3} (greedy merges to a{6}) @@ -5482,11 +5479,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a{2}?){3} - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- Reluctant optimization bypass: PREFIX merge -- A B (A B)+? stays separate (greedy merges to (a b){2,}) @@ -5499,11 +5495,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a b (a b)+? - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- Reluctant optimization bypass: SUFFIX merge -- (A B)+? A B stays separate (greedy merges to (a b){2,}) @@ -5516,11 +5511,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a b)+? a b - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- GROUP unwrap with quantifier propagation: (A)?? B -> a?? b -- Single VAR child {1,1} receives GROUP's quantifier and reluctant @@ -5533,11 +5527,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a?? b - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- Reluctant preserved through ALT flatten -- (A | (B | C))+? flattens to (a | b | c)+? - inner ALT flattened, reluctant kept @@ -5550,11 +5543,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a | b | c)+? - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- Reluctant optimization bypass: absorption flags -- A+? with SKIP PAST LAST ROW - no absorption markers (greedy A+ gets a+") @@ -5567,11 +5559,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+? - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- Duplicate GROUP removal: ((A | B)+ | (A | B)+) -> (a | b)+ EXPLAIN (COSTS OFF) @@ -5583,11 +5574,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a | b)+ - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- Consecutive VAR merge with zero-min: A* A+ -> a+ EXPLAIN (COSTS OFF) @@ -5599,11 +5589,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- Consecutive VAR merge (4-element): A A{2} A+ A{3} -> a{7,} EXPLAIN (COSTS OFF) @@ -5615,11 +5604,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a{7,}" - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- PREFIX+SUFFIX merge (5-way): A B A B (A B)+ A B A B -> (a b){5,} EXPLAIN (COSTS OFF) @@ -5632,11 +5620,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a' b'){5,}" - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- PREFIX+SUFFIX merge (5-way): B A B (A B)+ A B A B -> b (a b){4,} EXPLAIN (COSTS OFF) @@ -5649,11 +5636,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: b (a b){4,} - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- Unwrap single-item ALT after dedup: (A | A)+ -> a+ -- ALT dedup reduces to single-item, then GROUP unwrap @@ -5666,11 +5652,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- GROUP{1,1} to SEQ with flatten: ((A B)(C D)) -> a b c d EXPLAIN (COSTS OFF) @@ -5684,11 +5669,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a b c d - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- Nested ALT pattern: ((A B) | C) D | A B C EXPLAIN (COSTS OFF) @@ -5702,11 +5686,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: ((a b | c) d | a b c) - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- Nested ALT with unbounded: ((A+ B) | C) D | A B C EXPLAIN (COSTS OFF) @@ -5720,11 +5703,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: ((a+" b | c) d | a b c) - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- ============================================================ -- Absorption Flag Display Tests @@ -5742,11 +5724,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- GROUP unbounded: (A B)+ -> (a' b')+" (branch + comparison) EXPLAIN (COSTS OFF) @@ -5758,11 +5739,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a' b')+" - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- ALT both absorbable: A+ | B+ -> (a+" | b+") EXPLAIN (COSTS OFF) @@ -5774,11 +5754,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a+" | b+") - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- ALT one absorbable: A+ | B -> (a+" | b) EXPLAIN (COSTS OFF) @@ -5790,11 +5769,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a+" | b) - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- Sequence with absorbable start: A+ B -> a+" b EXPLAIN (COSTS OFF) @@ -5806,11 +5784,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" b - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- Complex nested: ((A+ B) | C) D | A B C - deeply nested ALT EXPLAIN (COSTS OFF) @@ -5823,11 +5800,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: ((a+" b | c) d | a b c) - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- ALT branch tail not over-marked: A | (B C)+ (D E)+ -> (a | (b' c')+" (d e)+) EXPLAIN (COSTS OFF) @@ -5840,11 +5816,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a | (b' c')+" (d e)+) - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- Nested unbounded: (A+ | B)+ -> (a+" | b)+ (first iteration absorbable) EXPLAIN (COSTS OFF) @@ -5857,11 +5832,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a+" | b)+ - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- ALT inside unbounded GROUP: (A+ B | A B)* -> (a+" b | a b)* (first iteration absorbable) EXPLAIN (COSTS OFF) @@ -5874,11 +5848,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a+" b | a b)* - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- Fixed-length group absorbable: (A{2} B{3})+ -> (a{2}' b{3}'){2,}" -- All children have min == max, equivalent to unrolling to {1,1} @@ -5892,11 +5865,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a{2}' b{3}')+" - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- Nested fixed-length group: (A (B C){2} D)+ -> absorbable EXPLAIN (COSTS OFF) @@ -5909,11 +5881,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a' (b' c'){2}' d')+" - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- Nested fixed-length with inner quantifier: ((A{2} B{3}){2})+ -> absorbable EXPLAIN (COSTS OFF) @@ -5926,11 +5897,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: ((a{2}' b{3}'){2}')+" - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- Non-absorbable fixed-length: (A B{2,5})+ -> no markers (min != max) EXPLAIN (COSTS OFF) @@ -5943,11 +5913,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a b{2,5})+ - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- Non-absorbable fixed-length: (A B?)+ -> no markers (min != max) EXPLAIN (COSTS OFF) @@ -5960,11 +5929,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a b?)+ - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- Non-absorbable (unbounded not at start): A B+ -> a b+ (no markers) EXPLAIN (COSTS OFF) @@ -5976,11 +5944,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a b+ - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- Non-absorbable (no unbounded branch): (A | B){2,} -> (a | b){2,} (no markers) EXPLAIN (COSTS OFF) @@ -5992,11 +5959,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a | b){2,} - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- Non-absorbable (SKIP TO NEXT ROW): A+ -> a+ (no markers) EXPLAIN (COSTS OFF) @@ -6008,11 +5974,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+ - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- Non-absorbable (limited frame): A+ -> a+ (no markers) EXPLAIN (COSTS OFF) @@ -6024,11 +5989,10 @@ WINDOW w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND 10 FOLLOWING WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND '10'::bigint FOLLOWING) Pattern: a+ - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- Reluctant {1}? quantifier deparse -- A{1}? is a reluctant {1,1} quantifier. The deparse code must @@ -6047,11 +6011,10 @@ WINDOW w AS ( WindowAgg Window: w AS (ORDER BY val ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a{1}? b - Nav Mark Lookback: 0 -> Sort Sort Key: val -> Seq Scan on rpr_plan -(7 rows) +(6 rows) -- ============================================================ -- Absorption Analysis Tests @@ -6626,11 +6589,10 @@ WINDOW w AS ( WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a{2000000000}){2} - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_fallback -(7 rows) +(6 rows) -- Expected: Fallback - pattern not merged due to min overflow (4000000000 > INT32_MAX) -- Test: max-only quantifier overflow causes optimization fallback @@ -6647,11 +6609,10 @@ WINDOW w AS ( WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a{1,2000000000}){2} - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_fallback -(7 rows) +(6 rows) -- Expected: Fallback - min OK (2*1=2), but max overflow (2*2000000000 > INT32_MAX) -- Test: max quantifier exceeds valid range (2147483647 = INT_MAX, limit is 2147483646) @@ -6680,11 +6641,10 @@ WINDOW w AS ( WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a{2000000000,}"){2000000000,} - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_fallback -(7 rows) +(6 rows) -- Expected: Fallback - min overflow (2000000000 * 2000000000 > INT32_MAX) -- Test: prefix mismatch causes optimization fallback @@ -6701,11 +6661,10 @@ WINDOW w AS ( WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a b (c d)+ - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_fallback -(7 rows) +(6 rows) -- Expected: Fallback - prefix elements don't match GROUP content -- Test: consecutive VAR merge whose min sum is exactly INF causes fallback. @@ -6725,11 +6684,10 @@ WINDOW w AS ( WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a{1073741824,}" a{1073741823,} - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_fallback -(7 rows) +(6 rows) -- Expected: Fallback - VARs not merged (min sum 2147483647 == INF) -- Test: consecutive GROUP merge whose min sum is exactly INF causes fallback. @@ -6746,11 +6704,10 @@ WINDOW w AS ( WindowAgg Window: w AS (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a' b'){1073741824,}" (a b){1073741823,} - Nav Mark Lookback: 0 -> Sort Sort Key: id -> Seq Scan on rpr_fallback -(7 rows) +(6 rows) -- Expected: Fallback - GROUPs not merged (min sum 2147483647 == INF) DROP TABLE rpr_fallback; @@ -7486,7 +7443,7 @@ DROP TABLE rpr_sort; -- substitute_actual_parameters_in_from via query_tree_mutator. CREATE TABLE rpr_srf_t (v int); INSERT INTO rpr_srf_t SELECT generate_series(1, 5); -CREATE FUNCTION rpr_srf_f(threshold int) +CREATE FUNCTION rpr_srf_inline(threshold int) RETURNS TABLE (v int, cnt bigint) LANGUAGE sql STABLE AS $$ SELECT v::int, count(*) OVER w @@ -7498,7 +7455,7 @@ LANGUAGE sql STABLE AS $$ DEFINE A AS v > $1 ) $$; -SELECT v, cnt FROM rpr_srf_f(3) ORDER BY v; +SELECT v, cnt FROM rpr_srf_inline(3) ORDER BY v; v | cnt ---+----- 1 | 0 @@ -7509,7 +7466,7 @@ SELECT v, cnt FROM rpr_srf_f(3) ORDER BY v; (5 rows) DROP TABLE rpr_srf_t; -DROP FUNCTION rpr_srf_f(int); +DROP FUNCTION rpr_srf_inline(int); DROP TABLE rpr_planner; -- ============================================================ -- Stress Tests diff --git a/src/test/regress/expected/rpr_explain.out b/src/test/regress/expected/rpr_explain.out index 78f36442283..19f23cef2e1 100644 --- a/src/test/regress/expected/rpr_explain.out +++ b/src/test/regress/expected/rpr_explain.out @@ -142,14 +142,13 @@ WINDOW w AS ( WindowAgg (actual rows=100.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a b - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 2 peak, 101 total, 0 merged NFA Contexts: 2 peak, 101 total, 60 pruned NFA: 20 matched (len 2/2/2.0), 0 mismatched NFA: 0 absorbed, 20 skipped (len 1/1/1.0) -> Seq Scan on rpr_nfa_test (actual rows=100.00 loops=1) -(10 rows) +(9 rows) -- Pattern with no matches - 0 matched CREATE VIEW rpr_ev_basic_nomatch AS @@ -182,13 +181,12 @@ WINDOW w AS ( WindowAgg (actual rows=100.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: x y z - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 1 peak, 101 total, 0 merged NFA Contexts: 2 peak, 101 total, 100 pruned NFA: 0 matched, 0 mismatched -> Seq Scan on rpr_nfa_test (actual rows=100.00 loops=1) -(9 rows) +(8 rows) -- Pattern matching every row - high match count CREATE VIEW rpr_ev_basic_allrows AS @@ -221,13 +219,12 @@ WINDOW w AS ( WindowAgg (actual rows=100.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: r - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 2 peak, 101 total, 0 merged NFA Contexts: 2 peak, 101 total, 0 pruned NFA: 100 matched (len 1/1/1.0), 0 mismatched -> Seq Scan on rpr_nfa_test (actual rows=100.00 loops=1) -(9 rows) +(8 rows) -- Regression test: Space before parenthesis in pattern deparse -- Verifies that "A (B | C)" correctly outputs as "a (b | c)" with space @@ -259,14 +256,13 @@ WINDOW w AS ( WindowAgg (actual rows=20.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a (b | c) - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 35 total, 0 merged NFA Contexts: 2 peak, 21 total, 6 pruned NFA: 7 matched (len 2/2/2.0), 0 mismatched NFA: 0 absorbed, 7 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=20.00 loops=1) -(10 rows) +(9 rows) -- Regression test: Sequential alternations at same depth -- Verifies that "((B | C) (D | E))" correctly outputs as "(b | c) (d | e)" @@ -299,13 +295,12 @@ WINDOW w AS ( WindowAgg (actual rows=30.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a ((b | c) (d | e))* - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 4 peak, 61 total, 0 merged NFA Contexts: 3 peak, 31 total, 24 pruned NFA: 6 matched (len 1/1/1.0), 0 mismatched -> Function Scan on generate_series s (actual rows=30.00 loops=1) -(9 rows) +(8 rows) -- Regression test: ALT branch whose tail is a group -- (A | (B C)+ (D E)+) means A | ((B C)+ (D E)+) by precedence, so the two @@ -340,14 +335,13 @@ WINDOW w AS ( WindowAgg (actual rows=30.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a | (b' c')+" (d e)+) - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 5 peak, 103 total, 0 merged NFA Contexts: 3 peak, 31 total, 15 pruned NFA: 10 matched (len 1/4/2.5), 0 mismatched NFA: 0 absorbed, 5 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=30.00 loops=1) -(10 rows) +(9 rows) -- Regression test: Quoted identifiers in EXPLAIN pattern deparse -- Mixed case names must be quoted to preserve round-trip safety @@ -403,13 +397,12 @@ WINDOW w AS ( WindowAgg (actual rows=50.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 76 total, 0 merged NFA Contexts: 3 peak, 51 total, 25 pruned NFA: 25 matched (len 1/1/1.0), 0 mismatched -> Function Scan on generate_series s (actual rows=50.00 loops=1) -(9 rows) +(8 rows) -- Alternation pattern - multiple state branches CREATE VIEW rpr_ev_state_alt AS @@ -446,14 +439,13 @@ WINDOW w AS ( WindowAgg (actual rows=100.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a | b | c) (d | e) - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 6 peak, 524 total, 0 merged NFA Contexts: 3 peak, 101 total, 20 pruned NFA: 20 matched (len 2/2/2.0), 40 mismatched (len 2/2/2.0) NFA: 0 absorbed, 20 skipped (len 1/1/1.0) -> Seq Scan on rpr_nfa_test (actual rows=100.00 loops=1) -(10 rows) +(9 rows) -- Complex pattern with high state count CREATE VIEW rpr_ev_state_complex AS @@ -492,14 +484,13 @@ WINDOW w AS ( WindowAgg (actual rows=100.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" b* c+ - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 5 peak, 235 total, 0 merged NFA Contexts: 3 peak, 101 total, 34 pruned NFA: 33 matched (len 3/3/3.0), 0 mismatched NFA: 0 absorbed, 33 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=100.00 loops=1) -(10 rows) +(9 rows) -- Grouped pattern with quantifier - state count with grouping CREATE VIEW rpr_ev_state_group_quant AS @@ -532,14 +523,13 @@ WINDOW w AS ( WindowAgg (actual rows=60.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a' b')+" - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 4 peak, 91 total, 0 merged NFA Contexts: 3 peak, 61 total, 0 pruned NFA: 1 matched (len 60/60/60.0), 0 mismatched NFA: 29 absorbed (len 2/2/2.0), 30 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=60.00 loops=1) -(10 rows) +(9 rows) -- State explosion pattern - many alternations -- Pattern (A|B)(A|B)(A|B)(A|B) can create many parallel states @@ -573,14 +563,13 @@ WINDOW w AS ( WindowAgg (actual rows=100.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a | b){8} - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 17 peak, 995 total, 0 merged NFA Contexts: 8 peak, 101 total, 1 pruned NFA: 12 matched (len 8/8/8.0), 3 mismatched (len 2/4/3.0) NFA: 0 absorbed, 84 skipped (len 1/7/4.0) -> Function Scan on generate_series s (actual rows=100.00 loops=1) -(10 rows) +(9 rows) -- Consecutive ALT merge followed by different ALT -- ((A | B) (A | B) (C | D)) -> (A|B){2} (C|D) @@ -614,14 +603,13 @@ WINDOW w AS ( WindowAgg (actual rows=40.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a | b){2} (c | d) - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 7 peak, 181 total, 0 merged NFA Contexts: 3 peak, 41 total, 12 pruned NFA: 9 matched (len 3/3/3.0), 1 mismatched (len 2/2/2.0) NFA: 0 absorbed, 18 skipped (len 1/2/1.5) -> Function Scan on generate_series s (actual rows=40.00 loops=1) -(10 rows) +(9 rows) -- Consecutive ALT merge followed by non-ALT element -- ((A | B) (A | B) C) -> (A|B){2} C @@ -655,14 +643,13 @@ WINDOW w AS ( WindowAgg (actual rows=40.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a | b){2} c - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 6 peak, 177 total, 0 merged NFA Contexts: 3 peak, 41 total, 2 pruned NFA: 12 matched (len 3/3/3.0), 2 mismatched (len 2/2/2.0) NFA: 0 absorbed, 24 skipped (len 1/2/1.5) -> Function Scan on generate_series s (actual rows=40.00 loops=1) -(10 rows) +(9 rows) -- ALT prefix/suffix absorbed into GROUP: (A|B) (A|B)+ (A|B) -> (A|B){3,} CREATE VIEW rpr_ev_state_alt_absorb_group AS @@ -695,14 +682,13 @@ WINDOW w AS ( WindowAgg (actual rows=40.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a | b){3,} - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 7 peak, 243 total, 0 merged NFA Contexts: 3 peak, 41 total, 0 pruned NFA: 1 matched (len 40/40/40.0), 0 mismatched NFA: 0 absorbed, 39 skipped (len 1/2/1.0) -> Function Scan on generate_series s (actual rows=40.00 loops=1) -(10 rows) +(9 rows) -- High state count - alternation with plus quantifier CREATE VIEW rpr_ev_state_alt_plus AS @@ -735,14 +721,13 @@ WINDOW w AS ( WindowAgg (actual rows=100.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a | b | c)+ d - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 16 peak, 1004 total, 0 merged NFA Contexts: 4 peak, 101 total, 0 pruned NFA: 25 matched (len 4/4/4.0), 0 mismatched NFA: 0 absorbed, 75 skipped (len 1/3/2.0) -> Function Scan on generate_series s (actual rows=100.00 loops=1) -(10 rows) +(9 rows) -- Early termination: first ALT branch (A) reaches FIN immediately, -- pruning second branch (A B+) before it can accumulate B repetitions. @@ -776,13 +761,12 @@ WINDOW w AS ( WindowAgg (actual rows=100.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a | a b)+ - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 6 peak, 306 total, 0 merged NFA Contexts: 3 peak, 101 total, 99 pruned NFA: 1 matched (len 1/1/1.0), 0 mismatched -> Function Scan on generate_series s (actual rows=100.00 loops=1) -(9 rows) +(8 rows) -- Nested quantifiers causing state growth CREATE VIEW rpr_ev_state_nested_quant AS @@ -815,14 +799,13 @@ WINDOW w AS ( WindowAgg (actual rows=1000.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a | b)+ - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 6 peak, 5004 total, 0 merged NFA Contexts: 3 peak, 1001 total, 333 pruned NFA: 334 matched (len 1/2/2.0), 0 mismatched NFA: 0 absorbed, 333 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=1000.00 loops=1) -(10 rows) +(9 rows) -- (A{2,})* must NOT flatten to a* (H-1): counts {0} UNION [2, INF) leave 1 -- unreachable. The planner keeps it as (a{2,})*, not a*. @@ -856,14 +839,13 @@ WINDOW w AS ( WindowAgg (actual rows=6.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a{2,}")* - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 6 peak, 18 total, 0 merged NFA Contexts: 3 peak, 7 total, 0 pruned NFA: 4 matched (len 0/2/1.0), 0 mismatched NFA: 0 absorbed, 2 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=6.00 loops=1) -(10 rows) +(9 rows) -- Overlapping DEFINEs let two branches reach the same state, so this is the -- only case here with a nonzero merged count; the alt_merge names above mean @@ -898,14 +880,13 @@ WINDOW w AS ( WindowAgg (actual rows=40.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a | b){2,4} - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 6 peak, 201 total, 14 merged NFA Contexts: 3 peak, 41 total, 14 pruned NFA: 6 matched (len 3/3/3.0), 8 mismatched (len 2/2/2.0) NFA: 0 absorbed, 12 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=40.00 loops=1) -(10 rows) +(9 rows) -- ============================================================ -- Context Statistics Tests (peak, total, pruned + absorbed/skipped) @@ -941,14 +922,13 @@ WINDOW w AS ( WindowAgg (actual rows=50.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" b - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 91 total, 0 merged NFA Contexts: 2 peak, 51 total, 0 pruned NFA: 10 matched (len 5/5/5.0), 0 mismatched NFA: 30 absorbed (len 1/1/1.0), 10 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=50.00 loops=1) -(10 rows) +(9 rows) -- Bare unbounded quantifier: A+ absorbs redundant contexts -- min=1 commits no match until the run ends, so newer contexts absorb in-progress @@ -982,14 +962,13 @@ WINDOW w AS ( WindowAgg (actual rows=10.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 21 total, 0 merged NFA Contexts: 2 peak, 11 total, 0 pruned NFA: 1 matched (len 10/10/10.0), 0 mismatched NFA: 9 absorbed (len 1/1/1.0), 0 skipped -> Function Scan on generate_series s (actual rows=10.00 loops=1) -(10 rows) +(9 rows) -- Bare min=0 quantifier: A* is skipped, not absorbed -- min=0 commits an empty match at creation, so SKIP (not absorption) removes them @@ -1023,14 +1002,13 @@ WINDOW w AS ( WindowAgg (actual rows=10.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a*" - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 5 peak, 32 total, 0 merged NFA Contexts: 2 peak, 11 total, 0 pruned NFA: 1 matched (len 10/10/10.0), 0 mismatched NFA: 0 absorbed, 9 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=10.00 loops=1) -(10 rows) +(9 rows) -- No absorption - bounded quantifier CREATE VIEW rpr_ev_ctx_no_absorb AS @@ -1063,14 +1041,13 @@ WINDOW w AS ( WindowAgg (actual rows=50.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a{2,4} b - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 7 peak, 101 total, 0 merged NFA Contexts: 5 peak, 51 total, 0 pruned NFA: 10 matched (len 5/5/5.0), 0 mismatched NFA: 0 absorbed, 40 skipped (len 1/4/2.5) -> Function Scan on generate_series s (actual rows=50.00 loops=1) -(10 rows) +(9 rows) -- Contexts skipped by SKIP PAST LAST ROW CREATE VIEW rpr_ev_ctx_skip AS @@ -1103,14 +1080,13 @@ WINDOW w AS ( WindowAgg (actual rows=100.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a b c - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 2 peak, 101 total, 0 merged NFA Contexts: 3 peak, 101 total, 80 pruned NFA: 10 matched (len 3/3/3.0), 0 mismatched NFA: 0 absorbed, 10 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=100.00 loops=1) -(10 rows) +(9 rows) -- High context absorption - unbounded group CREATE VIEW rpr_ev_ctx_absorb_group AS @@ -1143,14 +1119,13 @@ WINDOW w AS ( WindowAgg (actual rows=100.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a' b')+" c - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 134 total, 0 merged NFA Contexts: 3 peak, 101 total, 34 pruned NFA: 33 matched (len 3/3/3.0), 0 mismatched NFA: 0 absorbed, 33 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=100.00 loops=1) -(10 rows) +(9 rows) -- Fixed-length group absorption: (A B B)+ C -- B B merged to B{2}; absorbable with fixed-length check @@ -1185,14 +1160,13 @@ WINDOW w AS ( WindowAgg (actual rows=70.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a' b{2}')+" c - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 91 total, 0 merged NFA Contexts: 4 peak, 71 total, 40 pruned NFA: 10 matched (len 7/7/7.0), 0 mismatched NFA: 10 absorbed (len 3/3/3.0), 10 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=70.00 loops=1) -(10 rows) +(9 rows) -- Nested fixed-length group absorption: (A (B C){2} D)+ E -- step_size = 1 + (1+1)*2 + 1 = 6; v % 13 cycle gives 2 iterations + E @@ -1230,14 +1204,13 @@ WINDOW w AS ( WindowAgg (actual rows=65.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a' (b' c'){2}' d')+" e - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 76 total, 0 merged NFA Contexts: 4 peak, 66 total, 50 pruned NFA: 5 matched (len 13/13/13.0), 0 mismatched NFA: 5 absorbed (len 6/6/6.0), 5 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=65.00 loops=1) -(10 rows) +(9 rows) -- Doubly nested fixed-length group absorption: (A ((B C{3}){2} D){2} E)+ F -- step_size = 1 + ((1+3)*2+1)*2 + 1 = 20; v % 41 cycle gives 2 iterations + F @@ -1283,14 +1256,13 @@ WINDOW w AS ( WindowAgg (actual rows=82.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a' ((b' c{3}'){2}' d'){2}' e')+" f - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 87 total, 0 merged NFA Contexts: 4 peak, 83 total, 76 pruned NFA: 2 matched (len 41/41/41.0), 0 mismatched NFA: 2 absorbed (len 20/20/20.0), 2 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=82.00 loops=1) -(10 rows) +(9 rows) -- 3-level END chain absorption: ((A (B C){2}){2})+ -- step_size = (1 + (1+1)*2) * 2 = 10; v % 21 cycle gives 2 iterations @@ -1329,14 +1301,13 @@ WINDOW w AS ( WindowAgg (actual rows=42.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: ((a' (b' c'){2}'){2}')+" - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 5 peak, 47 total, 0 merged NFA Contexts: 5 peak, 43 total, 30 pruned NFA: 2 matched (len 20/20/20.0), 0 mismatched NFA: 2 absorbed (len 10/10/10.0), 8 skipped (len 1/5/3.0) -> Function Scan on generate_series s (actual rows=42.00 loops=1) -(10 rows) +(9 rows) -- No absorption when DEFINE uses FIRST (match_start-dependent) -- Same pattern as rpr_ev_ctx_absorb_unbounded but with FIRST in DEFINE. @@ -1371,7 +1342,6 @@ WINDOW w AS ( WindowAgg (actual rows=50.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+ b - Nav Mark Lookback: 0 Nav Mark Lookahead: 0 Storage: Memory Maximum Storage: NkB NFA States: 9 peak, 151 total, 0 merged @@ -1379,7 +1349,7 @@ WINDOW w AS ( NFA: 10 matched (len 5/5/5.0), 0 mismatched NFA: 0 absorbed, 40 skipped (len 1/4/2.5) -> Function Scan on generate_series s (actual rows=50.00 loops=1) -(11 rows) +(10 rows) -- Absorption preserved when DEFINE uses only LAST without offset -- LAST(v) is match_start-independent (always currentpos), so absorption @@ -1449,7 +1419,6 @@ WINDOW w AS ( WindowAgg (actual rows=50.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+ b - Nav Mark Lookback: 0 Nav Mark Lookahead: -1 Storage: Memory Maximum Storage: NkB NFA States: 9 peak, 151 total, 0 merged @@ -1457,7 +1426,7 @@ WINDOW w AS ( NFA: 10 matched (len 4/5/4.9), 1 mismatched (len 5/5/5.0) NFA: 0 absorbed, 39 skipped (len 1/4/2.5) -> Function Scan on generate_series s (actual rows=50.00 loops=1) -(11 rows) +(10 rows) -- Alternation, non-absorbable branch match survives absorption: A+ B | C -- The dominating A+ run absorbs redundant contexts, but the recorded C matches @@ -1500,7 +1469,6 @@ WINDOW w AS ( WindowAgg (actual rows=6.00 loops=1) Window: w AS (ORDER BY "*VALUES*".column1 ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a+" b | c) - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 11 peak, 34 total, 0 merged NFA Contexts: 4 peak, 7 total, 0 pruned @@ -1510,7 +1478,7 @@ WINDOW w AS ( Sort Key: "*VALUES*".column1 Sort Method: quicksort Memory: NkB -> Values Scan on "*VALUES*" (actual rows=6.00 loops=1) -(13 rows) +(12 rows) -- Alternation, both branches absorbable: A+ C | B+ -- A+ C never completes (C absent) so its A+ run absorbs redundant contexts; the @@ -1555,7 +1523,6 @@ WINDOW w AS ( WindowAgg (actual rows=9.00 loops=1) Window: w AS (ORDER BY "*VALUES*".column1 ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a+" c | b+") - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 10 peak, 49 total, 0 merged NFA Contexts: 3 peak, 10 total, 0 pruned @@ -1565,7 +1532,7 @@ WINDOW w AS ( Sort Key: "*VALUES*".column1 Sort Method: quicksort Memory: NkB -> Values Scan on "*VALUES*" (actual rows=9.00 loops=1) -(13 rows) +(12 rows) -- ============================================================ -- Match Length Statistics Tests @@ -1605,14 +1572,13 @@ WINDOW w AS ( WindowAgg (actual rows=100.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a b c d e - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 2 peak, 101 total, 0 merged NFA Contexts: 3 peak, 101 total, 60 pruned NFA: 20 matched (len 5/5/5.0), 0 mismatched NFA: 0 absorbed, 20 skipped (len 1/1/1.0) -> Seq Scan on rpr_nfa_test (actual rows=100.00 loops=1) -(10 rows) +(9 rows) -- Variable length matches - min/max/avg differ CREATE VIEW rpr_ev_mlen_variable AS @@ -1645,14 +1611,13 @@ WINDOW w AS ( WindowAgg (actual rows=100.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" b - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 191 total, 0 merged NFA Contexts: 2 peak, 101 total, 0 pruned NFA: 10 matched (len 10/10/10.0), 0 mismatched NFA: 80 absorbed (len 1/1/1.0), 10 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=100.00 loops=1) -(10 rows) +(9 rows) -- Very long matches CREATE VIEW rpr_ev_mlen_long AS @@ -1685,14 +1650,13 @@ WINDOW w AS ( WindowAgg (actual rows=200.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" b - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 396 total, 0 merged NFA Contexts: 2 peak, 201 total, 4 pruned NFA: 1 matched (len 196/196/196.0), 0 mismatched NFA: 194 absorbed (len 1/1/1.0), 1 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=200.00 loops=1) -(10 rows) +(9 rows) -- Uniform match length with mismatches from gap rows (v%20 = 11..15) CREATE VIEW rpr_ev_mlen_with_mismatch AS @@ -1729,14 +1693,13 @@ WINDOW w AS ( WindowAgg (actual rows=100.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" b - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 171 total, 0 merged NFA Contexts: 3 peak, 101 total, 25 pruned NFA: 5 matched (len 5/5/5.0), 5 mismatched (len 11/11/11.0) NFA: 60 absorbed (len 1/1/1.0), 5 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=100.00 loops=1) -(10 rows) +(9 rows) -- ============================================================ -- Mismatch Length Statistics Tests @@ -1787,14 +1750,13 @@ WINDOW w AS ( WindowAgg (actual rows=100.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" b+ c - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 151 total, 0 merged NFA Contexts: 3 peak, 101 total, 60 pruned NFA: 10 matched (len 6/6/6.0), 0 mismatched NFA: 20 absorbed (len 1/1/1.0), 10 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=100.00 loops=1) -(10 rows) +(9 rows) -- Long partial matches that fail CREATE VIEW rpr_ev_mlen_long_partial AS @@ -1851,14 +1813,13 @@ WINDOW w AS ( WindowAgg (actual rows=60.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" b+ c - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 115 total, 0 merged NFA Contexts: 3 peak, 61 total, 15 pruned NFA: 1 matched (len 30/30/30.0), 1 mismatched (len 26/26/26.0) NFA: 42 absorbed (len 1/1/1.0), 1 skipped (len 1/1/1.0) -> Function Scan on generate_series i (actual rows=60.00 loops=1) -(10 rows) +(9 rows) -- ============================================================ -- JSON Format Tests @@ -1902,7 +1863,6 @@ WINDOW w AS ( "Disabled": false, + "Window": "w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)",+ "Pattern": "a+\" b+", + - "Nav Mark Lookback": 0, + "Storage": "Memory", + "Maximum Storage": 0, + "NFA States Peak": 3, + @@ -1980,7 +1940,6 @@ WINDOW w AS ( "Disabled": false, + "Window": "w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)",+ "Pattern": "a+\" b", + - "Nav Mark Lookback": 0, + "Storage": "Memory", + "Maximum Storage": 0, + "NFA States Peak": 3, + @@ -2062,7 +2021,6 @@ WINDOW w AS ( "Disabled": false, + "Window": "w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)",+ "Pattern": "a b c", + - "Nav Mark Lookback": 0, + "Storage": "Memory", + "Maximum Storage": 0, + "NFA States Peak": 2, + @@ -2143,7 +2101,6 @@ WINDOW w AS ( "Disabled": false, + "Window": "w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)",+ "Pattern": "(a | b){8}", + - "Nav Mark Lookback": 0, + "Storage": "Memory", + "Maximum Storage": 0, + "NFA States Peak": 17, + @@ -2227,7 +2184,6 @@ WINDOW w AS ( false + w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)+ a b + - 0 + Memory + 0 + 2 + @@ -2266,6 +2222,170 @@ WINDOW w AS ( (1 row) +-- Absorbed contexts and a navigation, neither of which the case above emits, +-- so without this the XML spelling of the absorbed length group and of Nav +-- Mark goes unchecked. +CREATE VIEW rpr_ev_xml_absorb AS +SELECT count(*) OVER w +FROM generate_series(1, 100) 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 v % 10 <> 0 AND PREV(v) IS NOT NULL, B AS v % 10 = 0 +); +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_xml_absorb'), E'\n')) AS line WHERE line ~ 'PATTERN'; + line +-------------------- + PATTERN (a+ b+) +(1 row) + +SELECT rpr_explain_filter(' +EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF, FORMAT XML) +SELECT count(*) OVER w +FROM generate_series(1, 100) 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 v % 10 <> 0 AND PREV(v) IS NOT NULL, B AS v % 10 = 0 +)'); + rpr_explain_filter +-------------------------------------------------------------------------------- + + + + + + + WindowAgg + + false + + false + + 100.00 + + 1 + + false + + w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)+ + a+" b+ + + 1 + + Memory + + 0 + + 4 + + 200 + + 0 + + 3 + + 101 + + 79 + + 10 + + 1 + + 10 + + 0 + + 9 + + 10 + + 9.9 + + 1 + + 1 + + 1.0 + + 1 + + 1 + + 1.0 + + + + + + Function Scan + + Outer + + false + + false + + generate_series + + s + + 100.00 + + 1 + + false + + + + + + + + + + + + + + +(1 row) + +-- A pattern that mismatches at all, which the two cases above never do, so +-- this is where the mismatch length group appears in XML. +CREATE VIEW rpr_ev_xml_mismatch AS +SELECT count(*) OVER w +FROM generate_series(1, 40) AS s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + PATTERN ((A | B){2,4}) + DEFINE A AS v % 2 = 1, B AS v % 3 = 0 +); +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_xml_mismatch'), E'\n')) AS line WHERE line ~ 'PATTERN'; + line +--------------------------- + PATTERN ((a | b){2,4}) +(1 row) + +SELECT rpr_explain_filter(' +EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF, FORMAT XML) +SELECT count(*) OVER w +FROM generate_series(1, 40) AS s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + PATTERN ((A | B){2,4}) + DEFINE A AS v % 2 = 1, B AS v % 3 = 0 +)'); + rpr_explain_filter +-------------------------------------------------------------------------------- + + + + + + + WindowAgg + + false + + false + + 40.00 + + 1 + + false + + w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)+ + (a | b){2,4} + + Memory + + 0 + + 6 + + 201 + + 14 + + 3 + + 41 + + 0 + + 12 + + 14 + + 6 + + 8 + + 3 + + 3 + + 3.0 + + 2 + + 2 + + 2.0 + + 1 + + 1 + + 1.0 + + + + + + Function Scan + + Outer + + false + + false + + generate_series + + s + + 40.00 + + 1 + + false + + + + + + + + + + + + + + +(1 row) + -- ============================================================ -- Multiple Partitions Tests -- ============================================================ @@ -2310,7 +2430,6 @@ WINDOW w AS ( WindowAgg (actual rows=90.00 loops=1) Window: w AS (PARTITION BY p.p ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" b - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 165 total, 0 merged NFA Contexts: 2 peak, 93 total, 0 pruned @@ -2322,7 +2441,7 @@ WINDOW w AS ( -> Nested Loop (actual rows=90.00 loops=1) -> Function Scan on generate_series p (actual rows=3.00 loops=1) -> Function Scan on generate_series v (actual rows=30.00 loops=3) -(15 rows) +(14 rows) -- Different pattern behavior per partition CREATE VIEW rpr_ev_part_diff AS @@ -2367,7 +2486,6 @@ WINDOW w AS ( WindowAgg (actual rows=50.00 loops=1) Window: w AS (PARTITION BY (CASE WHEN (v.v <= 25) THEN 1 ELSE 2 END) ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" b - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 77 total, 0 merged NFA Contexts: 2 peak, 52 total, 21 pruned @@ -2377,7 +2495,7 @@ WINDOW w AS ( Sort Key: (CASE WHEN (v.v <= 25) THEN 1 ELSE 2 END) Sort Method: quicksort Memory: NkB -> Function Scan on generate_series v (actual rows=50.00 loops=1) -(13 rows) +(12 rows) -- ============================================================ -- Edge Cases @@ -2413,9 +2531,8 @@ WINDOW w AS ( WindowAgg (actual rows=0.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a b - Nav Mark Lookback: 0 -> Function Scan on generate_series s (actual rows=0.00 loops=1) -(5 rows) +(4 rows) -- Empty matches (length 0): mirror the test_728_* cases in rpr_nfa.sql. -- Window aggregates over a length-0 frame return 0 / NULL, so the SELECT @@ -2453,13 +2570,12 @@ WINDOW w AS ( WindowAgg (actual rows=3.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a{0,3} - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 8 total, 0 merged NFA Contexts: 2 peak, 4 total, 0 pruned NFA: 3 matched (len 0/0/0.0), 0 mismatched -> Function Scan on generate_series s (actual rows=3.00 loops=1) -(9 rows) +(8 rows) -- (A?){1,3}: min=1, one empty iteration satisfies min -> 3 length-0 matches CREATE VIEW rpr_ev_edge_empty_match_min1 AS @@ -2492,13 +2608,12 @@ WINDOW w AS ( WindowAgg (actual rows=3.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a{0,3} - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 8 total, 0 merged NFA Contexts: 2 peak, 4 total, 0 pruned NFA: 3 matched (len 0/0/0.0), 0 mismatched -> Function Scan on generate_series s (actual rows=3.00 loops=1) -(9 rows) +(8 rows) -- (A?){2,3}: min=2 (ISO/IEC 19075-5 7.2.8 STR06 = STRE STRE) -> 3 length-0 matches CREATE VIEW rpr_ev_edge_empty_match_min2 AS @@ -2531,13 +2646,12 @@ WINDOW w AS ( WindowAgg (actual rows=3.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a{0,3} - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 8 total, 0 merged NFA Contexts: 2 peak, 4 total, 0 pruned NFA: 3 matched (len 0/0/0.0), 0 mismatched -> Function Scan on generate_series s (actual rows=3.00 loops=1) -(9 rows) +(8 rows) -- (A?){2,3} mixed: rows 1-2 match A (real), rows 3-4 fall back to empty CREATE VIEW rpr_ev_edge_empty_match_mixed AS @@ -2570,13 +2684,12 @@ WINDOW w AS ( WindowAgg (actual rows=4.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a{0,3} - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 6 peak, 13 total, 0 merged NFA Contexts: 4 peak, 5 total, 0 pruned NFA: 4 matched (len 0/2/0.8), 0 mismatched -> Function Scan on generate_series s (actual rows=4.00 loops=1) -(9 rows) +(8 rows) -- (A? B?){2,3}: pure empty multi-element body -> 3 length-0 matches CREATE VIEW rpr_ev_edge_empty_match_multi AS @@ -2609,13 +2722,12 @@ WINDOW w AS ( WindowAgg (actual rows=3.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a? b?){2,3} - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 7 peak, 24 total, 0 merged NFA Contexts: 2 peak, 4 total, 0 pruned NFA: 3 matched (len 0/0/0.0), 0 mismatched -> Function Scan on generate_series s (actual rows=3.00 loops=1) -(9 rows) +(8 rows) -- Single row CREATE VIEW rpr_ev_edge_single_row AS @@ -2648,13 +2760,12 @@ WINDOW w AS ( WindowAgg (actual rows=1.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 2 peak, 2 total, 0 merged NFA Contexts: 2 peak, 2 total, 0 pruned NFA: 1 matched (len 1/1/1.0), 0 mismatched -> Function Scan on generate_series s (actual rows=1.00 loops=1) -(9 rows) +(8 rows) -- Pattern longer than data CREATE VIEW rpr_ev_edge_pattern_longer AS @@ -2691,13 +2802,12 @@ WINDOW w AS ( WindowAgg (actual rows=5.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a b c d e f g h i j - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 2 peak, 6 total, 0 merged NFA Contexts: 3 peak, 6 total, 4 pruned NFA: 0 matched, 1 mismatched (len 5/5/5.0) -> Function Scan on generate_series s (actual rows=5.00 loops=1) -(9 rows) +(8 rows) -- All rows match as single match CREATE VIEW rpr_ev_edge_single_match AS @@ -2730,14 +2840,13 @@ WINDOW w AS ( WindowAgg (actual rows=50.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 101 total, 0 merged NFA Contexts: 2 peak, 51 total, 0 pruned NFA: 1 matched (len 50/50/50.0), 0 mismatched NFA: 49 absorbed (len 1/1/1.0), 0 skipped -> Function Scan on generate_series s (actual rows=50.00 loops=1) -(10 rows) +(9 rows) -- ============================================================ -- Complex Pattern Tests @@ -2773,14 +2882,13 @@ WINDOW w AS ( WindowAgg (actual rows=60.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a' b' c')+" - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 4 peak, 81 total, 0 merged NFA Contexts: 4 peak, 61 total, 20 pruned NFA: 1 matched (len 60/60/60.0), 0 mismatched NFA: 19 absorbed (len 3/3/3.0), 20 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=60.00 loops=1) -(10 rows) +(9 rows) -- Multiple alternations CREATE VIEW rpr_ev_cpx_multi_alt AS @@ -2817,14 +2925,13 @@ WINDOW w AS ( WindowAgg (actual rows=100.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a | b) (c | d | e) - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 6 peak, 423 total, 0 merged NFA Contexts: 3 peak, 101 total, 40 pruned NFA: 20 matched (len 2/2/2.0), 20 mismatched (len 2/2/2.0) NFA: 0 absorbed, 20 skipped (len 1/1/1.0) -> Seq Scan on rpr_nfa_test (actual rows=100.00 loops=1) -(10 rows) +(9 rows) -- Optional elements CREATE VIEW rpr_ev_cpx_optional AS @@ -2857,14 +2964,13 @@ WINDOW w AS ( WindowAgg (actual rows=50.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a b? c - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 64 total, 0 merged NFA Contexts: 3 peak, 51 total, 25 pruned NFA: 12 matched (len 3/3/3.0), 1 mismatched (len 2/2/2.0) NFA: 0 absorbed, 12 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=50.00 loops=1) -(10 rows) +(9 rows) -- Bounded quantifiers CREATE VIEW rpr_ev_cpx_bounded AS @@ -2897,14 +3003,13 @@ WINDOW w AS ( WindowAgg (actual rows=100.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a{2,5} b - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 9 peak, 311 total, 0 merged NFA Contexts: 7 peak, 101 total, 0 pruned NFA: 10 matched (len 6/6/6.0), 40 mismatched (len 6/6/6.0) NFA: 0 absorbed, 50 skipped (len 1/5/3.0) -> Function Scan on generate_series s (actual rows=100.00 loops=1) -(10 rows) +(9 rows) -- Star quantifier CREATE VIEW rpr_ev_cpx_star AS @@ -2937,14 +3042,13 @@ WINDOW w AS ( WindowAgg (actual rows=50.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a b* c - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 91 total, 0 merged NFA Contexts: 3 peak, 51 total, 40 pruned NFA: 5 matched (len 9/9/9.0), 0 mismatched NFA: 0 absorbed, 5 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=50.00 loops=1) -(10 rows) +(9 rows) -- ============================================================ -- Real-world Pattern Examples @@ -2980,14 +3084,13 @@ WINDOW w AS ( WindowAgg (actual rows=30.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: d+" u+ - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 4 peak, 58 total, 0 merged NFA Contexts: 3 peak, 31 total, 3 pruned NFA: 3 matched (len 3/14/8.0), 1 mismatched (len 3/3/3.0) NFA: 9 absorbed (len 1/1/1.0), 14 skipped (len 1/1/1.0) -> Seq Scan on rpr_nfa_complex (actual rows=30.00 loops=1) -(10 rows) +(9 rows) -- Stock price pattern - peak (up, stable, down) CREATE VIEW rpr_ev_real_peak AS @@ -3020,14 +3123,13 @@ WINDOW w AS ( WindowAgg (actual rows=30.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: u+" s* d+ - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 5 peak, 76 total, 0 merged NFA Contexts: 3 peak, 31 total, 1 pruned NFA: 4 matched (len 3/11/7.2), 0 mismatched NFA: 12 absorbed (len 1/1/1.0), 13 skipped (len 1/1/1.0) -> Seq Scan on rpr_nfa_complex (actual rows=30.00 loops=1) -(10 rows) +(9 rows) -- Consecutive increasing values (using PREV) CREATE VIEW rpr_ev_real_increasing AS @@ -3103,14 +3205,13 @@ WINDOW w AS ( WindowAgg (actual rows=1000.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a b - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 2 peak, 1001 total, 0 merged NFA Contexts: 2 peak, 1001 total, 0 pruned NFA: 500 matched (len 2/2/2.0), 0 mismatched NFA: 0 absorbed, 500 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=1000.00 loops=1) -(10 rows) +(9 rows) -- Large dataset with absorption CREATE VIEW rpr_ev_perf_large_absorb AS @@ -3143,14 +3244,13 @@ WINDOW w AS ( WindowAgg (actual rows=1000.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" b - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 1991 total, 0 merged NFA Contexts: 2 peak, 1001 total, 0 pruned NFA: 10 matched (len 100/100/100.0), 0 mismatched NFA: 980 absorbed (len 1/1/1.0), 10 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=1000.00 loops=1) -(10 rows) +(9 rows) -- High state merge ratio CREATE VIEW rpr_ev_perf_high_merge AS @@ -3183,14 +3283,13 @@ WINDOW w AS ( WindowAgg (actual rows=500.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a | b)+ c - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 9 peak, 3006 total, 0 merged NFA Contexts: 3 peak, 501 total, 1 pruned NFA: 166 matched (len 3/3/3.0), 1 mismatched (len 2/2/2.0) NFA: 0 absorbed, 332 skipped (len 1/2/1.5) -> Function Scan on generate_series s (actual rows=500.00 loops=1) -(10 rows) +(9 rows) -- ============================================================ -- INITIAL vs no INITIAL comparison @@ -3228,14 +3327,13 @@ WINDOW w AS ( WindowAgg (actual rows=50.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" b - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 91 total, 0 merged NFA Contexts: 2 peak, 51 total, 0 pruned NFA: 10 matched (len 5/5/5.0), 0 mismatched NFA: 30 absorbed (len 1/1/1.0), 10 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=50.00 loops=1) -(10 rows) +(9 rows) -- Without INITIAL keyword (same behavior currently) CREATE VIEW rpr_ev_initial_without AS @@ -3268,14 +3366,13 @@ WINDOW w AS ( WindowAgg (actual rows=50.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" b - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 91 total, 0 merged NFA Contexts: 2 peak, 51 total, 0 pruned NFA: 10 matched (len 5/5/5.0), 0 mismatched NFA: 30 absorbed (len 1/1/1.0), 10 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=50.00 loops=1) -(10 rows) +(9 rows) -- ============================================================ -- Quantifier Variations @@ -3311,14 +3408,13 @@ WINDOW w AS ( WindowAgg (actual rows=40.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 71 total, 0 merged NFA Contexts: 3 peak, 41 total, 10 pruned NFA: 10 matched (len 3/3/3.0), 0 mismatched NFA: 20 absorbed (len 1/1/1.0), 0 skipped -> Function Scan on generate_series s (actual rows=40.00 loops=1) -(10 rows) +(9 rows) -- Star quantifier (zero or more) CREATE VIEW rpr_ev_quant_star AS @@ -3351,14 +3447,13 @@ WINDOW w AS ( WindowAgg (actual rows=40.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a*" b - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 4 peak, 102 total, 0 merged NFA Contexts: 2 peak, 41 total, 10 pruned NFA: 10 matched (len 3/3/3.0), 0 mismatched NFA: 10 absorbed (len 1/1/1.0), 10 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=40.00 loops=1) -(10 rows) +(9 rows) -- Question mark (zero or one) CREATE VIEW rpr_ev_quant_question AS @@ -3391,14 +3486,13 @@ WINDOW w AS ( WindowAgg (actual rows=40.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a? b c - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 4 peak, 82 total, 0 merged NFA Contexts: 3 peak, 41 total, 10 pruned NFA: 10 matched (len 3/3/3.0), 0 mismatched NFA: 0 absorbed, 20 skipped (len 1/2/1.5) -> Function Scan on generate_series s (actual rows=40.00 loops=1) -(10 rows) +(9 rows) -- Exact count {n} CREATE VIEW rpr_ev_quant_exact AS @@ -3431,14 +3525,13 @@ WINDOW w AS ( WindowAgg (actual rows=50.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a{3} b - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 4 peak, 51 total, 0 merged NFA Contexts: 5 peak, 51 total, 0 pruned NFA: 10 matched (len 4/4/4.0), 10 mismatched (len 4/4/4.0) NFA: 0 absorbed, 30 skipped (len 1/3/2.0) -> Function Scan on generate_series s (actual rows=50.00 loops=1) -(10 rows) +(9 rows) -- Range {n,m} CREATE VIEW rpr_ev_quant_range AS @@ -3471,14 +3564,13 @@ WINDOW w AS ( WindowAgg (actual rows=50.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a{2,4} b - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 7 peak, 101 total, 0 merged NFA Contexts: 5 peak, 51 total, 0 pruned NFA: 10 matched (len 5/5/5.0), 0 mismatched NFA: 0 absorbed, 40 skipped (len 1/4/2.5) -> Function Scan on generate_series s (actual rows=50.00 loops=1) -(10 rows) +(9 rows) -- At least {n,} CREATE VIEW rpr_ev_quant_atleast AS @@ -3511,14 +3603,13 @@ WINDOW w AS ( WindowAgg (actual rows=50.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a{3,}" b - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 86 total, 0 merged NFA Contexts: 2 peak, 51 total, 0 pruned NFA: 5 matched (len 10/10/10.0), 0 mismatched NFA: 40 absorbed (len 1/1/1.0), 5 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=50.00 loops=1) -(10 rows) +(9 rows) -- ============================================================ -- Regression Tests for Statistics Accuracy @@ -3555,14 +3646,13 @@ WINDOW w AS ( WindowAgg (actual rows=20.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" b - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 37 total, 0 merged NFA Contexts: 2 peak, 21 total, 0 pruned NFA: 4 matched (len 5/5/5.0), 0 mismatched NFA: 12 absorbed (len 1/1/1.0), 4 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=20.00 loops=1) -(10 rows) +(9 rows) -- Verify context count with known absorption CREATE VIEW rpr_ev_reg_ctx_absorb AS @@ -3595,14 +3685,13 @@ WINDOW w AS ( WindowAgg (actual rows=30.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" b c - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 52 total, 0 merged NFA Contexts: 3 peak, 31 total, 6 pruned NFA: 3 matched (len 9/9/9.0), 0 mismatched NFA: 18 absorbed (len 1/1/1.0), 3 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=30.00 loops=1) -(10 rows) +(9 rows) -- Verify match length with fixed-length pattern CREATE VIEW rpr_ev_reg_matchlen AS @@ -3635,14 +3724,13 @@ WINDOW w AS ( WindowAgg (actual rows=30.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a b c - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 2 peak, 31 total, 0 merged NFA Contexts: 3 peak, 31 total, 10 pruned NFA: 10 matched (len 3/3/3.0), 0 mismatched NFA: 0 absorbed, 10 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=30.00 loops=1) -(10 rows) +(9 rows) -- ============================================================ -- Alternation Pattern Tests @@ -3678,14 +3766,13 @@ WINDOW w AS ( WindowAgg (actual rows=100.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a | b) c - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 4 peak, 303 total, 0 merged NFA Contexts: 3 peak, 101 total, 40 pruned NFA: 20 matched (len 2/2/2.0), 20 mismatched (len 2/2/2.0) NFA: 0 absorbed, 20 skipped (len 1/1/1.0) -> Seq Scan on rpr_nfa_test (actual rows=100.00 loops=1) -(10 rows) +(9 rows) -- Multiple items in alternation CREATE VIEW rpr_ev_alt_multi_item AS @@ -3722,14 +3809,13 @@ WINDOW w AS ( WindowAgg (actual rows=100.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a | b | c | d) e - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 6 peak, 505 total, 0 merged NFA Contexts: 3 peak, 101 total, 0 pruned NFA: 20 matched (len 2/2/2.0), 60 mismatched (len 2/2/2.0) NFA: 0 absorbed, 20 skipped (len 1/1/1.0) -> Seq Scan on rpr_nfa_test (actual rows=100.00 loops=1) -(10 rows) +(9 rows) -- Alternation with quantifiers CREATE VIEW rpr_ev_alt_with_quant AS @@ -3762,14 +3848,13 @@ WINDOW w AS ( WindowAgg (actual rows=50.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a | b)+ c - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 9 peak, 306 total, 0 merged NFA Contexts: 3 peak, 51 total, 1 pruned NFA: 16 matched (len 3/3/3.0), 1 mismatched (len 2/2/2.0) NFA: 0 absorbed, 32 skipped (len 1/2/1.5) -> Function Scan on generate_series s (actual rows=50.00 loops=1) -(10 rows) +(9 rows) -- Multiple alternatives (4+) CREATE VIEW rpr_ev_alt_four_plus AS @@ -3800,13 +3885,12 @@ WINDOW w AS ( WindowAgg (actual rows=100.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a | b | c | d | e) - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 7 peak, 606 total, 0 merged NFA Contexts: 2 peak, 101 total, 0 pruned NFA: 100 matched (len 1/1/1.0), 0 mismatched -> Function Scan on generate_series s (actual rows=100.00 loops=1) -(9 rows) +(8 rows) -- Alternation at start CREATE VIEW rpr_ev_alt_at_start AS @@ -3837,14 +3921,13 @@ WINDOW w AS ( WindowAgg (actual rows=60.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a | b) c d - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 4 peak, 183 total, 0 merged NFA Contexts: 3 peak, 61 total, 16 pruned NFA: 15 matched (len 3/3/3.0), 14 mismatched (len 2/2/2.0) NFA: 0 absorbed, 15 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=60.00 loops=1) -(10 rows) +(9 rows) -- Multiple sequential alternations CREATE VIEW rpr_ev_alt_sequential AS @@ -3875,13 +3958,12 @@ WINDOW w AS ( WindowAgg (actual rows=100.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a | b) c (d | e) f - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 5 peak, 337 total, 0 merged NFA Contexts: 3 peak, 101 total, 67 pruned NFA: 0 matched, 33 mismatched (len 2/4/3.0) -> Function Scan on generate_series s (actual rows=100.00 loops=1) -(9 rows) +(8 rows) -- Quantified alternatives CREATE VIEW rpr_ev_alt_quantified AS @@ -3912,14 +3994,13 @@ WINDOW w AS ( WindowAgg (actual rows=60.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a+" | b+") c - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 5 peak, 223 total, 0 merged NFA Contexts: 3 peak, 61 total, 1 pruned NFA: 20 matched (len 2/2/2.0), 19 mismatched (len 2/2/2.0) NFA: 0 absorbed, 20 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=60.00 loops=1) -(10 rows) +(9 rows) -- Alternation at end CREATE VIEW rpr_ev_alt_at_end AS @@ -3950,14 +4031,13 @@ WINDOW w AS ( WindowAgg (actual rows=60.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a b (c | d) - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 89 total, 0 merged NFA Contexts: 3 peak, 61 total, 32 pruned NFA: 14 matched (len 3/3/3.0), 0 mismatched NFA: 0 absorbed, 14 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=60.00 loops=1) -(10 rows) +(9 rows) -- Nested ALT at start of branch inside outer ALT -- Pattern: (A ((B | C) D | E)) - preceding VAR + inner ALT as first branch element @@ -3989,13 +4069,12 @@ WINDOW w AS ( WindowAgg (actual rows=20.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a ((b | c) d | e) - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 4 peak, 37 total, 0 merged NFA Contexts: 3 peak, 21 total, 17 pruned NFA: 0 matched, 3 mismatched (len 3/3/3.0) -> Function Scan on generate_series s (actual rows=20.00 loops=1) -(9 rows) +(8 rows) -- Nested ALT at end of branch inside outer ALT -- Pattern: (C (A | B) | D) - inner ALT is last element in outer branch @@ -4027,13 +4106,12 @@ WINDOW w AS ( WindowAgg (actual rows=20.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (c (a | b) | d) - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 5 peak, 73 total, 0 merged NFA Contexts: 3 peak, 21 total, 10 pruned NFA: 5 matched (len 1/1/1.0), 5 mismatched (len 2/2/2.0) -> Function Scan on generate_series s (actual rows=20.00 loops=1) -(9 rows) +(8 rows) -- Quantified group as the first alternation branch -- Pattern: ((A B)+ | C) - leading group branch must open the enclosing paren @@ -4065,14 +4143,13 @@ WINDOW w AS ( WindowAgg (actual rows=20.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: ((a' b')+" | c) - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 5 peak, 67 total, 0 merged NFA Contexts: 3 peak, 21 total, 7 pruned NFA: 9 matched (len 1/2/1.4), 0 mismatched NFA: 0 absorbed, 4 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=20.00 loops=1) -(10 rows) +(9 rows) -- Quantified group as the last alternation branch -- Pattern: (C | (A B)+) - trailing group branch, no separator follows @@ -4104,14 +4181,13 @@ WINDOW w AS ( WindowAgg (actual rows=20.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (c | (a' b')+") - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 5 peak, 67 total, 0 merged NFA Contexts: 3 peak, 21 total, 7 pruned NFA: 9 matched (len 1/2/1.4), 0 mismatched NFA: 0 absorbed, 4 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=20.00 loops=1) -(10 rows) +(9 rows) -- Quantified group as the middle branch of a three-way alternation -- Pattern: (C | (A B)+ | D) - separator before D must survive the group branch @@ -4143,14 +4219,13 @@ WINDOW w AS ( WindowAgg (actual rows=20.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (c | (a' b')+" | d) - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 6 peak, 88 total, 0 merged NFA Contexts: 3 peak, 21 total, 2 pruned NFA: 14 matched (len 1/2/1.3), 0 mismatched NFA: 0 absorbed, 4 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=20.00 loops=1) -(10 rows) +(9 rows) -- Quantified group as the first branch of a three-way alternation -- Pattern: ((A B)+ | C | D) - leading group branch with two following branches @@ -4182,14 +4257,13 @@ WINDOW w AS ( WindowAgg (actual rows=20.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: ((a' b')+" | c | d) - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 6 peak, 88 total, 0 merged NFA Contexts: 3 peak, 21 total, 2 pruned NFA: 14 matched (len 1/2/1.3), 0 mismatched NFA: 0 absorbed, 4 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=20.00 loops=1) -(10 rows) +(9 rows) -- Bounded-quantifier group as the first alternation branch -- Pattern: ((A B){2} | C) - leading group branch with a range quantifier @@ -4221,13 +4295,12 @@ WINDOW w AS ( WindowAgg (actual rows=20.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: ((a b){2} | c) - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 4 peak, 63 total, 0 merged NFA Contexts: 3 peak, 21 total, 11 pruned NFA: 5 matched (len 1/1/1.0), 4 mismatched (len 3/3/3.0) -> Function Scan on generate_series s (actual rows=20.00 loops=1) -(9 rows) +(8 rows) -- Two quantified groups in one alternation -- Pattern: ((A B)+ | (C D)+) - both branches are groups @@ -4259,14 +4332,13 @@ WINDOW w AS ( WindowAgg (actual rows=20.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: ((a' b')+" | (c' d')+") - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 5 peak, 72 total, 0 merged NFA Contexts: 3 peak, 21 total, 2 pruned NFA: 9 matched (len 2/2/2.0), 0 mismatched NFA: 0 absorbed, 9 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=20.00 loops=1) -(10 rows) +(9 rows) -- Leading group branch in an alternation nested in a sequence -- Pattern: (((A B)+ | C) D) - inner alternation opens with a group branch @@ -4298,14 +4370,13 @@ WINDOW w AS ( WindowAgg (actual rows=20.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: ((a' b')+" | c) d - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 5 peak, 67 total, 0 merged NFA Contexts: 3 peak, 21 total, 6 pruned NFA: 5 matched (len 2/2/2.0), 4 mismatched (len 3/3/3.0) NFA: 0 absorbed, 5 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=20.00 loops=1) -(10 rows) +(9 rows) -- Trailing group branch in an alternation nested in a sequence -- Pattern: ((C | (A B)+) D) - group as last branch, then a sequence element @@ -4337,14 +4408,13 @@ WINDOW w AS ( WindowAgg (actual rows=20.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (c | (a' b')+") d - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 5 peak, 67 total, 0 merged NFA Contexts: 3 peak, 21 total, 6 pruned NFA: 5 matched (len 2/2/2.0), 4 mismatched (len 3/3/3.0) NFA: 0 absorbed, 5 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=20.00 loops=1) -(10 rows) +(9 rows) -- Quantified alternation whose first branch is a quantified group -- Pattern: (((A B){2} | C)+) - single-ALT group wraps a leading group branch @@ -4376,13 +4446,12 @@ WINDOW w AS ( WindowAgg (actual rows=20.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: ((a b){2} | c)+ - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 6 peak, 78 total, 0 merged NFA Contexts: 3 peak, 21 total, 11 pruned NFA: 5 matched (len 1/1/1.0), 4 mismatched (len 3/3/3.0) -> Function Scan on generate_series s (actual rows=20.00 loops=1) -(9 rows) +(8 rows) -- Unit (1,1) group as an alternation branch (emits no BEGIN/END) -- Pattern: ((A B) | C) - control: takes the variable path, not deparse_rpr_group @@ -4414,14 +4483,13 @@ WINDOW w AS ( WindowAgg (actual rows=20.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a b | c) - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 4 peak, 63 total, 0 merged NFA Contexts: 2 peak, 21 total, 7 pruned NFA: 9 matched (len 1/2/1.4), 0 mismatched NFA: 0 absorbed, 4 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=20.00 loops=1) -(10 rows) +(9 rows) -- Quantified variable as the first alternation branch -- Pattern: (A+ | C) - control: deparse_rpr_var already opens the leading paren @@ -4453,13 +4521,12 @@ WINDOW w AS ( WindowAgg (actual rows=20.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a+" | c) - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 5 peak, 68 total, 0 merged NFA Contexts: 3 peak, 21 total, 10 pruned NFA: 10 matched (len 1/1/1.0), 0 mismatched -> Function Scan on generate_series s (actual rows=20.00 loops=1) -(9 rows) +(8 rows) -- Quantified group as the last branch of a three-way alternation -- Pattern: (C | D | (A B)+) - control: trailing group needs no separator @@ -4491,14 +4558,13 @@ WINDOW w AS ( WindowAgg (actual rows=20.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (c | d | (a' b')+") - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 6 peak, 88 total, 0 merged NFA Contexts: 3 peak, 21 total, 2 pruned NFA: 14 matched (len 1/2/1.3), 0 mismatched NFA: 0 absorbed, 4 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=20.00 loops=1) -(10 rows) +(9 rows) -- Alternation nested in a leading branch must not swallow the trailing branch -- Pattern: (D (A | B) | E) - inherited limit bounds the inner alternation @@ -4530,13 +4596,12 @@ WINDOW w AS ( WindowAgg (actual rows=20.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (d (a | b) | e) - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 5 peak, 71 total, 0 merged NFA Contexts: 3 peak, 21 total, 12 pruned NFA: 4 matched (len 1/1/1.0), 4 mismatched (len 2/2/2.0) -> Function Scan on generate_series s (actual rows=20.00 loops=1) -(9 rows) +(8 rows) -- Group mid-branch followed by a sequence element needs no separator before it -- Pattern: (C | (A B)+ D) - only the branch end takes a SEP, not D @@ -4568,13 +4633,12 @@ WINDOW w AS ( WindowAgg (actual rows=20.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (c | (a' b')+" d) - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 5 peak, 67 total, 0 merged NFA Contexts: 3 peak, 21 total, 11 pruned NFA: 5 matched (len 1/1/1.0), 4 mismatched (len 3/3/3.0) -> Function Scan on generate_series s (actual rows=20.00 loops=1) -(9 rows) +(8 rows) -- Quantified group wrapping a lone alternation: the ALT supplies the parens -- Pattern: ((A | B)+) - loneAlt path, single pair of parens @@ -4606,14 +4670,13 @@ WINDOW w AS ( WindowAgg (actual rows=20.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a | b)+ - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 6 peak, 93 total, 0 merged NFA Contexts: 3 peak, 21 total, 10 pruned NFA: 6 matched (len 1/2/1.7), 0 mismatched NFA: 0 absorbed, 4 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=20.00 loops=1) -(10 rows) +(9 rows) -- Quantified group wrapping a sequence whose last element is an alternation -- Pattern: ((A (B | C))+) - group paren plus a nested alternation paren @@ -4645,14 +4708,13 @@ WINDOW w AS ( WindowAgg (actual rows=20.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a (b | c))+ - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 35 total, 0 merged NFA Contexts: 3 peak, 21 total, 12 pruned NFA: 4 matched (len 2/2/2.0), 0 mismatched NFA: 0 absorbed, 4 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=20.00 loops=1) -(10 rows) +(9 rows) -- Quantified group wrapping a sequence whose first element is an alternation -- Pattern: (((A | B) C)+) - leading nested alternation inside a group sequence @@ -4684,14 +4746,13 @@ WINDOW w AS ( WindowAgg (actual rows=20.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: ((a | b) c)+ - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 6 peak, 78 total, 0 merged NFA Contexts: 3 peak, 21 total, 6 pruned NFA: 5 matched (len 2/2/2.0), 4 mismatched (len 2/2/2.0) NFA: 0 absorbed, 5 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=20.00 loops=1) -(10 rows) +(9 rows) -- Alternation non-last in a non-last branch, stacked three deep: each level's -- inherited limit must bound the inner alternation against the next branch @@ -4726,14 +4787,13 @@ WINDOW w AS ( WindowAgg (actual rows=20.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (((a | b) c | d) e | f) - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 6 peak, 147 total, 0 merged NFA Contexts: 3 peak, 21 total, 4 pruned NFA: 6 matched (len 1/2/1.5), 7 mismatched (len 2/3/2.4) NFA: 0 absorbed, 3 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=20.00 loops=1) -(10 rows) +(9 rows) -- Same interaction stacked four deep, to exercise the induction one step further -- Pattern: ((((A | B) C | D) E | F) G | H) - four nested inherited-limit boundaries @@ -4767,14 +4827,13 @@ WINDOW w AS ( WindowAgg (actual rows=20.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: ((((a | b) c | d) e | f) g | h) - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 7 peak, 189 total, 0 merged NFA Contexts: 3 peak, 21 total, 6 pruned NFA: 4 matched (len 1/2/1.5), 8 mismatched (len 2/3/2.6) NFA: 0 absorbed, 2 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=20.00 loops=1) -(10 rows) +(9 rows) -- Three-deep stack whose innermost branch is a quantified group: the group's -- skip-target jump must not be mistaken for a branch separator at any depth @@ -4809,14 +4868,13 @@ WINDOW w AS ( WindowAgg (actual rows=20.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (((a | b)+ c | d) e | f) - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 11 peak, 177 total, 0 merged NFA Contexts: 4 peak, 21 total, 4 pruned NFA: 6 matched (len 1/2/1.5), 7 mismatched (len 2/4/3.1) NFA: 0 absorbed, 3 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=20.00 loops=1) -(10 rows) +(9 rows) -- Alternation trailing a paren-less sequence (last element of a non-last -- branch, no same-depth sibling to bound it), nested three deep @@ -4851,14 +4909,13 @@ WINDOW w AS ( WindowAgg (actual rows=20.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a (b (c | d) | e) | f) - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 5 peak, 75 total, 0 merged NFA Contexts: 3 peak, 21 total, 11 pruned NFA: 6 matched (len 1/3/2.0), 0 mismatched NFA: 0 absorbed, 3 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=20.00 loops=1) -(10 rows) +(9 rows) -- Same branch-tail alternation nested four deep -- Pattern: (A (B (C (D | E) | F) | G) | H) - branch-tail alternation x4 @@ -4892,14 +4949,13 @@ WINDOW w AS ( WindowAgg (actual rows=20.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a (b (c (d | e) | f) | g) | h) - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 5 peak, 75 total, 0 merged NFA Contexts: 3 peak, 21 total, 14 pruned NFA: 4 matched (len 1/4/2.5), 0 mismatched NFA: 0 absorbed, 2 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=20.00 loops=1) -(10 rows) +(9 rows) -- A nested alternation tail neighbouring a multi-element sequence branch: the -- branch boundary must split "...branch-tail ALT" from a plain "G A" sequence @@ -4934,14 +4990,13 @@ WINDOW w AS ( WindowAgg (actual rows=20.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a (b (c (d | e) | f) | g a) | h) - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 5 peak, 75 total, 0 merged NFA Contexts: 3 peak, 21 total, 14 pruned NFA: 4 matched (len 1/4/2.5), 0 mismatched NFA: 0 absorbed, 2 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=20.00 loops=1) -(10 rows) +(9 rows) -- A nested alternation that is sibling-bounded by a trailing sequence element -- at the outer level (the ALT is not the branch tail; G follows it in-branch) @@ -4976,14 +5031,13 @@ WINDOW w AS ( WindowAgg (actual rows=20.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: ((a (b (c | d) | e) | f) g | h) - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 6 peak, 113 total, 0 merged NFA Contexts: 3 peak, 21 total, 12 pruned NFA: 4 matched (len 1/2/1.5), 2 mismatched (len 4/4/4.0) NFA: 0 absorbed, 2 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=20.00 loops=1) -(10 rows) +(9 rows) -- ============================================================ -- Group Pattern Tests @@ -5019,14 +5073,13 @@ WINDOW w AS ( WindowAgg (actual rows=40.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a' b')+" - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 4 peak, 61 total, 0 merged NFA Contexts: 3 peak, 41 total, 0 pruned NFA: 1 matched (len 40/40/40.0), 0 mismatched NFA: 19 absorbed (len 2/2/2.0), 20 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=40.00 loops=1) -(10 rows) +(9 rows) -- Group with bounded quantifier CREATE VIEW rpr_ev_grp_bounded AS @@ -5059,14 +5112,13 @@ WINDOW w AS ( WindowAgg (actual rows=40.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a b){2,4} - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 4 peak, 51 total, 0 merged NFA Contexts: 3 peak, 41 total, 5 pruned NFA: 5 matched (len 8/8/8.0), 0 mismatched NFA: 0 absorbed, 30 skipped (len 1/2/1.5) -> Function Scan on generate_series s (actual rows=40.00 loops=1) -(10 rows) +(9 rows) -- Nested groups CREATE VIEW rpr_ev_grp_nested AS @@ -5099,14 +5151,13 @@ WINDOW w AS ( WindowAgg (actual rows=60.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: ((a' b'){2}')+" - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 5 peak, 76 total, 0 merged NFA Contexts: 4 peak, 61 total, 15 pruned NFA: 1 matched (len 60/60/60.0), 0 mismatched NFA: 14 absorbed (len 4/4/4.0), 30 skipped (len 1/2/1.5) -> Function Scan on generate_series s (actual rows=60.00 loops=1) -(10 rows) +(9 rows) -- Deep nesting (3+ levels) CREATE VIEW rpr_ev_grp_deep AS @@ -5137,14 +5188,13 @@ WINDOW w AS ( WindowAgg (actual rows=40.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a | b)+ - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 6 peak, 243 total, 0 merged NFA Contexts: 2 peak, 41 total, 0 pruned NFA: 1 matched (len 40/40/40.0), 0 mismatched NFA: 0 absorbed, 39 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=40.00 loops=1) -(10 rows) +(9 rows) -- Bounded quantifier on alternation CREATE VIEW rpr_ev_grp_bounded_alt AS @@ -5175,14 +5225,13 @@ WINDOW w AS ( WindowAgg (actual rows=60.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a | b){2,3} c - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 8 peak, 320 total, 0 merged NFA Contexts: 3 peak, 61 total, 2 pruned NFA: 19 matched (len 3/3/3.0), 1 mismatched (len 2/2/2.0) NFA: 0 absorbed, 38 skipped (len 1/2/1.5) -> Function Scan on generate_series s (actual rows=60.00 loops=1) -(10 rows) +(9 rows) -- Nested groups with quantifiers CREATE VIEW rpr_ev_grp_nested_quant AS @@ -5213,14 +5262,13 @@ WINDOW w AS ( WindowAgg (actual rows=60.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: ((a' b')+" c)* - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 9 peak, 178 total, 0 merged NFA Contexts: 4 peak, 61 total, 0 pruned NFA: 4 matched (len 0/57/14.2), 0 mismatched NFA: 0 absorbed, 56 skipped (len 1/3/1.6) -> Function Scan on generate_series s (actual rows=60.00 loops=1) -(10 rows) +(9 rows) -- Partial nested quantification CREATE VIEW rpr_ev_grp_partial_quant AS @@ -5251,14 +5299,13 @@ WINDOW w AS ( WindowAgg (actual rows=60.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: (a (b c)+)* - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 8 peak, 160 total, 0 merged NFA Contexts: 4 peak, 61 total, 0 pruned NFA: 4 matched (len 0/57/14.2), 0 mismatched NFA: 0 absorbed, 56 skipped (len 1/3/1.6) -> Function Scan on generate_series s (actual rows=60.00 loops=1) -(10 rows) +(9 rows) -- ============================================================ -- Window Function Combinations @@ -5294,14 +5341,13 @@ WINDOW w AS ( WindowAgg (actual rows=30.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" b - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 55 total, 0 merged NFA Contexts: 2 peak, 31 total, 0 pruned NFA: 6 matched (len 5/5/5.0), 0 mismatched NFA: 18 absorbed (len 1/1/1.0), 6 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=30.00 loops=1) -(10 rows) +(9 rows) -- first_value with pattern CREATE VIEW rpr_ev_wfn_first_value AS @@ -5334,14 +5380,13 @@ WINDOW w AS ( WindowAgg (actual rows=30.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" b - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 55 total, 0 merged NFA Contexts: 2 peak, 31 total, 0 pruned NFA: 6 matched (len 5/5/5.0), 0 mismatched NFA: 18 absorbed (len 1/1/1.0), 6 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=30.00 loops=1) -(10 rows) +(9 rows) -- last_value with pattern CREATE VIEW rpr_ev_wfn_last_value AS @@ -5374,14 +5419,13 @@ WINDOW w AS ( WindowAgg (actual rows=30.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" b - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 55 total, 0 merged NFA Contexts: 2 peak, 31 total, 0 pruned NFA: 6 matched (len 5/5/5.0), 0 mismatched NFA: 18 absorbed (len 1/1/1.0), 6 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=30.00 loops=1) -(10 rows) +(9 rows) -- Multiple window functions CREATE VIEW rpr_ev_wfn_multi AS @@ -5420,14 +5464,13 @@ WINDOW w AS ( WindowAgg (actual rows=30.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" b - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 55 total, 0 merged NFA Contexts: 2 peak, 31 total, 0 pruned NFA: 6 matched (len 5/5/5.0), 0 mismatched NFA: 18 absorbed (len 1/1/1.0), 6 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=30.00 loops=1) -(10 rows) +(9 rows) -- ============================================================ -- DEFINE Expression Variations @@ -5467,14 +5510,13 @@ WINDOW w AS ( WindowAgg (actual rows=50.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" b - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 78 total, 0 merged NFA Contexts: 2 peak, 51 total, 6 pruned NFA: 17 matched (len 2/3/2.6), 0 mismatched NFA: 10 absorbed (len 1/1/1.0), 17 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=50.00 loops=1) -(10 rows) +(9 rows) -- Using PREV function CREATE VIEW rpr_ev_def_prev AS @@ -5638,14 +5680,13 @@ WINDOW w AS ( WindowAgg (actual rows=30.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" b - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 55 total, 0 merged NFA Contexts: 2 peak, 31 total, 0 pruned NFA: 6 matched (len 5/5/5.0), 0 mismatched NFA: 18 absorbed (len 1/1/1.0), 6 skipped (len 1/1/1.0) -> Function Scan on generate_series v (actual rows=30.00 loops=1) -(10 rows) +(9 rows) -- ============================================================ -- Large Scale Statistics Verification @@ -5681,14 +5722,13 @@ WINDOW w AS ( WindowAgg (actual rows=500.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" b c - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 3 peak, 851 total, 0 merged NFA Contexts: 3 peak, 501 total, 101 pruned NFA: 50 matched (len 8/9/9.0), 0 mismatched NFA: 299 absorbed (len 1/1/1.0), 50 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=500.00 loops=1) -(10 rows) +(9 rows) -- High match count scenario CREATE VIEW rpr_ev_scale_high_match AS @@ -5721,14 +5761,13 @@ WINDOW w AS ( WindowAgg (actual rows=500.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a b - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 2 peak, 501 total, 0 merged NFA Contexts: 2 peak, 501 total, 0 pruned NFA: 250 matched (len 2/2/2.0), 0 mismatched NFA: 0 absorbed, 250 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=500.00 loops=1) -(10 rows) +(9 rows) -- High skip count scenario CREATE VIEW rpr_ev_scale_high_skip AS @@ -5771,14 +5810,13 @@ WINDOW w AS ( WindowAgg (actual rows=500.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a b c d e - Nav Mark Lookback: 0 Storage: Memory Maximum Storage: NkB NFA States: 2 peak, 501 total, 0 merged NFA Contexts: 3 peak, 501 total, 490 pruned NFA: 5 matched (len 5/5/5.0), 0 mismatched NFA: 0 absorbed, 5 skipped (len 1/1/1.0) -> Function Scan on generate_series s (actual rows=500.00 loops=1) -(10 rows) +(9 rows) -- -- Planner optimization: optimize_window_clauses must not alter RPR frame @@ -5857,13 +5895,12 @@ EXPLAIN (COSTS OFF) SELECT * FROM rpr_ev_opt_mixed; -> WindowAgg Window: w_rpr AS (ORDER BY s.v ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" - Nav Mark Lookback: 0 -> WindowAgg Window: w_normal AS (ORDER BY s.v ROWS UNBOUNDED PRECEDING) -> Sort Sort Key: s.v -> Function Scan on generate_series s -(10 rows) +(9 rows) -- -- Planner optimization: find_window_run_conditions must not push down @@ -5958,9 +5995,8 @@ WINDOW w AS ( WindowAgg Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" - Nav Mark Lookback: 0 -> Function Scan on generate_series s -(5 rows) +(4 rows) -- NEXT only: no backward navigation, offset 0 EXPLAIN (COSTS OFF) SELECT count(*) OVER w @@ -5975,9 +6011,8 @@ WINDOW w AS ( WindowAgg Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" - Nav Mark Lookback: 0 -> Function Scan on generate_series s -(5 rows) +(4 rows) -- PREV(v): implicit offset 1 EXPLAIN (COSTS OFF) SELECT count(*) OVER w @@ -6013,6 +6048,24 @@ WINDOW w AS ( -> Function Scan on generate_series s (5 rows) +-- PREV(v, 1 + 1): a foldable offset has to arrive as a constant, or the trim +-- bound would print "runtime" instead of 2 +EXPLAIN (COSTS OFF) SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS v > PREV(v, 1 + 1) +); + QUERY PLAN +------------------------------------------------------------------- + WindowAgg + Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + Pattern: a+" + Nav Mark Lookback: 2 + -> Function Scan on generate_series s +(5 rows) + -- Two PREV with different offsets: max(1, 5) = 5 EXPLAIN (COSTS OFF) SELECT count(*) OVER w FROM generate_series(1,10) s(v) @@ -6055,55 +6108,163 @@ EXPLAIN (COSTS OFF) EXECUTE rpr_nav_offset_prep(2); RESET plan_cache_mode; DEALLOCATE rpr_nav_offset_prep; --- FIRST(v): retain all (references match_start row) -EXPLAIN (COSTS OFF) SELECT count(*) OVER w +-- EXPLAIN (GENERIC_PLAN) of an unbound parameter offset must not evaluate the +-- parameter: the offset stays "runtime" instead of failing with "no value +-- found for parameter 1". +EXPLAIN (GENERIC_PLAN, COSTS OFF) +SELECT count(*) OVER w FROM generate_series(1,10) s(v) WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING PATTERN (A+) - DEFINE A AS v > FIRST(v) + DEFINE A AS v > PREV(v, $1) ); QUERY PLAN ------------------------------------------------------------------- WindowAgg Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a+ - Nav Mark Lookback: 0 - Nav Mark Lookahead: 0 + Pattern: a+" + Nav Mark Lookback: runtime -> Function Scan on generate_series s -(6 rows) +(5 rows) --- LAST(v, 1): backward reach 1, same as PREV(v, 1) +-- FIRST(v): retain all (references match_start row) EXPLAIN (COSTS OFF) SELECT count(*) OVER w FROM generate_series(1,10) s(v) WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING PATTERN (A+) - DEFINE A AS LAST(v, 1) > 0 + DEFINE A AS v > FIRST(v) ); QUERY PLAN ------------------------------------------------------------------- WindowAgg Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+ - Nav Mark Lookback: 1 + Nav Mark Lookahead: 0 -> Function Scan on generate_series s (5 rows) --- LAST(v) without offset + PREV(v): no match_start dependency, offset 1 +-- FIRST(v, 5): forward reach 5 EXPLAIN (COSTS OFF) SELECT count(*) OVER w FROM generate_series(1,10) s(v) WINDOW w AS ( ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING PATTERN (A+) - DEFINE A AS LAST(v) > PREV(v) + DEFINE A AS v > FIRST(v, 5) ); QUERY PLAN ------------------------------------------------------------------- WindowAgg Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) - Pattern: a+" - Nav Mark Lookback: 1 + Pattern: a+ + Nav Mark Lookahead: 5 + -> Function Scan on generate_series s +(5 rows) + +-- The same forward reach in the XML and JSON forms. Only the text form is +-- exercised elsewhere, so the tag itself has no coverage. +EXPLAIN (COSTS OFF, FORMAT XML) SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS v > FIRST(v, 5) +); + QUERY PLAN +-------------------------------------------------------------------------------- + + + + + + + WindowAgg + + false + + false + + false + + w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)+ + a+ + + 5 + + + + + + Function Scan + + Outer + + false + + false + + generate_series + + s + + false + + + + + + + + + + +(1 row) + +EXPLAIN (COSTS OFF, FORMAT JSON) SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS v > FIRST(v, 5) +); + QUERY PLAN +---------------------------------------------------------------------------- + [ + + { + + "Plan": { + + "Node Type": "WindowAgg", + + "Parallel Aware": false, + + "Async Capable": false, + + "Disabled": false, + + "Window": "w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)",+ + "Pattern": "a+", + + "Nav Mark Lookahead": 5, + + "Plans": [ + + { + + "Node Type": "Function Scan", + + "Parent Relationship": "Outer", + + "Parallel Aware": false, + + "Async Capable": false, + + "Function Name": "generate_series", + + "Alias": "s", + + "Disabled": false + + } + + ] + + } + + } + + ] +(1 row) + +-- LAST(v, 1): backward reach 1, same as PREV(v, 1) +EXPLAIN (COSTS OFF) SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS LAST(v, 1) > 0 +); + QUERY PLAN +------------------------------------------------------------------- + WindowAgg + Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + Pattern: a+ + Nav Mark Lookback: 1 + -> Function Scan on generate_series s +(5 rows) + +-- LAST(v) without offset + PREV(v): no match_start dependency, offset 1 +EXPLAIN (COSTS OFF) SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS LAST(v) > PREV(v) +); + QUERY PLAN +------------------------------------------------------------------- + WindowAgg + Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + Pattern: a+" + Nav Mark Lookback: 1 -> Function Scan on generate_series s (5 rows) @@ -6120,10 +6281,9 @@ WINDOW w AS ( WindowAgg Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+ - Nav Mark Lookback: 0 Nav Mark Lookahead: -1 -> Function Scan on generate_series s -(6 rows) +(5 rows) -- Compound NEXT(FIRST(val), 3): firstOffset = 0+3 = 3 EXPLAIN (COSTS OFF) SELECT count(*) OVER w @@ -6138,10 +6298,9 @@ WINDOW w AS ( WindowAgg Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+ - Nav Mark Lookback: 0 Nav Mark Lookahead: 3 -> Function Scan on generate_series s -(6 rows) +(5 rows) -- Compound PREV(LAST(val), 2): lookback = 0+2 = 2 EXPLAIN (COSTS OFF) SELECT count(*) OVER w @@ -6177,6 +6336,73 @@ WINDOW w AS ( -> Function Scan on generate_series s (5 rows) +-- Compound forms with the outer offset left out, which defaults to 1: each of +-- the four arms combines it with the inner offset differently, and the value +-- decides both the trim bound and the row the navigation lands on. +EXPLAIN (COSTS OFF) SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS PREV(FIRST(v, 2)) > 0 +); + QUERY PLAN +------------------------------------------------------------------- + WindowAgg + Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + Pattern: a+ + Nav Mark Lookahead: 1 + -> Function Scan on generate_series s +(5 rows) + +EXPLAIN (COSTS OFF) SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS NEXT(FIRST(v, 2)) > 0 +); + QUERY PLAN +------------------------------------------------------------------- + WindowAgg + Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + Pattern: a+ + Nav Mark Lookahead: 3 + -> Function Scan on generate_series s +(5 rows) + +EXPLAIN (COSTS OFF) SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS PREV(LAST(v, 2)) > 0 +); + QUERY PLAN +------------------------------------------------------------------- + WindowAgg + Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + Pattern: a+ + Nav Mark Lookback: 3 + -> Function Scan on generate_series s +(5 rows) + +EXPLAIN (COSTS OFF) SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS NEXT(LAST(v, 2)) > 0 +); + QUERY PLAN +------------------------------------------------------------------- + WindowAgg + Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + Pattern: a+ + Nav Mark Lookback: 1 + -> Function Scan on generate_series s +(5 rows) + -- Compound PREV(LAST(val, N), M): constant near-overflow (N+M just fits int64) EXPLAIN (COSTS OFF) SELECT count(*) OVER w FROM generate_series(1,10) s(v) @@ -6225,10 +6451,153 @@ WINDOW w AS ( WindowAgg Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+ - Nav Mark Lookback: 0 Nav Mark Lookahead: infinite -> Function Scan on generate_series s -(6 rows) +(5 rows) + +-- A navigation with a negative offset cannot run, so it contributes no reach +-- and its dimension reports nothing at all. +EXPLAIN (COSTS OFF) SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS PREV(FIRST(v, -3), 2) IS NOT NULL +); + QUERY PLAN +------------------------------------------------------------------- + WindowAgg + Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + Pattern: a+ + -> Function Scan on generate_series s +(4 rows) + +-- The same query errors once it runs, since execution validates the offset. +EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS PREV(FIRST(v, -3), 2) IS NOT NULL +); +ERROR: row pattern navigation offset must not be negative +-- Same at the int64 limit, where the reach subtraction would otherwise wrap. +EXPLAIN (COSTS OFF) SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS PREV(FIRST(v, (-9223372036854775807)::int8), 2) IS NOT NULL +); + QUERY PLAN +------------------------------------------------------------------- + WindowAgg + Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + Pattern: a+ + -> Function Scan on generate_series s +(4 rows) + +-- The other dimension keeps its own aggregate. +EXPLAIN (COSTS OFF) SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS PREV(v, 5) IS NOT NULL AND FIRST(v, -1) IS NOT NULL +); + QUERY PLAN +------------------------------------------------------------------- + WindowAgg + Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + Pattern: a+ + Nav Mark Lookback: 5 + -> Function Scan on generate_series s +(5 rows) + +-- A null offset cannot run either, so it too contributes no reach. Resolving +-- it to a placeholder 0 would instead let it join the aggregate and displace +-- the offset of the navigation that can run. +EXPLAIN (COSTS OFF) SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS FIRST(v, 7) IS NOT NULL AND PREV(FIRST(v, NULL::int), 2) IS NOT NULL +); + QUERY PLAN +------------------------------------------------------------------- + WindowAgg + Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + Pattern: a+ + Nav Mark Lookahead: 7 + -> Function Scan on generate_series s +(5 rows) + +-- And it errors once it runs, for the same reason the negative one does. +EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS FIRST(v, 7) IS NOT NULL AND PREV(FIRST(v, NULL::int), 2) IS NOT NULL +); +ERROR: row pattern navigation offset must not be null +-- Dropping a navigation must not disturb the kind the survivor reports. An +-- overflowing lookback still retains every row. +EXPLAIN (COSTS OFF) SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS FIRST(v, -1) IS NOT NULL + AND PREV(LAST(v, 4611686018427387904), 4611686018427387904) IS NOT NULL +); + QUERY PLAN +------------------------------------------------------------------- + WindowAgg + Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + Pattern: a+ + Nav Mark Lookback: retain all + -> Function Scan on generate_series s +(5 rows) + +-- And a parameter offset beside a dropped one is still settled per scan. +PREPARE test_dropped_with_runtime(int8) AS +SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS FIRST(v, -1) IS NOT NULL AND PREV(v, $1) IS NOT NULL +); +SET plan_cache_mode = force_generic_plan; +EXPLAIN (COSTS OFF) EXECUTE test_dropped_with_runtime(2); + QUERY PLAN +------------------------------------------------------------------- + WindowAgg + Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + Pattern: a+ + Nav Mark Lookback: runtime + -> Function Scan on generate_series s +(5 rows) + +RESET plan_cache_mode; +DEALLOCATE test_dropped_with_runtime; +-- NEXT(LAST()) reaches the same subtraction on the lookback side. +EXPLAIN (COSTS OFF) SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS NEXT(LAST(v, 2), (-9223372036854775807)::int8) IS NOT NULL +); + QUERY PLAN +------------------------------------------------------------------- + WindowAgg + Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + Pattern: a+ + -> Function Scan on generate_series s +(4 rows) -- Compound PREV(LAST(val, $1), $2): parameter lookback overflow -> retain all -- EXPLAIN shows "runtime" (plan-level); EXPLAIN ANALYZE shows "retain all" @@ -6288,14 +6657,13 @@ EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) WindowAgg (actual rows=10.00 loops=1) Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+ - Nav Mark Lookback: 0 Nav Mark Lookahead: infinite Storage: Memory Maximum Storage: NkB NFA States: 1 peak, 11 total, 0 merged NFA Contexts: 2 peak, 11 total, 10 pruned NFA: 0 matched, 0 mismatched -> Function Scan on generate_series s (actual rows=10.00 loops=1) -(10 rows) +(9 rows) RESET plan_cache_mode; DEALLOCATE test_overflow_lookahead; @@ -6317,17 +6685,16 @@ EXPLAIN (COSTS OFF) EXECUTE p_first_runtime(1, 1); WindowAgg Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+ - Nav Mark Lookback: 0 Nav Mark Lookahead: runtime -> Function Scan on generate_series s -(6 rows) +(5 rows) RESET plan_cache_mode; DEALLOCATE p_first_runtime; --- PREV(v) + PREV(v, $1): NEEDS_EVAL path must account for implicit lookback=1 --- Previously, eval_nav_max_offset_walker skipped PREV(v) when offset_arg was --- NULL, causing maxOffset=0 when $1=0, which would trim the row needed by --- PREV(v). Verify this executes without "cannot fetch row before mark" error. +-- PREV(v) + PREV(v, $1): the implicit lookback of 1 has to count even when the +-- explicit offset resolves to 0, or PREV(v) would fail with "cannot fetch row +-- before mark". A generic plan settles the reach per scan instead of at init. +SET plan_cache_mode = force_generic_plan; PREPARE test_prev_implicit_offset(int8) AS SELECT count(*) OVER w FROM generate_series(1,10) s(v) @@ -6352,10 +6719,11 @@ EXECUTE test_prev_implicit_offset(0); (10 rows) DEALLOCATE test_prev_implicit_offset; +RESET plan_cache_mode; -- NEEDS_EVAL executor offset paths: a Param nav offset stays non-Const under a --- generic plan, so the planner marks the offset NEEDS_EVAL and the executor --- resolves it at init via eval_define_offsets -> visit_nav_exec. Each query --- below exercises a different navigation arm of that walker. +-- generic plan, so build_define_offsets() marks the offset NEEDS_EVAL and +-- resolve_nav_offsets() settles it once per scan. Each query below exercises +-- a different navigation arm of that walker. -- Simple FIRST(v, $1): forward-reach FIRST arm. PREPARE test_eval_first(int8) AS SELECT count(*) OVER w @@ -6462,6 +6830,35 @@ EXECUTE test_eval_prevfirst(1, 1); 0 (10 rows) +-- Observe the arm rather than only run it. At plan time the line reads +-- "runtime"; once resolved it reads inner - outer, so 2 here is the PREV_FIRST +-- subtraction. A bare FIRST would report the inner offset alone. +EXPLAIN (COSTS OFF) EXECUTE test_eval_prevfirst(3, 1); + QUERY PLAN +------------------------------------------------------------------- + WindowAgg + Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + Pattern: a+ + Nav Mark Lookahead: runtime + -> Function Scan on generate_series s +(5 rows) + +SELECT rpr_explain_filter(' +EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) +EXECUTE test_eval_prevfirst(3, 1);'); + rpr_explain_filter +---------------------------------------------------------------------- + WindowAgg (actual rows=10.00 loops=1) + Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + Pattern: a+ + Nav Mark Lookahead: 2 + Storage: Memory Maximum Storage: NkB + NFA States: 1 peak, 11 total, 0 merged + NFA Contexts: 2 peak, 11 total, 10 pruned + NFA: 0 matched, 0 mismatched + -> Function Scan on generate_series s (actual rows=10.00 loops=1) +(9 rows) + RESET plan_cache_mode; DEALLOCATE test_eval_prevfirst; -- Runtime error: negative offset at execution time @@ -6476,6 +6873,23 @@ WINDOW w AS ( EXECUTE test_runtime_neg_offset(-1); ERROR: row pattern navigation offset must not be negative DEALLOCATE test_runtime_neg_offset; +-- The same at generic-plan resolution time, and for each half of a compound +-- navigation on its own. +PREPARE test_runtime_neg_compound_offset(int8, int8) AS +SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS NEXT(FIRST(v, $1), $2) IS NOT NULL +); +SET plan_cache_mode = force_generic_plan; +EXECUTE test_runtime_neg_compound_offset(1, -1); +ERROR: row pattern navigation offset must not be negative +EXECUTE test_runtime_neg_compound_offset(-1, 1); +ERROR: row pattern navigation offset must not be negative +RESET plan_cache_mode; +DEALLOCATE test_runtime_neg_compound_offset; -- Runtime error: null offset at execution time PREPARE test_runtime_null_offset(int8) AS SELECT count(*) OVER w @@ -6488,3 +6902,64 @@ WINDOW w AS ( EXECUTE test_runtime_null_offset(NULL); ERROR: row pattern navigation offset must not be null DEALLOCATE test_runtime_null_offset; +-- A correlated PARAM_EXEC nav offset (reaching the offset via SRF inlining) is +-- resolved per scan by resolve_nav_offsets(); after execution EXPLAIN ANALYZE +-- must display the concrete resolved bound (a number), not "runtime" -- that is, +-- navMaxOffsetKind resolves to FIXED. Plain EXPLAIN of the same query shows +-- "runtime"; only ANALYZE exercises the per-scan clear. +CREATE TABLE rpr_exp_srf (v int); +INSERT INTO rpr_exp_srf SELECT generate_series(1, 10); +CREATE FUNCTION rpr_exp_srf_f(k int) RETURNS SETOF bigint AS $$ + SELECT count(*) OVER w + FROM rpr_exp_srf + WINDOW w AS (ORDER BY v ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) DEFINE A AS v > PREV(v, k)) +$$ LANGUAGE sql STABLE; +SELECT t FROM rpr_explain_filter( + 'EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF) + SELECT g.n, max(s) FROM (VALUES (2), (2)) g(n), LATERAL rpr_exp_srf_f(g.n) s + GROUP BY g.n') AS t +WHERE t LIKE '%Nav Mark Lookback%'; + t +------------------------------------ + Nav Mark Lookback: 2 +(1 row) + +DROP FUNCTION rpr_exp_srf_f(int); +-- The kind a scan settles on is per scan, not sticky. An outer offset that +-- overflows int64 gives up on the trim for that scan alone, and EXPLAIN +-- ANALYZE reports what the last rescan left behind: the same three offsets +-- in a different order have to read differently, retain all when the +-- overflow runs last and a bound again when a smaller offset follows it. +CREATE FUNCTION rpr_exp_srf_cmp(k int8) RETURNS SETOF bigint AS $$ + SELECT count(*) OVER w + FROM rpr_exp_srf + WINDOW w AS (ORDER BY v ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) DEFINE B AS v > PREV(LAST(v, 1), k)) +$$ LANGUAGE sql STABLE; +SELECT t FROM rpr_explain_filter( + 'EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF) + SELECT g.n, max(s) + FROM (VALUES (1::int8), (3::int8), (9223372036854775807::int8)) g(n), + LATERAL rpr_exp_srf_cmp(g.n) s + GROUP BY g.n') AS t +WHERE t LIKE '%Nav Mark Lookback%'; + t +--------------------------------------------- + Nav Mark Lookback: retain all +(1 row) + +SELECT t FROM rpr_explain_filter( + 'EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF) + SELECT g.n, max(s) + FROM (VALUES (1::int8), (9223372036854775807::int8), (3::int8)) g(n), + LATERAL rpr_exp_srf_cmp(g.n) s + GROUP BY g.n') AS t +WHERE t LIKE '%Nav Mark Lookback%'; + t +------------------------------------ + Nav Mark Lookback: 4 +(1 row) + +DROP FUNCTION rpr_exp_srf_cmp(int8); +DROP TABLE rpr_exp_srf; diff --git a/src/test/regress/expected/rpr_integration.out b/src/test/regress/expected/rpr_integration.out index 5762f6411af..893f61290cc 100644 --- a/src/test/regress/expected/rpr_integration.out +++ b/src/test/regress/expected/rpr_integration.out @@ -410,9 +410,8 @@ SELECT count(*), sum(c) FROM ( -> WindowAgg Window: w AS (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a+" - Nav Mark Lookback: 0 -> Seq Scan on rpr_integ -(6 rows) +(5 rows) SELECT count(*), sum(c) FROM ( SELECT count(*) OVER w AS c FROM rpr_integ @@ -491,13 +490,12 @@ SELECT sum(c) FROM ( Output: NULL::integer, count(*) OVER w, rpr_integ.id, rpr_integ.* Window: w AS (ORDER BY rpr_integ.id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: a b+ - Nav Mark Lookback: 0 -> Sort Output: rpr_integ.id, rpr_integ.* Sort Key: rpr_integ.id -> Seq Scan on public.rpr_integ Output: rpr_integ.id, rpr_integ.* -(12 rows) +(11 rows) SELECT sum(c) FROM ( SELECT val, count(*) OVER w AS c FROM rpr_integ @@ -1634,7 +1632,6 @@ SELECT cnt FROM ( Output: rpr_over1.a, NULL::integer, count(*) OVER w Window: w AS (ORDER BY rpr_over1.a ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) Pattern: x+" - Nav Mark Lookback: 0 -> Sort Output: rpr_over1.a Sort Key: rpr_over1.a @@ -1644,9 +1641,124 @@ SELECT cnt FROM ( Output: rpr_over1.a -> Materialize -> Seq Scan on public.rpr_over2 -(16 rows) +(15 rows) DROP TABLE rpr_over1, rpr_over2; +-- A row pattern navigation offset that resolves to a correlated PARAM_EXEC +-- (here through SRF inlining of rpr_srf_prev(g.n)) must be re-resolved on every +-- rescan, not frozen at executor init. The inlined WindowAgg is the inner +-- side of a nestloop and is rescanned once per outer row, so each row sees its +-- own PREV(v, n) offset; a frozen offset would report the same value for all. +CREATE TABLE rpr_srf (v int); +INSERT INTO rpr_srf SELECT generate_series(1, 10); +CREATE FUNCTION rpr_srf_prev(k int) RETURNS SETOF bigint AS $$ + SELECT count(*) OVER w + FROM rpr_srf + WINDOW w AS (ORDER BY v ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) DEFINE A AS v > PREV(v, k)) +$$ LANGUAGE sql STABLE; +-- The offset reads "runtime"; the WindowAgg inlines into the nestloop and is +-- rescanned per outer row. +EXPLAIN (COSTS OFF) +SELECT g.n, max(s) FROM (VALUES (1), (2), (3)) g(n), LATERAL rpr_srf_prev(g.n) s +GROUP BY g.n ORDER BY g.n; + QUERY PLAN +-------------------------------------------------------------------------------------------------------- + Sort + Sort Key: "*VALUES*".column1 + -> HashAggregate + Group Key: "*VALUES*".column1 + -> Nested Loop + -> Values Scan on "*VALUES*" + -> WindowAgg + Window: w AS (ORDER BY rpr_srf.v ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + Pattern: a+" + Nav Mark Lookback: runtime + -> Sort + Sort Key: rpr_srf.v + -> Seq Scan on rpr_srf +(13 rows) + +-- Each outer row yields its own offset (9, 8, 7), not one frozen value. +SELECT g.n, max(s) AS m FROM (VALUES (1), (2), (3)) g(n), LATERAL rpr_srf_prev(g.n) s +GROUP BY g.n ORDER BY g.n; + n | m +---+--- + 1 | 9 + 2 | 8 + 3 | 7 +(3 rows) + +-- A forward FIRST-family offset with a correlated PARAM_EXEC must likewise be +-- re-resolved per scan (navFirstOffset / navFirstOffsetKind), not frozen at init. +-- PATTERN (B A+) anchors the match start at B so A can reference FIRST(v, k) +-- k rows ahead; each outer k yields its own forward offset (k=0 matches all +-- ten rows, k>=1 makes the first A fail), proving per-scan re-resolution. +CREATE FUNCTION rpr_srf_first(k int) RETURNS SETOF bigint AS $$ + SELECT count(*) OVER w + FROM rpr_srf + WINDOW w AS (ORDER BY v ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (B A+) DEFINE A AS v > FIRST(v, k)) +$$ LANGUAGE sql STABLE; +-- The forward offset reads "runtime" and the WindowAgg inlines into the nestloop. +EXPLAIN (COSTS OFF) +SELECT g.n, max(s) FROM (VALUES (0), (1), (2)) g(n), LATERAL rpr_srf_first(g.n) s +GROUP BY g.n ORDER BY g.n; + QUERY PLAN +-------------------------------------------------------------------------------------------------------- + Sort + Sort Key: "*VALUES*".column1 + -> HashAggregate + Group Key: "*VALUES*".column1 + -> Nested Loop + -> Values Scan on "*VALUES*" + -> WindowAgg + Window: w AS (ORDER BY rpr_srf.v ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) + Pattern: b a+ + Nav Mark Lookahead: runtime + -> Sort + Sort Key: rpr_srf.v + -> Seq Scan on rpr_srf +(13 rows) + +-- k=0 -> 10, k>=1 -> 0: distinct per outer row, so the forward offset is not +-- frozen at ExecInit. +SELECT g.n, max(s) AS m FROM (VALUES (0), (1), (2)) g(n), LATERAL rpr_srf_first(g.n) s +GROUP BY g.n ORDER BY g.n; + n | m +---+---- + 0 | 10 + 1 | 0 + 2 | 0 +(3 rows) + +DROP FUNCTION rpr_srf_first(int); +-- A compound navigation whose OUTER offset is a correlated PARAM_EXEC must be +-- re-resolved per scan as well. The last offset overflows int64, so that +-- scan's navigation has no target row at all: k=1 -> 9, k=3 -> 7, +-- k=overflow -> 0. Three answers from one plan is what says each rescan +-- resolved its own outer offset. The trim kind a scan settles on does not +-- show in a count; rpr_explain reads it out of EXPLAIN ANALYZE instead. +CREATE FUNCTION rpr_srf_cmp(k int8) RETURNS SETOF bigint AS $$ + SELECT count(*) OVER w + FROM rpr_srf + WINDOW w AS (ORDER BY v ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) DEFINE B AS v > PREV(LAST(v, 1), k)) +$$ LANGUAGE sql STABLE; +SELECT g.n, max(s) AS m +FROM (VALUES (1::int8), (3::int8), (9223372036854775807::int8)) g(n), + LATERAL rpr_srf_cmp(g.n) s +GROUP BY g.n ORDER BY g.n; + n | m +---------------------+--- + 1 | 9 + 3 | 7 + 9223372036854775807 | 0 +(3 rows) + +DROP FUNCTION rpr_srf_cmp(int8); +DROP FUNCTION rpr_srf_prev(int); +DROP TABLE rpr_srf; -- A correlated PARAM_EXEC used only inside DEFINE must reach the WindowAgg's -- extParam. Otherwise chgParam never gets to the HashAgg that DISTINCT plans -- above it, and its hash table for the first outer row is re-served. diff --git a/src/test/regress/sql/rpr.sql b/src/test/regress/sql/rpr.sql index 4796d42d9c2..ab8b3e64e18 100644 --- a/src/test/regress/sql/rpr.sql +++ b/src/test/regress/sql/rpr.sql @@ -740,11 +740,9 @@ WINDOW w AS ( -- 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! +-- PREV(price, 2): with A=any, B matches where the price beats the one two rows +-- back. On company1 (100, 200, 150, 140, 150, 90, 110, 130, 120, 130) that is +-- 200 -> 150, then 110 -> 130 -> 120, which stops where 130 only ties 130. SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w, count(*) OVER w FROM stock @@ -757,8 +755,9 @@ WINDOW w AS ( 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. +-- NEXT(price, 2): A matches while the price beats the one two rows ahead, so +-- company1 gives 200 on its own, since 150 only ties the 150 ahead of it, and +-- then 140, 150 up to where 90 falls short of 130. SELECT company, tdate, price, first_value(price) OVER w, last_value(price) OVER w, count(*) OVER w FROM stock @@ -882,8 +881,10 @@ EXECUTE test_prev_offset(-1); EXECUTE test_prev_offset(NULL); DEALLOCATE test_prev_offset; --- 2-arg PREV/NEXT: host variable with positive value --- Exercises RPR_NAV_OFFSET_NEEDS_EVAL -> eval_nav_max_offset() path +-- 2-arg PREV/NEXT: host variable with positive value. A generic plan keeps +-- the parameter as a Param, which is what reaches the RPR_NAV_OFFSET_NEEDS_EVAL +-- path; a custom plan would fold it to a Const and settle the reach at init. +SET plan_cache_mode = force_generic_plan; PREPARE test_prev_offset(int8) AS SELECT company, tdate, price, first_value(price) OVER w, count(*) OVER w FROM stock @@ -897,6 +898,7 @@ WINDOW w AS ( EXECUTE test_prev_offset(1); EXECUTE test_prev_offset(2); DEALLOCATE test_prev_offset; +RESET plan_cache_mode; -- 2-arg: two PREV with different offsets in same DEFINE clause -- B: price exceeds both 1-back and 2-back values @@ -1240,6 +1242,84 @@ SELECT id, val, count(*) OVER w FROM rpr_nav WINDOW w AS ( DEFINE A AS NEXT(LAST(val), -1) IS NULL ); +-- Compound: an out-of-range inner offset must not skip validation of the outer +-- one. All four arms resolve their outer offset through the same call, so each +-- appears once, and the negative and the null case take two arms apiece. +SELECT id, val, count(*) OVER w FROM rpr_nav WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE A AS TRUE, B AS PREV(FIRST(val, 99), -1) IS NULL +); +SELECT id, val, count(*) OVER w FROM rpr_nav WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE A AS TRUE, B AS PREV(LAST(val, 99), NULL::int8) IS NULL +); +SELECT id, val, count(*) OVER w FROM rpr_nav WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE A AS TRUE, B AS NEXT(FIRST(val, 99), NULL::int8) IS NULL +); +SELECT id, val, count(*) OVER w FROM rpr_nav WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE A AS TRUE, B AS NEXT(LAST(val, 99), -1) IS NULL +); + +-- Same with a host variable, where the offset is not a Const the planner can +-- fold: one prepared statement, and only the outer offset decides the outcome. +-- The reach reads "runtime" here; a custom plan would fold it to 99 - 1 = 98. +SET plan_cache_mode = force_generic_plan; +PREPARE test_compound_illegal(int8, int8) AS +SELECT id, val, count(*) OVER w FROM rpr_nav WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) + DEFINE A AS TRUE, B AS PREV(FIRST(val, $1), $2) IS NULL +); +EXPLAIN (COSTS OFF) EXECUTE test_compound_illegal(99, 1); +EXECUTE test_compound_illegal(99, 1); +EXECUTE test_compound_illegal(99, -1); +EXECUTE test_compound_illegal(99, NULL); +EXECUTE test_compound_illegal(0, -1); +DEALLOCATE test_compound_illegal; +RESET plan_cache_mode; + +-- An offset is settled before the first row is fetched, so a partition with no +-- rows at all rejects an illegal one just the same, and a legal one returns no +-- rows rather than failing. +CREATE TABLE rpr_nav_empty (id int, val int); +SELECT id, count(*) OVER w FROM rpr_nav_empty WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS PREV(val, -1) IS NULL +); +SELECT id, count(*) OVER w FROM rpr_nav_empty WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS PREV(val, 1) IS NULL +); +SET plan_cache_mode = force_generic_plan; +PREPARE test_empty_offset(int8) AS +SELECT id, count(*) OVER w FROM rpr_nav_empty WINDOW w AS ( + ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS PREV(val, $1) IS NULL +); +EXECUTE test_empty_offset(-1); +EXECUTE test_empty_offset(NULL); +EXECUTE test_empty_offset(1); +DEALLOCATE test_empty_offset; +RESET plan_cache_mode; +DROP TABLE rpr_nav_empty; + -- Outer offset overflows int64: target position out of range -> NULL. -- Plain NEXT(val, INT64_MAX): currentpos + INT64_MAX overflows. SELECT id, val, count(*) OVER w FROM rpr_nav WINDOW w AS ( diff --git a/src/test/regress/sql/rpr_base.sql b/src/test/regress/sql/rpr_base.sql index 4842581b859..18a42535ad0 100644 --- a/src/test/regress/sql/rpr_base.sql +++ b/src/test/regress/sql/rpr_base.sql @@ -1285,6 +1285,46 @@ SELECT format($$SELECT count(*) OVER w FROM (SELECT 1 i) t -- ============================================================ -- Navigation Functions Tests (PREV / NEXT / FIRST / LAST) -- ============================================================ +CREATE TEMP TABLE rpr_nav0 (id int, v int); +INSERT INTO rpr_nav0 SELECT g, g*10 FROM generate_series(1, 5) g; + +-- Two concurrently open portals of the SAME cached generic plan, with different +-- offset parameters. +-- +-- The parameterized cursor 'c' compiles to one plpgsql statement -> one SPI +-- cached plan. The recursive call OPENs a second portal of that same plan +-- (with a different offset) while the outer portal is already started but has +-- not yet FETCHed. +CREATE OR REPLACE FUNCTION rpr_nested(p_off int, depth int) +RETURNS SETOF text LANGUAGE plpgsql AS $$ +DECLARE + c CURSOR (o int) FOR + SELECT id, count(*) OVER w AS cnt + FROM rpr_nav0 + WINDOW w AS (ORDER BY id + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A) + DEFINE A AS PREV(v, o) IS NULL); + r record; +BEGIN + OPEN c(p_off); + IF depth > 0 THEN + RETURN QUERY SELECT * FROM rpr_nested(p_off + 2, depth - 1); + END IF; + + LOOP + FETCH c INTO r; + EXIT WHEN NOT FOUND; + RETURN NEXT format('off=%s id=%s cnt=%s', p_off, r.id, r.cnt); + END LOOP; + CLOSE c; +END $$; + +SET plan_cache_mode = force_generic_plan; +SELECT * FROM rpr_nested(1, 1); +RESET plan_cache_mode; + +DROP FUNCTION rpr_nested(int, int); CREATE TABLE rpr_nav (id INT, val INT); INSERT INTO rpr_nav VALUES @@ -4500,7 +4540,7 @@ DROP TABLE rpr_sort; CREATE TABLE rpr_srf_t (v int); INSERT INTO rpr_srf_t SELECT generate_series(1, 5); -CREATE FUNCTION rpr_srf_f(threshold int) +CREATE FUNCTION rpr_srf_inline(threshold int) RETURNS TABLE (v int, cnt bigint) LANGUAGE sql STABLE AS $$ SELECT v::int, count(*) OVER w @@ -4513,10 +4553,10 @@ LANGUAGE sql STABLE AS $$ ) $$; -SELECT v, cnt FROM rpr_srf_f(3) ORDER BY v; +SELECT v, cnt FROM rpr_srf_inline(3) ORDER BY v; DROP TABLE rpr_srf_t; -DROP FUNCTION rpr_srf_f(int); +DROP FUNCTION rpr_srf_inline(int); DROP TABLE rpr_planner; diff --git a/src/test/regress/sql/rpr_explain.sql b/src/test/regress/sql/rpr_explain.sql index 01bb7ff36ef..4246c559d35 100644 --- a/src/test/regress/sql/rpr_explain.sql +++ b/src/test/regress/sql/rpr_explain.sql @@ -1265,6 +1265,53 @@ WINDOW w AS ( DEFINE A AS v % 2 = 1, B AS v % 2 = 0 )'); +-- Absorbed contexts and a navigation, neither of which the case above emits, +-- so without this the XML spelling of the absorbed length group and of Nav +-- Mark goes unchecked. +CREATE VIEW rpr_ev_xml_absorb AS +SELECT count(*) OVER w +FROM generate_series(1, 100) 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 v % 10 <> 0 AND PREV(v) IS NOT NULL, B AS v % 10 = 0 +); +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_xml_absorb'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT rpr_explain_filter(' +EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF, FORMAT XML) +SELECT count(*) OVER w +FROM generate_series(1, 100) 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 v % 10 <> 0 AND PREV(v) IS NOT NULL, B AS v % 10 = 0 +)'); + +-- A pattern that mismatches at all, which the two cases above never do, so +-- this is where the mismatch length group appears in XML. +CREATE VIEW rpr_ev_xml_mismatch AS +SELECT count(*) OVER w +FROM generate_series(1, 40) AS s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + PATTERN ((A | B){2,4}) + DEFINE A AS v % 2 = 1, B AS v % 3 = 0 +); +SELECT line FROM unnest(string_to_array(pg_get_viewdef('rpr_ev_xml_mismatch'), E'\n')) AS line WHERE line ~ 'PATTERN'; +SELECT rpr_explain_filter(' +EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF, FORMAT XML) +SELECT count(*) OVER w +FROM generate_series(1, 40) AS s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + AFTER MATCH SKIP PAST LAST ROW + PATTERN ((A | B){2,4}) + DEFINE A AS v % 2 = 1, B AS v % 3 = 0 +)'); + -- ============================================================ -- Multiple Partitions Tests -- ============================================================ @@ -3424,6 +3471,16 @@ WINDOW w AS ( DEFINE A AS v > PREV(v, 3) ); +-- PREV(v, 1 + 1): a foldable offset has to arrive as a constant, or the trim +-- bound would print "runtime" instead of 2 +EXPLAIN (COSTS OFF) SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS v > PREV(v, 1 + 1) +); + -- Two PREV with different offsets: max(1, 5) = 5 EXPLAIN (COSTS OFF) SELECT count(*) OVER w FROM generate_series(1,10) s(v) @@ -3442,6 +3499,18 @@ EXPLAIN (COSTS OFF) EXECUTE rpr_nav_offset_prep(2); RESET plan_cache_mode; DEALLOCATE rpr_nav_offset_prep; +-- EXPLAIN (GENERIC_PLAN) of an unbound parameter offset must not evaluate the +-- parameter: the offset stays "runtime" instead of failing with "no value +-- found for parameter 1". +EXPLAIN (GENERIC_PLAN, COSTS OFF) +SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS v > PREV(v, $1) +); + -- FIRST(v): retain all (references match_start row) EXPLAIN (COSTS OFF) SELECT count(*) OVER w FROM generate_series(1,10) s(v) @@ -3451,6 +3520,33 @@ WINDOW w AS ( DEFINE A AS v > FIRST(v) ); +-- FIRST(v, 5): forward reach 5 +EXPLAIN (COSTS OFF) SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS v > FIRST(v, 5) +); + +-- The same forward reach in the XML and JSON forms. Only the text form is +-- exercised elsewhere, so the tag itself has no coverage. +EXPLAIN (COSTS OFF, FORMAT XML) SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS v > FIRST(v, 5) +); + +EXPLAIN (COSTS OFF, FORMAT JSON) SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS v > FIRST(v, 5) +); + -- LAST(v, 1): backward reach 1, same as PREV(v, 1) EXPLAIN (COSTS OFF) SELECT count(*) OVER w FROM generate_series(1,10) s(v) @@ -3505,6 +3601,41 @@ WINDOW w AS ( DEFINE A AS NEXT(LAST(v, 1), 3) > 0 ); +-- Compound forms with the outer offset left out, which defaults to 1: each of +-- the four arms combines it with the inner offset differently, and the value +-- decides both the trim bound and the row the navigation lands on. +EXPLAIN (COSTS OFF) SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS PREV(FIRST(v, 2)) > 0 +); + +EXPLAIN (COSTS OFF) SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS NEXT(FIRST(v, 2)) > 0 +); + +EXPLAIN (COSTS OFF) SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS PREV(LAST(v, 2)) > 0 +); + +EXPLAIN (COSTS OFF) SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS NEXT(LAST(v, 2)) > 0 +); + -- Compound PREV(LAST(val, N), M): constant near-overflow (N+M just fits int64) EXPLAIN (COSTS OFF) SELECT count(*) OVER w FROM generate_series(1,10) s(v) @@ -3533,6 +3664,97 @@ WINDOW w AS ( DEFINE A AS NEXT(FIRST(v, 4611686018427387904), 4611686018427387904) IS NOT NULL ); +-- A navigation with a negative offset cannot run, so it contributes no reach +-- and its dimension reports nothing at all. +EXPLAIN (COSTS OFF) SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS PREV(FIRST(v, -3), 2) IS NOT NULL +); + +-- The same query errors once it runs, since execution validates the offset. +EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS PREV(FIRST(v, -3), 2) IS NOT NULL +); + +-- Same at the int64 limit, where the reach subtraction would otherwise wrap. +EXPLAIN (COSTS OFF) SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS PREV(FIRST(v, (-9223372036854775807)::int8), 2) IS NOT NULL +); + +-- The other dimension keeps its own aggregate. +EXPLAIN (COSTS OFF) SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS PREV(v, 5) IS NOT NULL AND FIRST(v, -1) IS NOT NULL +); + +-- A null offset cannot run either, so it too contributes no reach. Resolving +-- it to a placeholder 0 would instead let it join the aggregate and displace +-- the offset of the navigation that can run. +EXPLAIN (COSTS OFF) SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS FIRST(v, 7) IS NOT NULL AND PREV(FIRST(v, NULL::int), 2) IS NOT NULL +); + +-- And it errors once it runs, for the same reason the negative one does. +EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF) SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS FIRST(v, 7) IS NOT NULL AND PREV(FIRST(v, NULL::int), 2) IS NOT NULL +); + +-- Dropping a navigation must not disturb the kind the survivor reports. An +-- overflowing lookback still retains every row. +EXPLAIN (COSTS OFF) SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS FIRST(v, -1) IS NOT NULL + AND PREV(LAST(v, 4611686018427387904), 4611686018427387904) IS NOT NULL +); + +-- And a parameter offset beside a dropped one is still settled per scan. +PREPARE test_dropped_with_runtime(int8) AS +SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS FIRST(v, -1) IS NOT NULL AND PREV(v, $1) IS NOT NULL +); +SET plan_cache_mode = force_generic_plan; +EXPLAIN (COSTS OFF) EXECUTE test_dropped_with_runtime(2); +RESET plan_cache_mode; +DEALLOCATE test_dropped_with_runtime; + +-- NEXT(LAST()) reaches the same subtraction on the lookback side. +EXPLAIN (COSTS OFF) SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS NEXT(LAST(v, 2), (-9223372036854775807)::int8) IS NOT NULL +); + -- Compound PREV(LAST(val, $1), $2): parameter lookback overflow -> retain all -- EXPLAIN shows "runtime" (plan-level); EXPLAIN ANALYZE shows "retain all" -- (executor-resolved). @@ -3584,10 +3806,10 @@ EXPLAIN (COSTS OFF) EXECUTE p_first_runtime(1, 1); RESET plan_cache_mode; DEALLOCATE p_first_runtime; --- PREV(v) + PREV(v, $1): NEEDS_EVAL path must account for implicit lookback=1 --- Previously, eval_nav_max_offset_walker skipped PREV(v) when offset_arg was --- NULL, causing maxOffset=0 when $1=0, which would trim the row needed by --- PREV(v). Verify this executes without "cannot fetch row before mark" error. +-- PREV(v) + PREV(v, $1): the implicit lookback of 1 has to count even when the +-- explicit offset resolves to 0, or PREV(v) would fail with "cannot fetch row +-- before mark". A generic plan settles the reach per scan instead of at init. +SET plan_cache_mode = force_generic_plan; PREPARE test_prev_implicit_offset(int8) AS SELECT count(*) OVER w FROM generate_series(1,10) s(v) @@ -3598,11 +3820,12 @@ WINDOW w AS ( ); EXECUTE test_prev_implicit_offset(0); DEALLOCATE test_prev_implicit_offset; +RESET plan_cache_mode; -- NEEDS_EVAL executor offset paths: a Param nav offset stays non-Const under a --- generic plan, so the planner marks the offset NEEDS_EVAL and the executor --- resolves it at init via eval_define_offsets -> visit_nav_exec. Each query --- below exercises a different navigation arm of that walker. +-- generic plan, so build_define_offsets() marks the offset NEEDS_EVAL and +-- resolve_nav_offsets() settles it once per scan. Each query below exercises +-- a different navigation arm of that walker. -- Simple FIRST(v, $1): forward-reach FIRST arm. PREPARE test_eval_first(int8) AS @@ -3657,6 +3880,13 @@ WINDOW w AS ( ); SET plan_cache_mode = force_generic_plan; EXECUTE test_eval_prevfirst(1, 1); +-- Observe the arm rather than only run it. At plan time the line reads +-- "runtime"; once resolved it reads inner - outer, so 2 here is the PREV_FIRST +-- subtraction. A bare FIRST would report the inner offset alone. +EXPLAIN (COSTS OFF) EXECUTE test_eval_prevfirst(3, 1); +SELECT rpr_explain_filter(' +EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF) +EXECUTE test_eval_prevfirst(3, 1);'); RESET plan_cache_mode; DEALLOCATE test_eval_prevfirst; @@ -3672,6 +3902,22 @@ WINDOW w AS ( EXECUTE test_runtime_neg_offset(-1); DEALLOCATE test_runtime_neg_offset; +-- The same at generic-plan resolution time, and for each half of a compound +-- navigation on its own. +PREPARE test_runtime_neg_compound_offset(int8, int8) AS +SELECT count(*) OVER w +FROM generate_series(1,10) s(v) +WINDOW w AS ( + ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) + DEFINE A AS NEXT(FIRST(v, $1), $2) IS NOT NULL +); +SET plan_cache_mode = force_generic_plan; +EXECUTE test_runtime_neg_compound_offset(1, -1); +EXECUTE test_runtime_neg_compound_offset(-1, 1); +RESET plan_cache_mode; +DEALLOCATE test_runtime_neg_compound_offset; + -- Runtime error: null offset at execution time PREPARE test_runtime_null_offset(int8) AS SELECT count(*) OVER w @@ -3683,3 +3929,50 @@ WINDOW w AS ( ); EXECUTE test_runtime_null_offset(NULL); DEALLOCATE test_runtime_null_offset; + +-- A correlated PARAM_EXEC nav offset (reaching the offset via SRF inlining) is +-- resolved per scan by resolve_nav_offsets(); after execution EXPLAIN ANALYZE +-- must display the concrete resolved bound (a number), not "runtime" -- that is, +-- navMaxOffsetKind resolves to FIXED. Plain EXPLAIN of the same query shows +-- "runtime"; only ANALYZE exercises the per-scan clear. +CREATE TABLE rpr_exp_srf (v int); +INSERT INTO rpr_exp_srf SELECT generate_series(1, 10); +CREATE FUNCTION rpr_exp_srf_f(k int) RETURNS SETOF bigint AS $$ + SELECT count(*) OVER w + FROM rpr_exp_srf + WINDOW w AS (ORDER BY v ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) DEFINE A AS v > PREV(v, k)) +$$ LANGUAGE sql STABLE; +SELECT t FROM rpr_explain_filter( + 'EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF) + SELECT g.n, max(s) FROM (VALUES (2), (2)) g(n), LATERAL rpr_exp_srf_f(g.n) s + GROUP BY g.n') AS t +WHERE t LIKE '%Nav Mark Lookback%'; +DROP FUNCTION rpr_exp_srf_f(int); +-- The kind a scan settles on is per scan, not sticky. An outer offset that +-- overflows int64 gives up on the trim for that scan alone, and EXPLAIN +-- ANALYZE reports what the last rescan left behind: the same three offsets +-- in a different order have to read differently, retain all when the +-- overflow runs last and a bound again when a smaller offset follows it. +CREATE FUNCTION rpr_exp_srf_cmp(k int8) RETURNS SETOF bigint AS $$ + SELECT count(*) OVER w + FROM rpr_exp_srf + WINDOW w AS (ORDER BY v ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) DEFINE B AS v > PREV(LAST(v, 1), k)) +$$ LANGUAGE sql STABLE; +SELECT t FROM rpr_explain_filter( + 'EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF) + SELECT g.n, max(s) + FROM (VALUES (1::int8), (3::int8), (9223372036854775807::int8)) g(n), + LATERAL rpr_exp_srf_cmp(g.n) s + GROUP BY g.n') AS t +WHERE t LIKE '%Nav Mark Lookback%'; +SELECT t FROM rpr_explain_filter( + 'EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS OFF) + SELECT g.n, max(s) + FROM (VALUES (1::int8), (9223372036854775807::int8), (3::int8)) g(n), + LATERAL rpr_exp_srf_cmp(g.n) s + GROUP BY g.n') AS t +WHERE t LIKE '%Nav Mark Lookback%'; +DROP FUNCTION rpr_exp_srf_cmp(int8); +DROP TABLE rpr_exp_srf; diff --git a/src/test/regress/sql/rpr_integration.sql b/src/test/regress/sql/rpr_integration.sql index 8e9049ffee2..ae437eb356d 100644 --- a/src/test/regress/sql/rpr_integration.sql +++ b/src/test/regress/sql/rpr_integration.sql @@ -1039,6 +1039,69 @@ SELECT cnt FROM ( ) s; DROP TABLE rpr_over1, rpr_over2; +-- A row pattern navigation offset that resolves to a correlated PARAM_EXEC +-- (here through SRF inlining of rpr_srf_prev(g.n)) must be re-resolved on every +-- rescan, not frozen at executor init. The inlined WindowAgg is the inner +-- side of a nestloop and is rescanned once per outer row, so each row sees its +-- own PREV(v, n) offset; a frozen offset would report the same value for all. +CREATE TABLE rpr_srf (v int); +INSERT INTO rpr_srf SELECT generate_series(1, 10); +CREATE FUNCTION rpr_srf_prev(k int) RETURNS SETOF bigint AS $$ + SELECT count(*) OVER w + FROM rpr_srf + WINDOW w AS (ORDER BY v ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A+) DEFINE A AS v > PREV(v, k)) +$$ LANGUAGE sql STABLE; +-- The offset reads "runtime"; the WindowAgg inlines into the nestloop and is +-- rescanned per outer row. +EXPLAIN (COSTS OFF) +SELECT g.n, max(s) FROM (VALUES (1), (2), (3)) g(n), LATERAL rpr_srf_prev(g.n) s +GROUP BY g.n ORDER BY g.n; +-- Each outer row yields its own offset (9, 8, 7), not one frozen value. +SELECT g.n, max(s) AS m FROM (VALUES (1), (2), (3)) g(n), LATERAL rpr_srf_prev(g.n) s +GROUP BY g.n ORDER BY g.n; + +-- A forward FIRST-family offset with a correlated PARAM_EXEC must likewise be +-- re-resolved per scan (navFirstOffset / navFirstOffsetKind), not frozen at init. +-- PATTERN (B A+) anchors the match start at B so A can reference FIRST(v, k) +-- k rows ahead; each outer k yields its own forward offset (k=0 matches all +-- ten rows, k>=1 makes the first A fail), proving per-scan re-resolution. +CREATE FUNCTION rpr_srf_first(k int) RETURNS SETOF bigint AS $$ + SELECT count(*) OVER w + FROM rpr_srf + WINDOW w AS (ORDER BY v ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (B A+) DEFINE A AS v > FIRST(v, k)) +$$ LANGUAGE sql STABLE; +-- The forward offset reads "runtime" and the WindowAgg inlines into the nestloop. +EXPLAIN (COSTS OFF) +SELECT g.n, max(s) FROM (VALUES (0), (1), (2)) g(n), LATERAL rpr_srf_first(g.n) s +GROUP BY g.n ORDER BY g.n; +-- k=0 -> 10, k>=1 -> 0: distinct per outer row, so the forward offset is not +-- frozen at ExecInit. +SELECT g.n, max(s) AS m FROM (VALUES (0), (1), (2)) g(n), LATERAL rpr_srf_first(g.n) s +GROUP BY g.n ORDER BY g.n; +DROP FUNCTION rpr_srf_first(int); + +-- A compound navigation whose OUTER offset is a correlated PARAM_EXEC must be +-- re-resolved per scan as well. The last offset overflows int64, so that +-- scan's navigation has no target row at all: k=1 -> 9, k=3 -> 7, +-- k=overflow -> 0. Three answers from one plan is what says each rescan +-- resolved its own outer offset. The trim kind a scan settles on does not +-- show in a count; rpr_explain reads it out of EXPLAIN ANALYZE instead. +CREATE FUNCTION rpr_srf_cmp(k int8) RETURNS SETOF bigint AS $$ + SELECT count(*) OVER w + FROM rpr_srf + WINDOW w AS (ORDER BY v ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING + PATTERN (A B+) DEFINE B AS v > PREV(LAST(v, 1), k)) +$$ LANGUAGE sql STABLE; +SELECT g.n, max(s) AS m +FROM (VALUES (1::int8), (3::int8), (9223372036854775807::int8)) g(n), + LATERAL rpr_srf_cmp(g.n) s +GROUP BY g.n ORDER BY g.n; +DROP FUNCTION rpr_srf_cmp(int8); +DROP FUNCTION rpr_srf_prev(int); +DROP TABLE rpr_srf; + -- A correlated PARAM_EXEC used only inside DEFINE must reach the WindowAgg's -- extParam. Otherwise chgParam never gets to the HashAgg that DISTINCT plans -- above it, and its hash table for the first outer row is re-served. diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list index be2eda8adc3..601876d7b04 100644 --- a/src/tools/pgindent/typedefs.list +++ b/src/tools/pgindent/typedefs.list @@ -1843,8 +1843,6 @@ NamedLWLockTrancheRequest NamedTuplestoreScan NamedTuplestoreScanState NamespaceInfo -NavTraversal -NavVisitFn NestLoop NestLoopParam NestLoopState @@ -2545,6 +2543,8 @@ RPRNFAState RPRNavExpr RPRNavKind RPRNavOffsetKind +RPRNavOffsets +RPRNavState RPRPattern RPRPatternElement RPRPatternNode