MIME-Version: 1.0
References: 
 <CAApHDvp738G75HfkKcfXaf3a8s=6mmtOLh46tMD0D2hAo1UCzA@mail.gmail.com>
In-Reply-To: 
 <CAApHDvp738G75HfkKcfXaf3a8s=6mmtOLh46tMD0D2hAo1UCzA@mail.gmail.com>
From: Tender Wang <tndrwang@gmail.com>
Date: Mon, 24 Mar 2025 14:50:08 +0800
Message-ID: 
 <CAHewXN=BbBCcyxPNBrQsF_Sz+i3n2jPf18rGJQwbbz18yD0J6A@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="0000000000007e66750631110475"
Archived-At: 
 <https://www.postgresql.org/message-id/CAHewXN%3DBbBCcyxPNBrQsF_Sz%2Bi3n2jPf18rGJQwbbz18yD0J6A%40mail.gmail.com>
Precedence: bulk

--0000000000007e66750631110475
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 05:28=E5=86=99=E9=81=93=EF=BC=9A

> Over in [1], there was some uncertainty about whether locking an
> unrelated partition was expected behaviour or not for the particular
> use-case which used a generic plan to scan a partitioned table and all
> of the partitions.
>
> I noticed that we don't mention this in the docs and though that
> perhaps we should. I thought a good place might be in [2] at the end
> of the following paragraph:
>
> "During initialization of the query plan. Partition pruning can be
> performed here for parameter values which are known during the
> initialization phase of execution. Partitions which are pruned during
> this stage will not show up in the query's EXPLAIN or EXPLAIN ANALYZE.
> It is possible to determine the number of partitions which were
> removed during this phase by observing the =E2=80=9CSubplans Removed=E2=
=80=9D property
> in the EXPLAIN output."
>
> Perhaps adding something like "Because all relations which are part of
> the plan are locked at the beginning of execution, any partitions
> pruned at this stage are already locked and will remain so until the
> end of the transaction.".
>
> or:
>
> "It's important to note that any partitions removed by the partition
> pruning done at this stage are still locked at the beginning of
> execution".
>

I prefer this.


>
> 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"
.


[1]
https://postgr.es/m/01020195b987abd3-a008b77d-8c63-4931-80a4-be36a351c8b2-0=
00000@eu-west-1.amazonses.com


--=20
Thanks,
Tender Wang

--0000000000007e66750631110475
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 05:28=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">Over in [1], there was some uncertainty about whether locking an<br>
unrelated partition was expected behaviour or not for the particular<br>
use-case which used a generic plan to scan a partitioned table and all<br>
of the partitions.<br>
<br>
I noticed that we don&#39;t mention this in the docs and though that<br>
perhaps we should. I thought a good place might be in [2] at the end<br>
of the following paragraph:<br>
<br>
&quot;During initialization of the query plan. Partition pruning can be<br>
performed here for parameter values which are known during the<br>
initialization phase of execution. Partitions which are pruned during<br>
this stage will not show up in the query&#39;s EXPLAIN or EXPLAIN ANALYZE.<=
br>
It is possible to determine the number of partitions which were<br>
removed during this phase by observing the =E2=80=9CSubplans Removed=E2=80=
=9D property<br>
in the EXPLAIN output.&quot;<br>
<br>
Perhaps adding something like &quot;Because all relations which are part of=
<br>
the plan are locked at the beginning of execution, any partitions<br>
pruned at this stage are already locked and will remain so until the<br>
end of the transaction.&quot;.<br>
<br>
or:<br>
<br>
&quot;It&#39;s important to note that any partitions removed by the partiti=
on<br>
pruning done at this stage are still locked at the beginning of<br>
execution&quot;.<br></blockquote><div><br></div><div>I prefer this.</div><d=
iv>=C2=A0</div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0p=
x 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
<br>
This is no longer true in master, so if we do something here it&#39;s only<=
br>
v17 and earlier.<br></blockquote><div>=C2=A0</div><div>In the case of [1], =
we still have AccessShareLock on entity_2, even though it is pruned during =
initial partition pruning.</div><div>This seems to contradict what you said=
.=C2=A0 &quot;This is no longer true in master&quot; .=C2=A0=C2=A0</div><di=
v><br></div><div><br></div><div>[1] <a href=3D"https://postgr.es/m/01020195=
b987abd3-a008b77d-8c63-4931-80a4-be36a351c8b2-000000@eu-west-1.amazonses.co=
m" rel=3D"noreferrer" target=3D"_blank">https://postgr.es/m/01020195b987abd=
3-a008b77d-8c63-4931-80a4-be36a351c8b2-000000@eu-west-1.amazonses.com</a></=
div></div><div><br clear=3D"all"></div><div><br></div><span class=3D"gmail_=
signature_prefix">-- </span><br><div dir=3D"ltr" class=3D"gmail_signature">=
<div dir=3D"ltr"><div>Thanks,</div><div>Tender Wang</div></div></div></div>

--0000000000007e66750631110475--