MIME-Version: 1.0
References: <20260319.121550.1075916211106201460.ishii@postgresql.org>
 <20260319.191035.328225166092779523.ishii@postgresql.org>
 <CAAAe_zBbrnx2fjK2s+Jgx6TSOdnKAPawXbHeX49WqmX9ji+Hdg@mail.gmail.com>
 <20260321.140232.1947157589839395257.ishii@postgresql.org>
In-Reply-To: <20260321.140232.1947157589839395257.ishii@postgresql.org>
Reply-To: assam258@gmail.com
From: Henson Choi <assam258@gmail.com>
Date: Sat, 21 Mar 2026 21:42:52 +0900
Message-ID: 
 <CAAAe_zDmjV-R6M0xeMDzZN+eRVUhsrt6NA=xjvj9BQMog9tqTQ@mail.gmail.com>
Subject: Re: Row pattern recognition
To: Tatsuo Ishii <ishii@postgresql.org>
Cc: zsolt.parragi@percona.com, sjjang112233@gmail.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="0000000000008daba4064d8824da"
Archived-At: 
 <https://www.postgresql.org/message-id/CAAAe_zDmjV-R6M0xeMDzZN%2BeRVUhsrt6NA%3Dxjvj9BQMog9tqTQ%40mail.gmail.com>
Precedence: bulk

--0000000000008daba4064d8824da
Content-Type: text/plain; charset="UTF-8"

Hi Tatsuo,

>   0004: Fix window_last_value set_mark during RPR
> >         Restore set_mark=true in window_last_value (normal behavior)
> >         and add an RPR-specific override inside WinGetFuncArgInFrame.
> >         This keeps the RPR workaround localized rather than changing
> >         the caller's semantics unconditionally.
>
> So, if RPR is active, WinSetMarkPosition() is not called at all?  That
> seems too strong limitation.  Can't we set mark at frameheadpos even
> if RPR is active? It seems safe since the only allowed franme start
> option is ROWS BETWEEN CURRENT ROW and we never step back to the rows
> before frameheadpos.


You raise a valid point. Setting mark at frameheadpos would be safe
for the window function's own read pointer, since we only allow
ROWS BETWEEN CURRENT ROW.

However, the difficulty is with the PREV/NEXT navigation. In the
experimental patch, PREV accepts an optional offset argument --
e.g. PREV(price, N) -- and this offset can be a runtime expression
whose value is not known until evaluation time. If the mark is
advanced to frameheadpos, PREV(price, N) with a large N could try
to access rows that the tuplestore has already truncated.

This is why the experimental patch takes a different approach: it
creates a separate nav_winobj with its own mark pointer pinned at
position 0, so that the tuplestore never truncates rows that PREV
might need. The window function's own mark management is left
unchanged.

The mark=0 approach is safe but conservative -- it prevents the
tuplestore from releasing any rows within the partition. The SQL
standard (ISO/IEC 19075-5, Subclause 5.6.2) requires the offset
argument to be a "runtime constant" -- meaning it cannot reference
columns or row pattern variables. So in practice, the maximum
offset is always known at plan time, which opens the door for a
future optimization: advance the mark to (currentpos - max_offset)
when all PREV offsets in the DEFINE clause are constant.

Since the experimental patch redesigns how mark works for PREV/NEXT
navigation, I think we should drop 0004 from the current series
and revisit the set_mark question together with the experimental
patch. What do you think?

Best regards,
Henson

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

<div dir=3D"ltr"><div dir=3D"ltr">Hi Tatsuo,</div><div dir=3D"ltr"><br></di=
v><div class=3D"gmail_quote gmail_quote_container"><blockquote class=3D"gma=
il_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left-width:1px;border-le=
ft-style:solid;border-left-color:rgb(204,204,204);padding-left:1ex">
&gt;=C2=A0 =C2=A00004: Fix window_last_value set_mark during RPR<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Restore set_mark=3Dtrue in window_las=
t_value (normal behavior)<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0and add an RPR-specific override insi=
de WinGetFuncArgInFrame.<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0This keeps the RPR workaround localiz=
ed rather than changing<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0the caller&#39;s semantics unconditio=
nally.<br>
<br>
So, if RPR is active, WinSetMarkPosition() is not called at all?=C2=A0 That=
<br>
seems too strong limitation.=C2=A0 Can&#39;t we set mark at frameheadpos ev=
en<br>
if RPR is active? It seems safe since the only allowed franme start<br>
option is ROWS BETWEEN CURRENT ROW and we never step back to the rows<br>
before frameheadpos.</blockquote><div><br></div>You raise a valid point. Se=
tting mark at frameheadpos would be safe<br>for the window function&#39;s o=
wn read pointer, since we only allow<br>ROWS BETWEEN CURRENT ROW.<br><br>Ho=
wever, the difficulty is with the PREV/NEXT navigation. In the<br>experimen=
tal patch, PREV accepts an optional offset argument --<br>e.g. PREV(price, =
N) -- and this offset can be a runtime expression<br>whose value is not kno=
wn until evaluation time. If the mark is<br>advanced to frameheadpos, PREV(=
price, N) with a large N could try<br>to access rows that the tuplestore ha=
s already truncated.<br><br>This is why the experimental patch takes a diff=
erent approach: it<br>creates a separate nav_winobj with its own mark point=
er pinned at<br>position 0, so that the tuplestore never truncates rows tha=
t PREV<br>might need. The window function&#39;s own mark management is left=
<br>unchanged.<br><br>The mark=3D0 approach is safe but conservative -- it =
prevents the<br>tuplestore from releasing any rows within the partition. Th=
e SQL<br>standard (ISO/IEC 19075-5, Subclause 5.6.2) requires the offset<br=
>argument to be a &quot;runtime constant&quot; -- meaning it cannot referen=
ce<br>columns or row pattern variables. So in practice, the maximum<br>offs=
et is always known at plan time, which opens the door for a<br>future optim=
ization: advance the mark to (currentpos - max_offset)<br>when all PREV off=
sets in the DEFINE clause are constant.<br><br>Since the experimental patch=
 redesigns how mark works for PREV/NEXT<br>navigation, I think we should dr=
op 0004 from the current series<br>and revisit the set_mark question togeth=
er with the experimental<br>patch. What do you think?<br><br>Best regards,<=
br>Henson=C2=A0</div></div>

--0000000000008daba4064d8824da--