MIME-Version: 1.0
References: 
 <CAApHDvp738G75HfkKcfXaf3a8s=6mmtOLh46tMD0D2hAo1UCzA@mail.gmail.com>
 <CAHewXN=BbBCcyxPNBrQsF_Sz+i3n2jPf18rGJQwbbz18yD0J6A@mail.gmail.com>
In-Reply-To: 
 <CAHewXN=BbBCcyxPNBrQsF_Sz+i3n2jPf18rGJQwbbz18yD0J6A@mail.gmail.com>
From: David Rowley <dgrowleyml@gmail.com>
Date: Mon, 24 Mar 2025 21:50:32 +1300
Message-ID: 
 <CAApHDvoRvkmD3z67hY5jb+eEQUyA1QVZBL8_C30HOTjgRhfHzA@mail.gmail.com>
Subject: Re: Add mention in docs about locking all partitions for generic
 plans
To: Tender Wang <tndrwang@gmail.com>
Cc: PostgreSQL Developers <pgsql-hackers@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAApHDvoRvkmD3z67hY5jb%2BeEQUyA1QVZBL8_C30HOTjgRhfHzA%40mail.gmail.com>
Precedence: bulk

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 thoug=
h it is pruned during initial partition pruning.
> This seems to contradict what you said.  "This is no longer true in maste=
r" .

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.

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?

> [1] https://postgr.es/m/01020195b987abd3-a008b77d-8c63-4931-80a4-be36a351=
c8b2-000000@eu-west-1.amazonses.com

David