Feedback-ID: i76984098:Fastmail
Message-ID: <02682db3-e3fa-4de5-af49-1f6dc4f8e5cf@aklaver.com>
Date: Fri, 21 Mar 2025 16:11:55 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Querying one partition in a function takes locks on all
 partitions
To: Evgeny Morozov <postgresql4@realityexists.net>,
 PostgreSQL General <pgsql-general@postgresql.org>
References: <01020195b987abd3-a008b77d-8c63-4931-80a4-be36a351c8b2-000000@eu-west-1.amazonses.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <01020195b987abd3-a008b77d-8c63-4931-80a4-be36a351c8b2-000000@eu-west-1.amazonses.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/02682db3-e3fa-4de5-af49-1f6dc4f8e5cf%40aklaver.com>
Precedence: bulk

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.klaver@aklaver.com