public inbox for [email protected]  

help / color / mirror / Atom feed

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

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
[email protected]

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], [email protected]
  Subject: Re: Querying one partition in a function takes locks on all partitions
  In-Reply-To: <[email protected]>

* 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