MIME-Version: 1.0
References: 
 <CAAAe_zBswm=JGsBi3Hn-8kVbH3LgAcOvWJ3nnoAeWbiwR7NOig@mail.gmail.com>
 <20260312.105500.450470685315646651.ishii@postgresql.org>
 <CAAAe_zC001P9_rpKwAiq=+hOvTV_orBchbzipZnRRzdU-UWg1A@mail.gmail.com>
 <20260314.164003.2119775273148039288.ishii@postgresql.org>
In-Reply-To: <20260314.164003.2119775273148039288.ishii@postgresql.org>
Reply-To: assam258@gmail.com
From: Henson Choi <assam258@gmail.com>
Date: Sat, 14 Mar 2026 23:14:26 +0900
Message-ID: 
 <CAAAe_zDrCxodQG+ybLnUqo7t6e8g9kU7PEZfSF_KnibUe+Dh+g@mail.gmail.com>
Subject: Re: Row pattern recognition
To: Tatsuo Ishii <ishii@postgresql.org>
Cc: zsolt.parragi@percona.com, vik@postgresfriends.org, er@xs4all.nl,
	jacob.champion@enterprisedb.com, david.g.johnston@gmail.com,
	peter@eisentraut.org, pgsql-hackers@postgresql.org
Content-Type: multipart/alternative; boundary="0000000000003b6283064cfc9bb3"
Archived-At: 
 <https://www.postgresql.org/message-id/CAAAe_zDrCxodQG%2BybLnUqo7t6e8g9kU7PEZfSF_KnibUe%2BDh%2Bg%40mail.gmail.com>
Precedence: bulk

--0000000000003b6283064cfc9bb3
Content-Type: text/plain; charset="UTF-8"

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

--0000000000003b6283064cfc9bb3
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><div dir=3D"ltr">Hi Tatsuo,</div><div dir=
=3D"ltr"><br></div><div class=3D"gmail_quote"><blockquote class=3D"gmail_qu=
ote" style=3D"margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-st=
yle:solid;border-left-color:rgb(204,204,204);padding-left:1ex">
Looks great. Do you have any idea how to let the existing ExecEvalExpr<br>
handle the swap/restore mechanism?</blockquote><div><br></div><br>Yes. I th=
ink the key insight is that ExecEvalExpr compiles expressions into<br>a fla=
t array of ExprEvalStep operations, which makes the swap/restore natural<br=
>to implement without any changes to the ExecEvalExpr caller interface.<br>=
<br>I should also say that this approach was shaped by the constraints you<=
br>identified early on -- the three-slot model&#39;s limitations and the ne=
ed<br>for variable offsets. Without that clear problem framing, I would not=
<br>have thought in this direction.<br><br>The idea is to introduce two new=
 ExprEvalOp steps:<br><br><font face=3D"monospace">=C2=A0 EEOP_RPR_NAV_SET =
=C2=A0 =C2=A0 -- save current slot, swap to target row<br>=C2=A0 EEOP_RPR_N=
AV_RESTORE -- restore original slot<br><br></font>These two steps bracket t=
he inner expression steps in the flat array.<br><br><font face=3D"monospace=
">For `price &gt; PREV(price)`:<br>=C2=A0 1. EEOP_OUTER_VAR =C2=A0 =C2=A0 =
=C2=A0-- fetch price from current slot --&gt; datum_a<br>=C2=A0 2. EEOP_RPR=
_NAV_SET =C2=A0 =C2=A0-- save current slot, swap to (currentpos - 1)<br>=C2=
=A0 3. EEOP_OUTER_VAR =C2=A0 =C2=A0 =C2=A0-- fetch price from swapped slot =
--&gt; datum_b<br>=C2=A0 4. EEOP_RPR_NAV_RESTORE -- restore original slot<b=
r>=C2=A0 5. EEOP_GT =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 -- evaluate d=
atum_a &gt; datum_b<br>=C2=A0 6. EEOP_DONE<br><br>For `price &gt; PREV(pric=
e, 3)` (with offset):<br>=C2=A0 1. EEOP_OUTER_VAR =C2=A0 =C2=A0 =C2=A0-- fe=
tch price from current slot --&gt; datum_a<br>=C2=A0 2. EEOP_CONST =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0-- evaluate offset constant 3 --&gt; datum_off<b=
r>=C2=A0 3. EEOP_RPR_NAV_SET =C2=A0 =C2=A0-- save current slot, swap to (cu=
rrentpos - 3)<br>=C2=A0 4. EEOP_OUTER_VAR =C2=A0 =C2=A0 =C2=A0-- fetch pric=
e from swapped slot --&gt; datum_b<br>=C2=A0 5. EEOP_RPR_NAV_RESTORE -- res=
tore original slot<br>=C2=A0 6. EEOP_GT =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 -- evaluate datum_a &gt; datum_b<br>=C2=A0 7. EEOP_DONE<br><br>For `=
price &lt; NEXT(price)`:<br>=C2=A0 1. EEOP_OUTER_VAR =C2=A0 =C2=A0 =C2=A0--=
 fetch price from current slot --&gt; datum_a<br>=C2=A0 2. EEOP_RPR_NAV_SET=
 =C2=A0 =C2=A0-- save current slot, swap to (currentpos + 1)<br>=C2=A0 3. E=
EOP_OUTER_VAR =C2=A0 =C2=A0 =C2=A0-- fetch price from swapped slot --&gt; d=
atum_b<br>=C2=A0 4. EEOP_RPR_NAV_RESTORE -- restore original slot<br>=C2=A0=
 5. EEOP_LT =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 -- evaluate datum_a &=
lt; datum_b<br>=C2=A0 6. EEOP_DONE<br></font><br>ExprState holds the flat a=
rray of ExprEvalSteps, and each step carries<br>its own payload. The RPR na=
vigation steps would store a pointer to the<br>window execution state in th=
eir payload, so that they can access the<br>current row position and the tu=
plestore at evaluation time. Since<br>PREV/NEXT are only permitted in the D=
EFINE clause, the window execution<br>state is always valid when these step=
s are evaluated, which eliminates<br>the risk of accessing stale or invalid=
 state.<br><br>The EEOP_RPR_NAV_SET step would:<br>=C2=A0 1. Save the curre=
nt slot for later restore<br>=C2=A0 2. Compute the target position from cur=
rentpos and the direction offset<br>=C2=A0 3. Fetch the corresponding Tuple=
TableSlot from the tuplestore<br>=C2=A0 4. Replace econtext-&gt;ecxt_outert=
uple with the target slot<br><br>The EEOP_RPR_NAV_RESTORE step would:<br>=
=C2=A0 1. Retrieve the previously saved slot<br>=C2=A0 2. Restore econtext-=
&gt;ecxt_outertuple to the original slot<br><br>This design also fits well =
with the future roadmap for FIRST/LAST navigation.<br>Once the match histor=
y infrastructure is in place, FIRST/LAST can look<br>up the first or last r=
ow matched by a given pattern variable from the<br>history, compute the tar=
get position from that, and pass it to<br>EEOP_RPR_NAV_SET -- the save/rest=
ore mechanism itself stays the same.<br><br>I would like to work on an expe=
rimental implementation of this approach,<br>including the parser-level che=
ck to reject nested PREV/NEXT calls as<br>required by the standard. The nes=
ting check will be part of the same<br>implementation rather than a separat=
e fix, as the parser structure may<br>need to be adjusted as part of the re=
design anyway.<br><br>Since the expression compilation infrastructure is no=
t an area I am<br>deeply familiar with, it may take some time to get right.=
 I would<br>appreciate your patience in reviewing it when it is ready.<br><=
br>Best regards,<br>Henson=C2=A0</div></div>
</div>

--0000000000003b6283064cfc9bb3--