MIME-Version: 1.0
From: David Rowley <dgrowleyml@gmail.com>
Date: Mon, 24 Mar 2025 10:27:58 +1300
Message-ID: 
 <CAApHDvp738G75HfkKcfXaf3a8s=6mmtOLh46tMD0D2hAo1UCzA@mail.gmail.com>
Subject: Add mention in docs about locking all partitions for generic plans
To: 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/CAApHDvp738G75HfkKcfXaf3a8s%3D6mmtOLh46tMD0D2hAo1UCzA%40mail.gmail.com>
Precedence: bulk

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

This is no longer true in master, so if we do something here it's only
v17 and earlier.

Does anyone else have an opinion on this?

David

[1] https://postgr.es/m/01020195b987abd3-a008b77d-8c63-4931-80a4-be36a351c8=
b2-000000@eu-west-1.amazonses.com
[2] https://www.postgresql.org/docs/17/ddl-partitioning.html