public inbox for [email protected]  

help / color / mirror / Atom feed

From: Evgeny Morozov <[email protected]>
To: PostgreSQL General <[email protected]>
Subject: Querying one partition in a function takes locks on all partitions
Date: Fri, 21 Mar 2025 16:27:11 +0000
Message-ID: <01020195b987abd3-a008b77d-8c63-4931-80a4-be36a351c8b2-000000@eu-west-1.amazonses.com> (raw)

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?

view thread (6+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected]
  Subject: Re: Querying one partition in a function takes locks on all partitions
  In-Reply-To: <01020195b987abd3-a008b77d-8c63-4931-80a4-be36a351c8b2-000000@eu-west-1.amazonses.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox