Re: Row pattern recognition

Hi Tatsuo,

Looks great. Do you have any idea how to let the existing ExecEvalExpr
> handle the swap/restore mechanism?



Yes. I think the key insight is that ExecEvalExpr compiles expressions into
a flat array of ExprEvalStep operations, which makes the swap/restore
natural
to implement without any changes to the ExecEvalExpr caller interface.

I should also say that this approach was shaped by the constraints you
identified early on -- the three-slot model's limitations and the need
for variable offsets. Without that clear problem framing, I would not
have thought in this direction.

The idea is to introduce two new ExprEvalOp steps:

  EEOP_RPR_NAV_SET     -- save current slot, swap to target row
  EEOP_RPR_NAV_RESTORE -- restore original slot

These two steps bracket the inner expression steps in the flat array.

For `price > PREV(price)`:
  1. EEOP_OUTER_VAR      -- fetch price from current slot --> datum_a
  2. EEOP_RPR_NAV_SET    -- save current slot, swap to (currentpos - 1)
  3. EEOP_OUTER_VAR      -- fetch price from swapped slot --> datum_b
  4. EEOP_RPR_NAV_RESTORE -- restore original slot
  5. EEOP_GT             -- evaluate datum_a > datum_b
  6. EEOP_DONE

For `price > PREV(price, 3)` (with offset):
  1. EEOP_OUTER_VAR      -- fetch price from current slot --> datum_a
  2. EEOP_CONST          -- evaluate offset constant 3 --> datum_off
  3. EEOP_RPR_NAV_SET    -- save current slot, swap to (currentpos - 3)
  4. EEOP_OUTER_VAR      -- fetch price from swapped slot --> datum_b
  5. EEOP_RPR_NAV_RESTORE -- restore original slot
  6. EEOP_GT             -- evaluate datum_a > datum_b
  7. EEOP_DONE

For `price < NEXT(price)`:
  1. EEOP_OUTER_VAR      -- fetch price from current slot --> datum_a
  2. EEOP_RPR_NAV_SET    -- save current slot, swap to (currentpos + 1)
  3. EEOP_OUTER_VAR      -- fetch price from swapped slot --> datum_b
  4. EEOP_RPR_NAV_RESTORE -- restore original slot
  5. EEOP_LT             -- evaluate datum_a < datum_b
  6. EEOP_DONE

ExprState holds the flat array of ExprEvalSteps, and each step carries
its own payload. The RPR navigation steps would store a pointer to the
window execution state in their payload, so that they can access the
current row position and the tuplestore at evaluation time. Since
PREV/NEXT are only permitted in the DEFINE clause, the window execution
state is always valid when these steps are evaluated, which eliminates
the risk of accessing stale or invalid state.

The EEOP_RPR_NAV_SET step would:
  1. Save the current slot for later restore
  2. Compute the target position from currentpos and the direction offset
  3. Fetch the corresponding TupleTableSlot from the tuplestore
  4. Replace econtext->ecxt_outertuple with the target slot

The EEOP_RPR_NAV_RESTORE step would:
  1. Retrieve the previously saved slot
  2. Restore econtext->ecxt_outertuple to the original slot

This design also fits well with the future roadmap for FIRST/LAST
navigation.
Once the match history infrastructure is in place, FIRST/LAST can look
up the first or last row matched by a given pattern variable from the
history, compute the target position from that, and pass it to
EEOP_RPR_NAV_SET -- the save/restore mechanism itself stays the same.

I would like to work on an experimental implementation of this approach,
including the parser-level check to reject nested PREV/NEXT calls as
required by the standard. The nesting check will be part of the same
implementation rather than a separate fix, as the parser structure may
need to be adjusted as part of the redesign anyway.

Since the expression compilation infrastructure is not an area I am
deeply familiar with, it may take some time to get right. I would
appreciate your patience in reviewing it when it is ready.

Best regards,
Henson