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

On 3/21/25 09:27, Evgeny Morozov wrote:
> I have a list-partitioned table. When I query the base table but filter
> by the partition column in a regular SQL query this takes a lock only on
> the one partition being queried, as I expect. However, when the exact
> same SQL query is run fom a DB function, with the partition ID passed in
> as argument, it takes (shared) locks on ALL partitions - which blocks
> any other process that wants an exclusive lock on another partition (and
> vice-versa).
>
> Originally found on PG 15.12, but happens on 17.4 as well. Easily
> reproducible:
>
> -- One-time setup
>
> create table entity
> (
>     part_id integer not null
> ) partition by list (part_id);
>
> create table entity_1 partition of entity for values in (1);
> create table entity_2 partition of entity for values in (2);
>
> create function read_partition(which_part int) returns bigint as
> 'select count(*) from entity where part_id = which_part;'
> language sql stable;
>
> -- Then try this, keeping the connection open (so the transaction is
> pending):
>
> begin;
> 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;
>
> 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?

Hmm, seems to be a sql function issue:

CREATE OR REPLACE FUNCTION public.read_partition(which_part integer)
RETURNS bigint
LANGUAGE plpgsql
STABLE
AS $$
DECLARE
id_ct bigint;
BEGIN
select count(*) into id_ct from entity where part_id = $1;
RETURN id_ct;
END;
$$;

BEGIN;

select read_partition(1);
read_partition
----------------
0

select relation::regclass, mode from pg_locks ;
relation | mode
----------+-----------------
pg_locks | AccessShareLock
entity_1 | AccessShareLock
entity | AccessShareLock
| ExclusiveLock

>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com