Subject: Querying one partition in a function takes locks on all partitions

From: Evgeny Morozov <[email-masked]>
Date: Fri, 21 Mar 2025 16:27:11 +0000

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?

---

From: Renan Alves Fonseca <[email-masked]>
Date: Fri, 28 Mar 2025 22:30:08 +0100

I've investigated further and found out that the code that processes
SQL functions is completely different from the code that processes SQL
statements. The latter is more efficient, and there is ongoing work to
merge both.

Currently, in the SQL function path the plan is always generic. The
planner ignores the function arguments. The plan_cache_mode setting
has no effect in this path.

I agree that the docs should be more explicit about this. There is a
high penalty for using generic plans in complex functions.

If you can use prepared statements
(https://www.postgresql.org/docs/current/sql-prepare.html), they run
on the optimized path and respect plan_cache_mode.

Regards,
Renan

On Fri, Mar 28, 2025 at 5:38 PM Evgeny Morozov
<[email-masked]> wrote:
>
> Thank you for doing the extra investigation! I realised only now you didn't send this to the mailing list, only to me. As you say, force_custom_plan doesn't seem to help with a SQL function - just tested that.
>
Thanks for noting. I've seen just now.

> Regards,
> Evgeny Morozov
>
> On 23/03/2025 12:08 am, Renan Alves Fonseca wrote:
>
> It seems that when we create a function using pure sql, the query planner uses a generic plan.
> We can mimic this behavior using prepared statements and plan_cache_mode:
>
> # prepare read1(int) as select count(*) from entity where part_id=$1;
> # set plan_cache_mode = force_generic_plan ;
> # explain (costs off) execute read1(1);
>               QUERY PLAN
> --------------------------------------
>  Aggregate
>    ->  Append
>          Subplans Removed: 1
>          ->  Seq Scan on entity_1
>                Filter: (part_id = $1)
> Note "Subplans Removed". This plan causes lock.
>
> # set plan_cache_mode = force_custom_plan ;
> # explain (costs off) execute read1(1);
>             QUERY PLAN
> -----------------------------------
>  Aggregate
>    ->  Seq Scan on entity_1 entity
>          Filter: (part_id = 1)
>
> No lock in this case.
>
> However, I didn't find a solution to force a custom plan in the stored procedure (written in pure sql). I don't know if it is not supported or if I'm missing some parameter. Anyway, it would be nice to have custom plans in sql stored procedures. I've run into other troubles in the past due to the generic plan.
>
> Regards,
> Renan Fonseca
>
> On Fri, Mar 21, 2025 at 5:27 PM Evgeny Morozov <[email-masked]> wrote:
>>

---

From: David Rowley <[email-masked]>
Date: Mon, 31 Mar 2025 16:10:21 +1300

> high penalty for using generic plans in complex functions.

If you have any suggestions about where you think those should be
added or wording for that, please feel free to suggest.

For the part that's specific to partitioning, I've suggested some
additional wording in the relevant spot in [1].

David

[1] https://postgr.es/m/CAApHDvogvzANoTOCyXUWgEuPFx1nT6S63aAN0bDRSJ=[email-masked]

---

From: Renan Alves Fonseca <[email-masked]>
Date: Mon, 31 Mar 2025 18:41:54 +0200

> added or wording for that, please feel free to suggest.

There is a specific chapter about functions written in SQL: [1]. It is
in an advanced section of the docs, so I think it is a suitable place
to address this level of detail.

There is a Note that says: "The entire body of an SQL function is
parsed before any of it is executed. While an SQL function can contain
commands that alter ..."
I would add another Note below like:
"Except when inlined, an SQL function is always executed with a
generic plan. This behavior may not be desired in some situations, and
it will be fixed in future versions."

I'm not sure if we should mention the fix or if we should mention a
workaround...

If I understood well [2], then both notes may be discarded together in
the next version.

Renan

[1] https://www.postgresql.org/docs/17/xfunc-sql.html
[2] https://www.postgresql.org/message-id/db42573039cc66815e80a48589eebea8%40postgrespro.ru

---

From: Laurenz Albe <[email-masked]>
Date: Mon, 31 Mar 2025 19:08:02 +0200

> it will be fixed in future versions."

But that is not true, as far as I can tell: just like any other prepared
statement, it may keep using custom plans.

Yours,
Laurenz Albe

---

From: Tom Lane <[email-masked]>
Date: Mon, 31 Mar 2025 13:21:07 -0400

> parsed before any of it is executed. While an SQL function can contain

Yup.

> If I understood well [2], then both notes may be discarded together in

No, that aspect is not changed in the currently-proposed patch:
we still do parse analysis of all the queries at the beginning.
I'd like to make it work differently eventually, but with feature
freeze so close I do not think that'll happen for v18.

Thinking a bit more about that, if the initial parse analysis succeeds
then the patched code will in fact re-parse before execution if any
DDL changes have occurred.  So you get at least some of the desired
behavioral change.  But the example given in the docs would still
fail, so I don't feel a need to change the docs yet.

			regards, tom lane