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

From: David Rowley <[email-masked]>
Date: Mon, 24 Mar 2025 02:35:58 +1300

On Sat, 22 Mar 2025 at 05:27, Evgeny Morozov
<[email-masked]> 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

---

From: Evgeny Morozov <[email-masked]>
Date: Fri, 28 Mar 2025 17:00:20 +0000

> Is this just an example command?  You can't add a column to a

Yes, it was just the simplest way I could think of to take an exclusive
lock. But on this note: I guess it takes a lock before checking that the
table is partition because it might otherwise get attached/detached as a
partition between doing the check and taking a lock, but it would be
nice if it took a shared lock to do all checks, then upgraded to an
exclusive lock to actually do the work. Then the user would not have to
wait for an exclusive lock only for the command to fail.

> pruning that occurs only does so after the locks are taken.

I see, thank you for the explanation. This seems like a bad plan,
though, because even at query preparation time it can be determined that
only one partition will need to be scanned, since the query filters on
the partition key, so it may need to read *any one* partition, but never
all partitions. So in this case, isn't it better to avoid caching a
generic plan at all? Even if the locking issue is fixed in PG 18, isn't
such a plan likely to be sub-optimal in other ways (for a more complex
query)? I don't know anything about the internals of the query planner,
but I have run into other performance issues with SQL functions querying
partitioned tables. One function we have *sometimes* uses very slow
sequential scans instead of index scans, and I've had to work around
that with "set enable_seqscan = off".

In this case, even "set plan_cache_mode = force_custom_plan" doesn't
help somehow. Isn't that supposed to... make PG use a custom a plan?

Here is another weird thing I forgot to menton in the original post: for
a set-returning function whether one partition table is locked or all of
them seems to depend on whether you "select func" or "select from func"!

create function read_partition_rows(which_part int) returns
table(part_id int) as
'select part_id from entity where part_id = which_part;'
language sql stable;

select * from read_partition_rows(1); -- This takes a lock only on entity_1
select read_partition_rows(1); -- but this takes locks on entity_1 and
entity_2!

> we'll see that feature released with PostgreSQL 18 toward the end of

Happy to hear that! I hope that makes it in, but in the meanwhile, yes,
it would be nice to at least document this gotcha and any workarounds
for it. The only one I've found is to use a pl/PgSQL function.

---

From: David Rowley <[email-masked]>
Date: Tue, 1 Apr 2025 09:28:00 +1300

> wait for an exclusive lock only for the command to fail.

Unfortunately, that's probably just swapping one problem for another.
Once you have 2 sessions following such a pattern of locking, you're
prone to unnecessary deadlocking.

For example:

-- session1
begin;
lock table t in access share mode; -- gets lock

-- session2
begin;
lock table t in access share mode; -- gets lock
lock table t in access exclusive mode; -- waits

-- session1
lock table t in access exclusive mode; -- deadlock

If you don't bother with the access share lock, there's no deadlock.

David