Add mention in docs about locking all partitions for generic plans

David Rowley <dgrowleyml@gmail.com> 于2025年3月24日周一 05:28写道：

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 “Subplans Removed” 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]/messages/by-id/01020195b987abd3-a008b77d-8c63-4931-80a4-be36a351c8b2-000000@eu-west-1.amazonses.com, 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]: /messages/by-id/01020195b987abd3-a008b77d-8c63-4931-80a4-be36a351c8b2-000000@eu-west-1.amazonses.com
/messages/by-id/01020195b987abd3-a008b77d-8c63-4931-80a4-be36a351c8b2-000000@eu-west-1.amazonses.com

--
Thanks,
Tender Wang

On Mon, 24 Mar 2025 at 19:50, Tender Wang <tndrwang@gmail.com> wrote:

David Rowley <dgrowleyml@gmail.com> 于2025年3月24日周一 05:28写道：

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 = 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 “Subplans Removed”
property.".

Any opinions?

[1] /messages/by-id/01020195b987abd3-a008b77d-8c63-4931-80a4-be36a351c8b2-000000@eu-west-1.amazonses.com

David

David Rowley <dgrowleyml@gmail.com> 于2025年3月24日周一 16:50写道：

On Mon, 24 Mar 2025 at 19:50, Tender Wang <tndrwang@gmail.com> wrote:

David Rowley <dgrowleyml@gmail.com> 于2025年3月24日周一 05:28写道：

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 = 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 “Subplans Removed”
property.".

Any opinions?

The above sentence looks good to me.

--
Thanks,
Tender Wang

On Mon, 24 Mar 2025 at 22:19, Tender Wang <tndrwang@gmail.com> wrote:

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 “Subplans Removed”
property.".

Any opinions?

The above sentence looks good to me.

Thanks for looking.

I've attached the master and the <= v17 in patch form. Also, the
compiled HTML for ease of review.

I'll push these in the next few days unless anyone else wants to voice
their opinions.

David

On Mon, 31 Mar 2025 at 12:19, David Rowley <dgrowleyml@gmail.com> wrote:

I'll push these in the next few days unless anyone else wants to voice
their opinions.

Thanks for the review. Pushed.

David