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?
