agora inbox for pgsql-bugs@postgresql.org
help / color / mirror / Atom feedBUG #19692: Generic partition-pruning plan delays statement_timeout cancellation
3+ messages / 3 participants
[nested] [flat]
* BUG #19692: Generic partition-pruning plan delays statement_timeout cancellation
@ 2026-09-17 08:49 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 2 replies; 3+ messages in thread
From: PG Bug reporting form @ 2026-09-17 08:49 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: imchifan@163.com
The following bug has been logged on the website:
Bug reference: 19692
Logged by: Qifan Liu
Email address: imchifan@163.com
PostgreSQL version: 18.6
Operating system: Linux on amd64
Description:
PostgreSQL version: PostgreSQL 20devel (Git commit
a12600b762c36d91450ce085fa25ef75250bc1c2); PostgreSQL 18.6; PostgreSQL 17.11
Operating system: Linux on amd64
Description
-----------
Generating a fresh generic plan for a range-partitioned table with 18
partition keys and two parameterized lower-bound clauses per key delays
statement_timeout cancellation. With statement_timeout set to 50 ms,
cancellation was not reported until 315–346 ms after the statement began.
The issue is localized to planning, and the backend remains healthy
afterward.
This weakens latency and resource-use limits for planning workloads with
this predicate shape. The demonstrated delay is less than 300 ms beyond the
configured limit.
Steps to reproduce
------------------
Run the following with psql:
\set ON_ERROR_STOP off
CREATE TABLE timeout_partprune
(
c01 int, c02 int, c03 int, c04 int, c05 int, c06 int,
c07 int, c08 int, c09 int, c10 int, c11 int, c12 int,
c13 int, c14 int, c15 int, c16 int, c17 int, c18 int
)
PARTITION BY RANGE
(c01, c02, c03, c04, c05, c06, c07, c08, c09,
c10, c11, c12, c13, c14, c15, c16, c17, c18);
CREATE TABLE timeout_partprune_default
PARTITION OF timeout_partprune DEFAULT;
SET plan_cache_mode = force_generic_plan;
SET statement_timeout = '50ms';
PREPARE timeout_q
(int, int, int, int, int, int, int, int, int,
int, int, int, int, int, int, int, int, int,
int, int, int, int, int, int, int, int, int,
int, int, int, int, int, int, int, int, int)
AS SELECT * FROM timeout_partprune
WHERE c01 >= $1 AND c01 >= $2
AND c02 >= $3 AND c02 >= $4
AND c03 >= $5 AND c03 >= $6
AND c04 >= $7 AND c04 >= $8
AND c05 >= $9 AND c05 >= $10
AND c06 >= $11 AND c06 >= $12
AND c07 >= $13 AND c07 >= $14
AND c08 >= $15 AND c08 >= $16
AND c09 >= $17 AND c09 >= $18
AND c10 >= $19 AND c10 >= $20
AND c11 >= $21 AND c11 >= $22
AND c12 >= $23 AND c12 >= $24
AND c13 >= $25 AND c13 >= $26
AND c14 >= $27 AND c14 >= $28
AND c15 >= $29 AND c15 >= $30
AND c16 >= $31 AND c16 >= $32
AND c17 >= $33 AND c17 >= $34
AND c18 >= $35 AND c18 >= $36;
\timing on
EXPLAIN (COSTS OFF) EXECUTE timeout_q
(1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,
1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1);
\timing off
Actual result
-------------
ERROR: canceling statement due to statement timeout
Time: 336.667 ms
Observed cancellation times across the tested versions ranged from 315 to
346 ms despite the 50 ms setting.
Expected result
---------------
The statement should report statement_timeout cancellation near the
configured 50 ms deadline instead of continuing partition-pruning plan
generation for approximately another 265–296 ms. statement_timeout is
intended to bound the duration of a statement, including planning, so
planner work must periodically service pending cancellation.
Additional information
----------------------
The issue reproduced on PostgreSQL 20devel, PostgreSQL 18.6, and PostgreSQL
17.11. plan_cache_mode was set to force_generic_plan; statement_timeout was
set to 50 ms.
Inference: this predicate shape appears to generate a Cartesian expansion of
partition-pruning clause prefixes without sufficiently frequent interrupt
checks.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #19692: Generic partition-pruning plan delays statement_timeout cancellation
@ 2026-09-17 22:40 Manuel Reyes Bravo <manuelreyesbravo@gmail.com>
parent: PG Bug reporting form <noreply@postgresql.org>
1 sibling, 0 replies; 3+ messages in thread
From: Manuel Reyes Bravo @ 2026-09-17 22:40 UTC (permalink / raw)
To: imchifan@163.com; pgsql-bugs@lists.postgresql.org
PG Bug reporting form <noreply@postgresql.org> wrote:
> Generating a fresh generic plan for a range-partitioned table with 18
> partition keys and two parameterized lower-bound clauses per key delays
> statement_timeout cancellation. With statement_timeout set to 50 ms,
> cancellation was not reported until 315-346 ms after the statement began.
Reproduced on master (e1d8f81d496), built with --enable-debug
--enable-cassert, so the absolute numbers here are larger than they would
be on a production build:
psql reports the error after 281-304 ms, five runs, statement_timeout 50ms.
> Inference: this predicate shape appears to generate a Cartesian expansion of
> partition-pruning clause prefixes without sufficiently frequent interrupt
> checks.
That is where the time goes, and there are no interrupt checks at all:
partprune.c has no CHECK_FOR_INTERRUPTS(). Instrumenting
gen_partprune_steps() to log elapsed time against the statement start on
the case above:
gen_partprune_steps entered at 0.383 ms
first recursive call at 0.402 ms
gen_partprune_steps returned at 130.026 ms, after 524250 recursive calls
get_steps_using_prefix_recurse() emits one pruning step per combination of
the clauses matched to each partition key, so with 18 keys and 2 clauses
per key the recursion runs ~2^19 times. None of those calls services a
pending cancellation.
The attached patch adds a CHECK_FOR_INTERRUPTS() to that recursion, for the
same reason commit 35313832248 added one to the planner in 2005 ("you can't
Query-Cancel a long planner run, because no part of the planner did
CHECK_FOR_INTERRUPTS()").
It is worth separating two things that the client-side number adds
together. Timing the server itself, with log_statement=all, and taking the
distance between the "statement:" log line and the timeout error:
server raises the error at
master 135 ms
master + patch 50 ms (statement_timeout = 50 ms)
So with the patch the backend honors the deadline. psql still reports
115 ms rather than 50 ms, but that remaining time elapses *after* the error
is raised, while the transaction aborts and the memory for the steps built
so far is released; it is not planner work that fails to check for
interrupts, and no additional CHECK_FOR_INTERRUPTS() will shorten it. I
have not looked into whether that part is worth doing anything about.
Adding a check to the loop in get_matching_partitions() as well made no
measurable difference on this case, because with the patch the statement is
already cancelled during step generation and never reaches it.
Tests: test_setup, create_index, partition_prune, partition_join and
inherit pass with the patch.
Regards,
Manu
Attachments:
[text/x-patch] bug19692-0001-Make-partition-pruning-step-generation-interruptible.patch (1.7K, ../../CA+bCEdAwPdtg6D-kePW+HeuJQZdqy=N9XXS5CFeHyNg1=Aux2A@mail.gmail.com/2-bug19692-0001-Make-partition-pruning-step-generation-interruptible.patch)
download | inline diff:
From 82d31451606348955e2bf1d9c6ef2426153ecaee Mon Sep 17 00:00:00 2001
From: Manu <manuelreyesbravo@gmail.com>
Date: Thu, 17 Sep 2026 19:10:53 -0300
Subject: [PATCH] Make partition pruning step generation interruptible
get_steps_using_prefix_recurse() generates one pruning step for each combination of the clauses matched to the partition keys, so the number of recursive calls is the product of the number of clauses matched to each key. For a range-partitioned table with 18 partition keys and two clauses per key that is about 520k calls taking ~120ms, none of which check for interrupts, so statement_timeout is not honored until the whole step list has been built.
Add a CHECK_FOR_INTERRUPTS() to the recursion, for the same reason commit 35313832248 added one to the planner.
Reported-by: Qifan Liu <imchifan@163.com>
---
src/backend/partitioning/partprune.c | 7 +++++++
1 file changed, 7 insertions(+)
diff --git a/src/backend/partitioning/partprune.c b/src/backend/partitioning/partprune.c
index 5e98f503244..0083bb7858f 100644
--- a/src/backend/partitioning/partprune.c
+++ b/src/backend/partitioning/partprune.c
@@ -2553,6 +2553,13 @@ get_steps_using_prefix_recurse(GeneratePruningStepsContext *context,
/* Actually, recursion would be limited by PARTITION_MAX_KEYS. */
check_stack_depth();
+ /*
+ * The number of combinations generated here is the product of the number
+ * of clauses matched to each partition key, so it can grow large enough
+ * that the statement becomes uncancellable for a noticeable time.
+ */
+ CHECK_FOR_INTERRUPTS();
+
Assert(start != NULL);
cur_keyno = ((PartClauseInfo *) lfirst(start))->keyno;
final_keyno = ((PartClauseInfo *) llast(prefix))->keyno;
--
2.55.0
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #19692: Generic partition-pruning plan delays statement_timeout cancellation
@ 2026-09-18 03:58 David Rowley <dgrowleyml@gmail.com>
parent: PG Bug reporting form <noreply@postgresql.org>
1 sibling, 0 replies; 3+ messages in thread
From: David Rowley @ 2026-09-18 03:58 UTC (permalink / raw)
To: imchifan@163.com; pgsql-bugs@lists.postgresql.org
On Thu, 17 Sept 2026 at 22:23, PG Bug reporting form
<noreply@postgresql.org> wrote:
> Generating a fresh generic plan for a range-partitioned table with 18
> partition keys and two parameterized lower-bound clauses per key delays
> statement_timeout cancellation. With statement_timeout set to 50 ms,
> cancellation was not reported until 315–346 ms after the statement began.
> The issue is localized to planning, and the backend remains healthy
> afterward.
There is nothing specifically slow about generating steps for a
partitioned table with exactly 18 partition keys. The point of
interest here is that the step generation can take a long time when
there are many partition keys and the query is complex.
I've pushed a patch that adds a CHECK_FOR_INTERRUPTS() in
get_steps_using_prefix_recurse().
David.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2026-09-18 03:58 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-09-17 08:49 BUG #19692: Generic partition-pruning plan delays statement_timeout cancellation PG Bug reporting form <noreply@postgresql.org>
2026-09-17 22:40 ` Manuel Reyes Bravo <manuelreyesbravo@gmail.com>
2026-09-18 03:58 ` David Rowley <dgrowleyml@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox