public inbox for [email protected]help / color / mirror / Atom feed
Querying one partition in a function takes locks on all partitions 6+ messages / 5 participants [nested] [flat]
* Querying one partition in a function takes locks on all partitions @ 2025-03-21 16:27 Evgeny Morozov <[email protected]> 0 siblings, 1 reply; 6+ messages in thread From: Evgeny Morozov @ 2025-03-21 16:27 UTC (permalink / raw) To: pgsql-general 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? ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Querying one partition in a function takes locks on all partitions @ 2025-03-28 21:30 Renan Alves Fonseca <[email protected]> parent: Evgeny Morozov <[email protected]> 0 siblings, 1 reply; 6+ messages in thread From: Renan Alves Fonseca @ 2025-03-28 21:30 UTC (permalink / raw) To: Evgeny Morozov <[email protected]>; pgsql-general 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 protected]> 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 protected]> 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? >> >> >> > ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Querying one partition in a function takes locks on all partitions @ 2025-03-31 03:10 David Rowley <[email protected]> parent: Renan Alves Fonseca <[email protected]> 0 siblings, 1 reply; 6+ messages in thread From: David Rowley @ 2025-03-31 03:10 UTC (permalink / raw) To: Renan Alves Fonseca <[email protected]>; +Cc: Evgeny Morozov <[email protected]>; pgsql-general On Sat, 29 Mar 2025 at 10:30, Renan Alves Fonseca <[email protected]> wrote: > 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 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=TaGBWew@mail.gmail.com ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Querying one partition in a function takes locks on all partitions @ 2025-03-31 16:41 Renan Alves Fonseca <[email protected]> parent: David Rowley <[email protected]> 0 siblings, 2 replies; 6+ messages in thread From: Renan Alves Fonseca @ 2025-03-31 16:41 UTC (permalink / raw) To: David Rowley <[email protected]>; +Cc: Evgeny Morozov <[email protected]>; pgsql-general On Mon, Mar 31, 2025 at 5:10 AM David Rowley <[email protected]> wrote: > > On Sat, 29 Mar 2025 at 10:30, Renan Alves Fonseca > <[email protected]> wrote: > > 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 have any suggestions about where you think those should be > 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 ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Querying one partition in a function takes locks on all partitions @ 2025-03-31 17:08 Laurenz Albe <[email protected]> parent: Renan Alves Fonseca <[email protected]> 1 sibling, 0 replies; 6+ messages in thread From: Laurenz Albe @ 2025-03-31 17:08 UTC (permalink / raw) To: Renan Alves Fonseca <[email protected]>; David Rowley <[email protected]>; +Cc: Evgeny Morozov <[email protected]>; pgsql-general On Mon, 2025-03-31 at 18:41 +0200, Renan Alves Fonseca wrote: > 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." 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 ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Querying one partition in a function takes locks on all partitions @ 2025-03-31 17:21 Tom Lane <[email protected]> parent: Renan Alves Fonseca <[email protected]> 1 sibling, 0 replies; 6+ messages in thread From: Tom Lane @ 2025-03-31 17:21 UTC (permalink / raw) To: Renan Alves Fonseca <[email protected]>; +Cc: David Rowley <[email protected]>; Evgeny Morozov <[email protected]>; pgsql-general Renan Alves Fonseca <[email protected]> writes: > 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 ..." Yup. > If I understood well [2], then both notes may be discarded together in > the next version. 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 ^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2025-03-31 17:21 UTC | newest] Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2025-03-21 16:27 Querying one partition in a function takes locks on all partitions Evgeny Morozov <[email protected]> 2025-03-28 21:30 ` Renan Alves Fonseca <[email protected]> 2025-03-31 03:10 ` David Rowley <[email protected]> 2025-03-31 16:41 ` Renan Alves Fonseca <[email protected]> 2025-03-31 17:08 ` Laurenz Albe <[email protected]> 2025-03-31 17:21 ` Tom Lane <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox