Re: Querying one partition in a function takes locks on all partitions

On Sat, 22 Mar 2025 at 05:27, Evgeny Morozov
<postgresql4(at)realityexists(dot)net> wrote:
> select read_partition(1); -- This takes shared locks on entity_1 AND
> entity_2
>
> -- select count(*) from entity where part_id = 1; -- but this would only
> take a shared lock only on entity_1
>
> If another session tries something that takes an exclusive lock on
> another partition, like
>
> alter table entity_2 add column new_column text;

Is this just an example command? You can't add a column to a
partition directly.

> I would expect that to be able to run concurrently, but it blocks due to
> the shared lock on entity_2. (The way I originally found the problem was
> the opposite: once one client took an exclusive lock on a partition many
> others were blocked from reading from ANY partition.)
>
> This seems like quite the "gotcha", especially when the query plan for
> the function call (logged via autoexplain) shows it only accessing one
> partition (entity_1). Is this expected behavior? If so, is it documented
> somewhere?

It is expected behaviour and, unfortunately, not really documented
anywhere outside of the source code.

What's going on is that PostgreSQL is creating a generic plan for your
query, that is, a plan that will work with any parameter value that
you give to your function. When the generic plan is executed and the
locks are taken for the relations mentioned in the query, and since
the plan is generic, it includes all partitions that could match any
possible parameter value you could pass. When the locks are taken,
it's not yet known which partition will be needed as the partition
pruning that occurs only does so after the locks are taken.

There has been work done for PostgreSQL 18 which swaps the order of
these operations and makes it so that the executor only obtains the
locks on the partitions which will actually be scanned. Hopefully,
we'll see that feature released with PostgreSQL 18 toward the end of
2025.

As for the documentation, it might be worth adding a mention of this
at the end of the following paragraph in [1]:

"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 something like. "It's important to note that any partitions
removed by the partition pruning done at this time are still locked at
the beginning of execution".

David

[1] https://www.postgresql.org/docs/17/ddl-partitioning.html