pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedbug: Gather rescan keeps the first scan's tuple bound in workers
2+ messages / 2 participants
[nested] [flat]
* bug: Gather rescan keeps the first scan's tuple bound in workers
@ 2026-09-18 22:57 Jeff Davis <pgsql@j-davis.com>
0 siblings, 1 reply; 2+ messages in thread
From: Jeff Davis @ 2026-09-18 22:57 UTC (permalink / raw)
To: pgsql-bugs; +Cc: Robert Haas <robertmhaas@gmail.com>
AI-discovered bug report appended to this email. I checked it out and
it's a legitimate wrong-results bug.
Regards,
Jeff Davis
Problem
-------
When a Gather or Gather Merge sits under a Limit whose count/offset
depends on a parameter, and the node is rescanned (inner side of a
nested loop, LATERAL, correlated subquery), the parallel workers keep
using the tuple bound from the first scan. If a later scan needs more
tuples than the first one did, workers stop early and the query
silently returns wrong results: too few rows, or -- with Gather Merge
over a Sort -- the right number of the wrong rows.
Affected branches and relevant commits
--------------------------------------
REL_14_STABLE through master, i.e. all supported branches; the code is
identical in each. Present since PG11.
3452dc5240d "Push tuple limits through Gather and Gather Merge"
(2017-08-29). Introduced the bug: added the tuple bound
to the workers' shared state, but only on the
initial-setup path.
3a1f8611f25 "Update parallel executor support to reuse the same DSM"
(2015-10-30). Added ExecParallelReinitialize(), the
rescan path that the commit above did not update.
Repro
-----
Self-contained, all settings at their defaults. Small values are
placed in the table's last blocks so that the workers, rather than the
leader (which starts scanning first), tend to read them.
CREATE TABLE big (a int, pad text);
INSERT INTO big
SELECT 2000000 - g, repeat('x', 100)
FROM generate_series(1, 2000000) g;
CREATE TABLE outer_t (n int);
INSERT INTO outer_t VALUES (1), (10);
ANALYZE big, outer_t;
SELECT o.n, s.arr
FROM outer_t o,
LATERAL (SELECT array_agg(a) arr
FROM (SELECT a FROM big
ORDER BY a LIMIT o.n) x) s;
The inner side is planned as
Limit -> Gather Merge (2 workers) -> Sort -> Parallel Seq Scan on big.
Expected:
n | arr
----+-----------------------
1 | {0}
10 | {0,1,2,3,4,5,6,7,8,9}
Actual (the wrong values vary from run to run, depending on which
process scans which blocks):
n | arr
----+--------------------------------
1 | {0}
10 | {0,44,45,46,47,48,49,50,51,52}
For a stable symptom, take the leader out of the scan. Each of the
two workers then returns only one row, the bound left over from the
first scan:
SET parallel_leader_participation = off;
-- same query
Expected, n = 10: {0,1,2,3,4,5,6,7,8,9}
Actual, n = 10: {0,44}
A plain Gather (no ORDER BY) behaves the same way with the leader out:
count(*) over "SELECT a FROM big LIMIT o.n" returns 2 instead of 10.
That plan is not chosen by default here; it needs
parallel_setup_cost = 0, parallel_tuple_cost = 0 and
min_parallel_table_scan_size = 0.
Diagnosis
---------
The bound reaches workers through
FixedParallelExecutorState.tuples_needed in the DSM segment. That
field is assigned only in ExecInitParallelPlan().
On rescan, ExecReScanLimit() -> recompute_limits() ->
ExecSetTupleBound() correctly updates
GatherState/GatherMergeState->tuples_needed in the leader. But
ExecGather()/ExecGatherMerge() then call ExecParallelReinitialize(),
which reuses the DSM segment and never rewrites fpes->tuples_needed.
The newly launched workers read the stale value in ParallelQueryMain()
and apply it twice: ExecSetTupleBound() makes the Sort below Gather
Merge a top-N sort with the old N, and ExecutorRun() is given the old
value as its row count.
The leader's own copy of the subplan has the fresh bound, which is why
the Gather Merge case yields a full-sized but incorrect result rather
than a short one. A bound that shrinks, or an unbounded first scan,
is harmless: it only forfeits the early exit.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: bug: Gather rescan keeps the first scan's tuple bound in workers
@ 2026-09-19 03:05 shihao zhong <zhong950419@gmail.com>
parent: Jeff Davis <pgsql@j-davis.com>
0 siblings, 0 replies; 2+ messages in thread
From: shihao zhong @ 2026-09-19 03:05 UTC (permalink / raw)
To: Jeff Davis <pgsql@j-davis.com>; +Cc: pgsql-bugs@postgresql.org, Robert Haas <robertmhaas@gmail.com>
Hi Jeff,
Thanks for reporting this bug.
I can reproduce this on master, and a 1000 row table is enough.
The attached 0001 adds a tuples_needed argument to
ExecParallelReinitialize() and stores it in the shared state, the same
way ExecInitParallelPlan() does. 0002 is a regression test and is
optional.
For the back branches this changes the signature of an exported
function. I don't know of any extension that calls it, but if that is
a concern, a new function could set the bound there instead.
Thanks,
Shihao
Attachments:
[application/octet-stream] v1-0001-Pass-the-current-tuple-bound-to-workers-on-Gather.patch (4.1K, ../../CAGRkXqRGE+TwD0Ttmb4b1J8Vy-drrkZYhh6hOf2_Uz5XKtXsEA@mail.gmail.com/3-v1-0001-Pass-the-current-tuple-bound-to-workers-on-Gather.patch)
download | inline diff:
From 1f47bcef6593b5e0d46591bf1b78ef849c269ba6 Mon Sep 17 00:00:00 2001
From: Shihao <zhong950419@gmail.com>
Date: Fri, 18 Sep 2026 22:44:38 -0400
Subject: [PATCH v1 1/2] Pass the current tuple bound to workers on Gather
rescan
Commit 3452dc5240d made Gather and Gather Merge pass the tuple bound
to the workers through the DSM segment. But the bound was only stored
by ExecInitParallelPlan(). ExecParallelReinitialize() reuses the
segment and left the old value in place. So after a rescan the new
workers still used the bound from the first scan.
If a Limit above the Gather depends on a parameter, a later scan can
need more tuples than the first one. The workers then stopped too
early, and the query returned too few rows. With Gather Merge over a
Sort it could return the right number of wrong rows.
Fix by adding a tuples_needed argument to ExecParallelReinitialize()
and storing it in the shared state, like ExecInitParallelPlan() does.
Reported-by: Jeff Davis <pgsql@j-davis.com>
Discussion: https://postgr.es/m/2701c83e33f3fe66320291fb5d1b74ca2cb90bd7.camel@j-davis.com
Backpatch-through: 14
---
src/backend/executor/execParallel.c | 10 +++++++++-
src/backend/executor/nodeGather.c | 3 ++-
src/backend/executor/nodeGatherMerge.c | 3 ++-
src/include/executor/execParallel.h | 3 ++-
4 files changed, 15 insertions(+), 4 deletions(-)
diff --git a/src/backend/executor/execParallel.c b/src/backend/executor/execParallel.c
index 6b85508a696..b7675409cd1 100644
--- a/src/backend/executor/execParallel.c
+++ b/src/backend/executor/execParallel.c
@@ -967,7 +967,8 @@ ExecParallelCreateReaders(ParallelExecutorInfo *pei)
void
ExecParallelReinitialize(PlanState *planstate,
ParallelExecutorInfo *pei,
- Bitmapset *sendParams)
+ Bitmapset *sendParams,
+ int64 tuples_needed)
{
EState *estate = planstate->state;
FixedParallelExecutorState *fpes;
@@ -989,6 +990,13 @@ ExecParallelReinitialize(PlanState *planstate,
fpes = shm_toc_lookup(pei->pcxt->toc, PARALLEL_KEY_EXECUTOR_FIXED, false);
+ /*
+ * The tuple bound might have changed since the last scan, for instance if
+ * a Limit node above us depends on a parameter. Pass the current value
+ * to the new batch of workers.
+ */
+ fpes->tuples_needed = tuples_needed;
+
/* Free any serialized parameters from the last round. */
if (DsaPointerIsValid(fpes->param_exec))
{
diff --git a/src/backend/executor/nodeGather.c b/src/backend/executor/nodeGather.c
index 31cd95adee7..bef118dea83 100644
--- a/src/backend/executor/nodeGather.c
+++ b/src/backend/executor/nodeGather.c
@@ -172,7 +172,8 @@ ExecGather(PlanState *pstate)
else
ExecParallelReinitialize(outerPlanState(node),
node->pei,
- gather->initParam);
+ gather->initParam,
+ node->tuples_needed);
/*
* Register backend workers. We might not get as many as we
diff --git a/src/backend/executor/nodeGatherMerge.c b/src/backend/executor/nodeGatherMerge.c
index 352a89ab53f..c96a333cd72 100644
--- a/src/backend/executor/nodeGatherMerge.c
+++ b/src/backend/executor/nodeGatherMerge.c
@@ -216,7 +216,8 @@ ExecGatherMerge(PlanState *pstate)
else
ExecParallelReinitialize(outerPlanState(node),
node->pei,
- gm->initParam);
+ gm->initParam,
+ node->tuples_needed);
/* Try to launch workers. */
pcxt = node->pei->pcxt;
diff --git a/src/include/executor/execParallel.h b/src/include/executor/execParallel.h
index 5a2034811d5..4ab902d5e12 100644
--- a/src/include/executor/execParallel.h
+++ b/src/include/executor/execParallel.h
@@ -44,7 +44,8 @@ extern void ExecParallelCreateReaders(ParallelExecutorInfo *pei);
extern void ExecParallelFinish(ParallelExecutorInfo *pei);
extern void ExecParallelCleanup(ParallelExecutorInfo *pei);
extern void ExecParallelReinitialize(PlanState *planstate,
- ParallelExecutorInfo *pei, Bitmapset *sendParams);
+ ParallelExecutorInfo *pei, Bitmapset *sendParams,
+ int64 tuples_needed);
extern void ParallelQueryMain(dsm_segment *seg, shm_toc *toc);
--
2.37.1 (Apple Git-137.1)
[application/octet-stream] v1-0002-Add-test-for-Gather-rescan-with-a-changing-tuple-.patch (3.5K, ../../CAGRkXqRGE+TwD0Ttmb4b1J8Vy-drrkZYhh6hOf2_Uz5XKtXsEA@mail.gmail.com/4-v1-0002-Add-test-for-Gather-rescan-with-a-changing-tuple-.patch)
download | inline diff:
From c85e4cd77d836b73201cefa47cfca776f26903f2 Mon Sep 17 00:00:00 2001
From: Shihao <zhong950419@gmail.com>
Date: Fri, 18 Sep 2026 22:44:39 -0400
Subject: [PATCH v1 2/2] Add test for Gather rescan with a changing tuple bound
Rescan a Limit over Gather Merge, with a limit count that comes from
the outer side of a nested loop and grows on the second scan. The
leader does not take part, so the result shows whether the workers
saw the new bound.
Discussion: https://postgr.es/m/2701c83e33f3fe66320291fb5d1b74ca2cb90bd7.camel@j-davis.com
---
src/test/regress/expected/select_parallel.out | 30 +++++++++++++++++++
src/test/regress/sql/select_parallel.sql | 13 ++++++++
2 files changed, 43 insertions(+)
diff --git a/src/test/regress/expected/select_parallel.out b/src/test/regress/expected/select_parallel.out
index e1344215644..4467944e179 100644
--- a/src/test/regress/expected/select_parallel.out
+++ b/src/test/regress/expected/select_parallel.out
@@ -526,6 +526,36 @@ select count(*) from tenk1
--reset the value of workers for each table as it was before this test.
alter table tenk1 set (parallel_workers = 4);
alter table tenk2 reset (parallel_workers);
+-- likewise, but the Limit's bound changes between rescans; the workers
+-- must see the new bound, not the one from the first scan.
+set parallel_leader_participation = off;
+explain (costs off)
+select v.n, ss.* from (values (1), (10)) v(n),
+ lateral (select count(*), sum(unique1) from
+ (select unique1 from tenk2 order by 1 limit v.n) x) ss;
+ QUERY PLAN
+----------------------------------------------------------
+ Nested Loop
+ -> Values Scan on "*VALUES*"
+ -> Aggregate
+ -> Limit
+ -> Gather Merge
+ Workers Planned: 4
+ -> Sort
+ Sort Key: tenk2.unique1
+ -> Parallel Seq Scan on tenk2
+(9 rows)
+
+select v.n, ss.* from (values (1), (10)) v(n),
+ lateral (select count(*), sum(unique1) from
+ (select unique1 from tenk2 order by 1 limit v.n) x) ss;
+ n | count | sum
+----+-------+-----
+ 1 | 1 | 0
+ 10 | 10 | 45
+(2 rows)
+
+reset parallel_leader_participation;
reset enable_material;
reset enable_bitmapscan;
reset enable_indexonlyscan;
diff --git a/src/test/regress/sql/select_parallel.sql b/src/test/regress/sql/select_parallel.sql
index 71a75bc86ea..df7d0901193 100644
--- a/src/test/regress/sql/select_parallel.sql
+++ b/src/test/regress/sql/select_parallel.sql
@@ -196,10 +196,23 @@ select count(*) from tenk1
left join (select tenk2.unique1 from tenk2 order by 1 limit 1000) ss
on tenk1.unique1 < ss.unique1 + 1
where tenk1.unique1 < 2;
+
--reset the value of workers for each table as it was before this test.
alter table tenk1 set (parallel_workers = 4);
alter table tenk2 reset (parallel_workers);
+-- likewise, but the Limit's bound changes between rescans; the workers
+-- must see the new bound, not the one from the first scan.
+set parallel_leader_participation = off;
+explain (costs off)
+select v.n, ss.* from (values (1), (10)) v(n),
+ lateral (select count(*), sum(unique1) from
+ (select unique1 from tenk2 order by 1 limit v.n) x) ss;
+select v.n, ss.* from (values (1), (10)) v(n),
+ lateral (select count(*), sum(unique1) from
+ (select unique1 from tenk2 order by 1 limit v.n) x) ss;
+reset parallel_leader_participation;
+
reset enable_material;
reset enable_bitmapscan;
reset enable_indexonlyscan;
--
2.37.1 (Apple Git-137.1)
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2026-09-19 03:05 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-09-18 22:57 bug: Gather rescan keeps the first scan's tuple bound in workers Jeff Davis <pgsql@j-davis.com>
2026-09-19 03:05 ` shihao zhong <zhong950419@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox