MIME-Version: 1.0
References: 
 <CAApHDvp738G75HfkKcfXaf3a8s=6mmtOLh46tMD0D2hAo1UCzA@mail.gmail.com>
 <CAHewXN=BbBCcyxPNBrQsF_Sz+i3n2jPf18rGJQwbbz18yD0J6A@mail.gmail.com>
 <CAApHDvoRvkmD3z67hY5jb+eEQUyA1QVZBL8_C30HOTjgRhfHzA@mail.gmail.com>
In-Reply-To: 
 <CAApHDvoRvkmD3z67hY5jb+eEQUyA1QVZBL8_C30HOTjgRhfHzA@mail.gmail.com>
From: Tender Wang <tndrwang@gmail.com>
Date: Mon, 24 Mar 2025 17:19:12 +0800
Message-ID: 
 <CAHewXNm2iCGgi4_+WCJ-nnBPKB1q_uXJzSPArHWV_SaDb=gV+Q@mail.gmail.com>
Subject: Re: Add mention in docs about locking all partitions for generic
 plans
To: David Rowley <dgrowleyml@gmail.com>
Cc: PostgreSQL Developers <pgsql-hackers@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="00000000000091493c06311319b4"
Archived-At: 
 <https://www.postgresql.org/message-id/CAHewXNm2iCGgi4_%2BWCJ-nnBPKB1q_uXJzSPArHWV_SaDb%3DgV%2BQ%40mail.gmail.com>
Precedence: bulk

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

David Rowley <dgrowleyml@gmail.com> =E4=BA=8E2025=E5=B9=B43=E6=9C=8824=E6=
=97=A5=E5=91=A8=E4=B8=80 16:50=E5=86=99=E9=81=93=EF=BC=9A

> On Mon, 24 Mar 2025 at 19:50, Tender Wang <tndrwang@gmail.com> wrote:
> >
> > David Rowley <dgrowleyml@gmail.com> =E4=BA=8E2025=E5=B9=B43=E6=9C=8824=
=E6=97=A5=E5=91=A8=E4=B8=80 05:28=E5=86=99=E9=81=93=EF=BC=9A
> >> This is no longer true in master, so if we do something here it's only
> >> v17 and earlier.
> >
> > In the case of [1], we still have AccessShareLock on entity_2, even
> though it is pruned during initial partition pruning.
> > This seems to contradict what you said.  "This is no longer true in
> master" .
>
> For that particular case, planning occurs each time prior to execution
> and it's the planner that takes the lock, not the executor. If you
> want to not plan each time then you could modify that case to use a
> plpgsql function instead of sql and then ensure you're using a cached
> plan with "set plan_cache_mode =3D force_generic_plan;" (apparently we
> don't cache plans for non-inlined SQL functions). I disagree that this
> makes the proposed sentence untrue. 525392d57 did change the order of
> operations here so that the partitions in the Append/MergeAppend are
> locked after run-time pruning occurs at executor startup.
>

Thanks for the explanation.

>
> Maybe I was wrong about writing nothing in master's docs. It might
> still be important to detail this. I don't know the best way to phrase
> that, but maybe something along the lines of: "The query planner
> obtains locks for all partitions which are part of the plan.  However,
> when the executor uses a cached plan, locks are only obtained on the
> partitions which remain after partition pruning done during the
> initialization phase of execution, i.e., the ones shown in the EXPLAIN
> output and not the ones referred to by the =E2=80=9CSubplans Removed=E2=
=80=9D
> property.".
>
> Any opinions?
>

The above sentence looks good to me.


--=20
Thanks,
Tender Wang

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

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><br><div class=3D"gmail_quote g=
mail_quote_container"><div dir=3D"ltr" class=3D"gmail_attr">David Rowley &l=
t;<a href=3D"mailto:dgrowleyml@gmail.com">dgrowleyml@gmail.com</a>&gt; =E4=
=BA=8E2025=E5=B9=B43=E6=9C=8824=E6=97=A5=E5=91=A8=E4=B8=80 16:50=E5=86=99=
=E9=81=93=EF=BC=9A<br></div><blockquote class=3D"gmail_quote" style=3D"marg=
in:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1e=
x">On Mon, 24 Mar 2025 at 19:50, Tender Wang &lt;<a href=3D"mailto:tndrwang=
@gmail.com" target=3D"_blank">tndrwang@gmail.com</a>&gt; wrote:<br>
&gt;<br>
&gt; David Rowley &lt;<a href=3D"mailto:dgrowleyml@gmail.com" target=3D"_bl=
ank">dgrowleyml@gmail.com</a>&gt; =E4=BA=8E2025=E5=B9=B43=E6=9C=8824=E6=97=
=A5=E5=91=A8=E4=B8=80 05:28=E5=86=99=E9=81=93=EF=BC=9A<br>
&gt;&gt; This is no longer true in master, so if we do something here it=
9;s only<br>
&gt;&gt; v17 and earlier.<br>
&gt;<br>
&gt; In the case of [1], we still have AccessShareLock on entity_2, even th=
ough it is pruned during initial partition pruning.<br>
&gt; This seems to contradict what you said.=C2=A0 &quot;This is no longer =
true in master&quot; .<br>
<br>
For that particular case, planning occurs each time prior to execution<br>
and it&#39;s the planner that takes the lock, not the executor. If you<br>
want to not plan each time then you could modify that case to use a<br>
plpgsql function instead of sql and then ensure you&#39;re using a cached<b=
r>
plan with &quot;set plan_cache_mode =3D force_generic_plan;&quot; (apparent=
ly we<br>
don&#39;t cache plans for non-inlined SQL functions). I disagree that this<=
br>
makes the proposed sentence untrue. 525392d57 did change the order of<br>
operations here so that the partitions in the Append/MergeAppend are<br>
locked after run-time pruning occurs at executor startup.<br></blockquote><=
div><br></div><div>Thanks for the explanation.=C2=A0</div><blockquote class=
=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rg=
b(204,204,204);padding-left:1ex">
<br>
Maybe I was wrong about writing nothing in master&#39;s docs. It might<br>
still be important to detail this. I don&#39;t know the best way to phrase<=
br>
that, but maybe something along the lines of: &quot;The query planner<br>
obtains locks for all partitions which are part of the plan.=C2=A0 However,=
<br>
when the executor uses a cached plan, locks are only obtained on the<br>
partitions which remain after partition pruning done during the<br>
initialization phase of execution, i.e., the ones shown in the EXPLAIN<br>
output and not the ones referred to by the =E2=80=9CSubplans Removed=E2=80=
=9D<br>
property.&quot;.<br>
<br>
Any opinions?<br></blockquote><div><br></div><div>The above sentence looks =
good to me.</div></div><div><br clear=3D"all"></div><div><br></div><span cl=
ass=3D"gmail_signature_prefix">-- </span><br><div dir=3D"ltr" class=3D"gmai=
l_signature"><div dir=3D"ltr"><div>Thanks,</div><div>Tender Wang</div></div=
></div></div>

--00000000000091493c06311319b4--