MIME-Version: 1.0
References: 
 <CAAAe_zBbrnx2fjK2s+Jgx6TSOdnKAPawXbHeX49WqmX9ji+Hdg@mail.gmail.com>
 <20260321.140232.1947157589839395257.ishii@postgresql.org>
 <CAAAe_zDmjV-R6M0xeMDzZN+eRVUhsrt6NA=xjvj9BQMog9tqTQ@mail.gmail.com>
 <20260321.231629.266287379124559013.ishii@postgresql.org>
In-Reply-To: <20260321.231629.266287379124559013.ishii@postgresql.org>
Reply-To: assam258@gmail.com
From: Henson Choi <assam258@gmail.com>
Date: Sat, 21 Mar 2026 23:57:06 +0900
Message-ID: 
 <CAAAe_zDneX+WWEgoM+rXLg+H2OK_VuXoiipzJu2Q0rCSSCzY8g@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="00000000000098a829064d8a0482"
Archived-At: 
 <https://www.postgresql.org/message-id/CAAAe_zDneX%2BWWEgoM%2BrXLg%2BH2OK_VuXoiipzJu2Q0rCSSCzY8g%40mail.gmail.com>
Precedence: bulk

--00000000000098a829064d8a0482
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

2026=EB=85=84 3=EC=9B=94 21=EC=9D=BC (=ED=86=A0) 23:16, Tatsuo Ishii <ishii=
@postgresql.org>=EB=8B=98=EC=9D=B4 =EC=9E=91=EC=84=B1:

> Hi Henson,
>
> >> 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=3D0 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?
>
> Agreed. So I just want to make sure I create v46 using 0001-0003 and
> 0005-0015. Am I correct?


Yes, that=E2=80=99s right!=E2=80=8B=E2=80=8B=E2=80=8B=E2=80=8B=E2=80=8B=E2=
=80=8B=E2=80=8B=E2=80=8B=E2=80=8B=E2=80=8B=E2=80=8B=E2=80=8B=E2=80=8B=E2=80=
=8B=E2=80=8B=E2=80=8B


> Best regards,
> --
> Tatsuo Ishii
> SRA OSS K.K.
> English: http://www.sraoss.co.jp/index_en/
> Japanese:http://www.sraoss.co.jp
>

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

<div><br></div><div><br><div class=3D"gmail_quote gmail_quote_container"><d=
iv dir=3D"ltr" class=3D"gmail_attr">2026=EB=85=84 3=EC=9B=94 21=EC=9D=BC (=
=ED=86=A0) 23:16, Tatsuo Ishii &lt;<a href=3D"mailto:ishii@postgresql.org">=
ishii@postgresql.org</a>&gt;=EB=8B=98=EC=9D=B4 =EC=9E=91=EC=84=B1:<br></div=
><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border=
-left-width:1px;border-left-style:solid;padding-left:1ex;border-left-color:=
rgb(204,204,204)" dir=3D"auto">Hi Henson,<br>
<br>
&gt;&gt; So, if RPR is active, WinSetMarkPosition() is not called at all?=
=C2=A0 That<br>
&gt;&gt; seems too strong limitation.=C2=A0 Can&#39;t we set mark at frameh=
eadpos even<br>
&gt;&gt; if RPR is active? It seems safe since the only allowed franme star=
t<br>
&gt;&gt; option is ROWS BETWEEN CURRENT ROW and we never step back to the r=
ows<br>
&gt;&gt; before frameheadpos.<br>
&gt; <br>
&gt; <br>
&gt; You raise a valid point. Setting mark at frameheadpos would be safe<br=
>
&gt; for the window function&#39;s own read pointer, since we only allow<br=
>
&gt; ROWS BETWEEN CURRENT ROW.<br>
&gt; <br>
&gt; However, the difficulty is with the PREV/NEXT navigation. In the<br>
&gt; experimental patch, PREV accepts an optional offset argument --<br>
&gt; e.g. PREV(price, N) -- and this offset can be a runtime expression<br>
&gt; whose value is not known until evaluation time. If the mark is<br>
&gt; advanced to frameheadpos, PREV(price, N) with a large N could try<br>
&gt; to access rows that the tuplestore has already truncated.<br>
&gt;<br>
&gt; This is why the experimental patch takes a different approach: it<br>
&gt; creates a separate nav_winobj with its own mark pointer pinned at<br>
&gt; position 0, so that the tuplestore never truncates rows that PREV<br>
&gt; might need. The window function&#39;s own mark management is left<br>
&gt; unchanged.<br>
&gt;<br>
&gt; The mark=3D0 approach is safe but conservative -- it prevents the<br>
&gt; tuplestore from releasing any rows within the partition. The SQL<br>
&gt; standard (ISO/IEC 19075-5, Subclause 5.6.2) requires the offset<br>
&gt; argument to be a &quot;runtime constant&quot; -- meaning it cannot ref=
erence<br>
&gt; columns or row pattern variables. So in practice, the maximum<br>
&gt; offset is always known at plan time, which opens the door for a<br>
&gt; future optimization: advance the mark to (currentpos - max_offset)<br>
&gt; when all PREV offsets in the DEFINE clause are constant.<br>
&gt;<br>
&gt; Since the experimental patch redesigns how mark works for PREV/NEXT<br=
>
&gt; navigation, I think we should drop 0004 from the current series<br>
&gt; and revisit the set_mark question together with the experimental<br>
&gt; patch. What do you think?<br>
<br>
Agreed. So I just want to make sure I create v46 using 0001-0003 and<br>
0005-0015. Am I correct?</blockquote><div dir=3D"auto"><br></div><div dir=
=3D"auto"><div style=3D"font-size:inherit">Yes, that=E2=80=99s right!=E2=80=
=8B=E2=80=8B=E2=80=8B=E2=80=8B=E2=80=8B=E2=80=8B=E2=80=8B=E2=80=8B=E2=80=8B=
=E2=80=8B=E2=80=8B=E2=80=8B=E2=80=8B=E2=80=8B=E2=80=8B=E2=80=8B</div><br></=
div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;bor=
der-left-width:1px;border-left-style:solid;padding-left:1ex;border-left-col=
or:rgb(204,204,204)" dir=3D"auto"><br>
Best regards,<br>
--<br>
Tatsuo Ishii<br>
SRA OSS K.K.<br>
English: <a href=3D"http://www.sraoss.co.jp/index_en/" rel=3D"noreferrer" t=
arget=3D"_blank">http://www.sraoss.co.jp/index_en/</a><br>
Japanese:<a href=3D"http://www.sraoss.co.jp" rel=3D"noreferrer" target=3D"_=
blank">http://www.sraoss.co.jp</a><br>
</blockquote></div></div>

--00000000000098a829064d8a0482--