bug: Gather rescan keeps the first scan's tuple bound in workers

Поиск
Список
Период
Сортировка
Искать

bug: Gather rescan keeps the first scan's tuple bound in workers

От:
Jeff Davis <pgsql@j-davis.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. 



Re: bug: Gather rescan keeps the first scan's tuple bound in workers

От:
shihao zhong <zhong950419@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



Re: bug: Gather rescan keeps the first scan's tuple bound in workers

От:
Aleksander Alekseev <aleksander@tigerdata.com>
Дата:
Hi Shihao,

> 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.

I reviewed v1.  It applies cleanly to the `master` branch and the
tests pass. The fix looks correct and complete. One nitpick: 0002 adds
an empty line unrelated to the patch.

Other than that I believe the patch is RfC.

-- 
Best regards,
Aleksander Alekseev


Re: bug: Gather rescan keeps the first scan's tuple bound in workers

От:
Andrei Lepikhov <lepihov@gmail.com>
Дата:
On 19/09/2026 05:05, shihao zhong wrote:
> 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.

I think you should merge patches 0001 and 0002. While you’re at it, you could
add a couple of words pointing out that the tuples_needed field of the
FixedParallelExecutorState structure needs to be updated on each rescan.

Also, for the back-patch you should add a wrapper for
ExecParallelReinitialize(), so that its existing signature stays intact in the
stable branches.

One more thing: the test covers only Gather Merge. It would be good to add the
same query without ORDER BY in the subquery, so that the plain Gather path is
exercised too.

-- 
regards, Andrei Lepikhov,
pgEdge


Re: bug: Gather rescan keeps the first scan's tuple bound in workers

От:
shihao zhong <zhong950419@gmail.com>
Дата:
Вложения:
Hi,

Thanks Aleksander and Andrei for the reviews.

v2 is one patch now, and backport version are attached.

Thanks,
Shihao
FAQ