From: Evgeny Morozov <postgresql4@realityexists.net>
Subject: Querying one partition in a function takes locks on all partitions
To: PostgreSQL General <pgsql-general@postgresql.org>
Message-ID: <01020195b987abd3-a008b77d-8c63-4931-80a4-be36a351c8b2-000000@eu-west-1.amazonses.com>
Date: Fri, 21 Mar 2025 16:27:11 +0000
User-Agent: Microsoft Office/16.0 (Windows NT 10.0; Microsoft Outlook
 16.0.6326; Pro)
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 8bit
Content-Language: en-GB
Feedback-ID: ::1.eu-west-1.6myxQ/Nob+RNyv0fYur+XpsQ4yD8LrGlDW2OrOybOAI=:AmazonSES
Archived-At: <https://www.postgresql.org/message-id/01020195b987abd3-a008b77d-8c63-4931-80a4-be36a351c8b2-000000%40eu-west-1.amazonses.com>
Precedence: bulk

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?