pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Rahul <rahul@rhyadav.dev>
To: Alexandra Wang <alexandra.wang.oss@gmail.com>
Cc: Samuel Olaoye <dapsalmy@gmail.com>
Cc: Pgsql Bugs <pgsql-bugs@lists.postgresql.org>
Subject: Re: Wrong results: hashed SubPlan referenced twice after OR-qual extraction reuses a stale hash table (13 to 19beta4)
Date: Sat, 17 Oct 2026 14:05:35 +0200 (CEST)
Message-ID: <P48k8Jv--N-9@rhyadav.dev> (raw)
In-Reply-To: <CAK98qZ29+QuHHYkpuCN5ZrvHyrSAQnuCzC-rVa+54Fp5pMTiRw@mail.gmail.com-P461mNg--J-9>
References: <179087936257.78721.1170374394656748858@mail.gmail.com>
	<P2v_Ach--F-9@rhyadav.dev>
	<CAK98qZ29+QuHHYkpuCN5ZrvHyrSAQnuCzC-rVa+54Fp5pMTiRw@mail.gmail.com-P461mNg--J-9>

Hi Alex,

On Fri, Oct 16, 2026, Alexandra Wang wrote:
> I tested v1-0001 on current master (6b3f8857aa8) and it looks good to
> me overall.

Thanks for testing and reviewing it!

> One minor question: with hashtablestale in place, do we still need the
> "planstate->chgParam != NULL" test in ExecHashSubPlan()?

No, I don't think we do.  The only place that sets chgParam on the
subplan of a hashed SubPlan is the subPlan loop in ExecReScan(), and
v1 already sets hashtablestale right there.  The attached v2 drops the
test, so the flag is the only signal.  Otherwise it's the same as v1,
rebased on current master.

> I also found a related bug in the EPQ recheck code, using the same
> query shape.

Nice catch.  I can reproduce it on current master (7787aec8ab), with
the same results you got: your FOR UPDATE query is fixed by your
change alone, and the FOR UPDATE variant of the join query needs both
fixes.

> Do you think this can go together with your fix?  Or would you prefer
> it as a separate patch?

I'd prefer a separate patch.  It's a different mechanism in different
code, and each fix can be committed without the other.  Could you post
it in a new thread with its own commitfest entry, along with your
isolation spec?  That also lets cfbot test each patch on its own.  A
link back to this thread would help, since the FOR UPDATE join query
needs both fixes.  I'm happy to review it.

With v2, the regression, isolation and contrib tests pass (I haven't
run the TAP tests).  I still owe back-branch versions; I'll post them
once the master version is settled.

Regards,
Rahul Yadav

Attachments:

v2-0001-Fix-reuse-of-stale-hash-tables-by-duplicated-hash.patch (application/octet-stream, 9.5K) download From 0b02ca1810b3a69a6072d8bc51f36adba6b2d62b Mon Sep 17 00:00:00 2001 From: Rahul Yadav <rahul@rhyadav.dev> Date: Sat, 17 Oct 2026 12:42:24 +0100 Subject: [PATCH v2] Fix reuse of stale hash tables by duplicated hashed SubPlans The same SubPlan can appear more than once in a plan tree. For example, when extract_restriction_or_clauses() extracts a restriction clause for one relation from a join OR clause, a SubPlan in the extracted sub-clauses ends up both in the new restriction clause and in the original join clause. Each reference gets its own SubPlanState, but they all share the subplan's PlanState. A hashed SubPlan whose subquery references outer query levels must rebuild its hash table whenever those parameters change. ExecHashSubPlan() detected that by checking chgParam of the subplan's PlanState. But buildSubPlanHash() rescans that PlanState, which clears its chgParam, so after one SubPlanState had rebuilt its hash table, the others no longer saw the change and kept probing hash tables built for previous parameter values, producing wrong query results. To fix, give each SubPlanState its own flag to mark its hash table as stale. ExecReScan() sets it, alongside propagating chgParam to the node's subplans, ExecHashSubPlan() checks it instead of the subplan's chgParam, and buildSubPlanHash() clears it. Reported-by: Samuel Olaoye <dapsalmy@gmail.com> Author: Rahul Yadav <rahul@rhyadav.dev> Reviewed-by: Alexandra Wang <alexandra.wang.oss@gmail.com> Discussion: https://postgr.es/m/179087936257.78721.1170374394656748858@mail.gmail.com Backpatch-through: 14 --- src/backend/executor/execAmi.c | 9 +++++ src/backend/executor/nodeSubplan.c | 12 ++++-- src/include/nodes/execnodes.h | 1 + src/test/regress/expected/subselect.out | 51 +++++++++++++++++++++++++ src/test/regress/sql/subselect.sql | 31 +++++++++++++++ 5 files changed, 101 insertions(+), 3 deletions(-) diff --git a/src/backend/executor/execAmi.c b/src/backend/executor/execAmi.c index 37fe03fdc3..a843a5dcff 100644 --- a/src/backend/executor/execAmi.c +++ b/src/backend/executor/execAmi.c @@ -116,6 +116,15 @@ ExecReScan(PlanState *node) if (splan->plan->extParam != NULL) UpdateChangedParamSet(splan, node->chgParam); + + /* + * Also mark this SubPlanState's hash table, if any, as stale. The + * subplan's chgParam isn't enough, since the subplan may be + * shared with other SubPlanStates, and building one of their hash + * tables rescans the subplan and clears its chgParam. + */ + if (splan->chgParam != NULL) + sstate->hashtablestale = true; } /* Well. Now set chgParam for child trees. */ if (outerPlanState(node) != NULL) diff --git a/src/backend/executor/nodeSubplan.c b/src/backend/executor/nodeSubplan.c index c6dd463c11..abf387775b 100644 --- a/src/backend/executor/nodeSubplan.c +++ b/src/backend/executor/nodeSubplan.c @@ -101,7 +101,6 @@ ExecHashSubPlan(SubPlanState *node, { bool result = false; SubPlan *subplan = node->subplan; - PlanState *planstate = node->planstate; TupleTableSlot *slot; /* Shouldn't have any direct correlation Vars */ @@ -110,9 +109,14 @@ ExecHashSubPlan(SubPlanState *node, /* * If first time through or we need to rescan the subplan, build the hash - * table. + * table. We can't detect the latter by checking the subplan's chgParam: + * if the same SubPlan appears more than once in the plan tree, all its + * SubPlanStates share one planstate, and building any of their hash + * tables rescans it and clears its chgParam. Instead, ExecReScan() sets + * our own hashtablestale flag whenever our parent node is rescanned with + * changed parameters that the subplan depends on. */ - if (node->hashtable == NULL || planstate->chgParam != NULL) + if (node->hashtable == NULL || node->hashtablestale) buildSubPlanHash(node, econtext); /* @@ -505,6 +509,7 @@ buildSubPlanHash(SubPlanState *node, ExprContext *econtext) */ node->havehashrows = false; node->havenullrows = false; + node->hashtablestale = false; nentries = planstate->plan->plan_rows; @@ -881,6 +886,7 @@ ExecInitSubPlan(SubPlan *subplan, PlanState *parent) sstate->projRight = NULL; sstate->hashtable = NULL; sstate->hashnulls = NULL; + sstate->hashtablestale = false; sstate->tuplesContext = NULL; sstate->innerecontext = NULL; sstate->keyColIdx = NULL; diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h index e3cea52e7d..79e74a1adc 100644 --- a/src/include/nodes/execnodes.h +++ b/src/include/nodes/execnodes.h @@ -1037,6 +1037,7 @@ typedef struct SubPlanState TupleHashTable hashnulls; /* hash table for rows with null(s) */ bool havehashrows; /* true if hashtable is not empty */ bool havenullrows; /* true if hashnulls is not empty */ + bool hashtablestale; /* hash tables must be rebuilt before use */ MemoryContext tuplesContext; /* context containing hash tables' tuples */ ExprContext *innerecontext; /* econtext for computing inner tuples */ int numCols; /* number of columns being hashed */ diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/expected/subselect.out index 6379bd6ca3..328338bebb 100644 --- a/src/test/regress/expected/subselect.out +++ b/src/test/regress/expected/subselect.out @@ -1475,6 +1475,57 @@ where o.ten = 0; 100 (1 row) +-- +-- Test rescan of a hashed subplan that appears in more than one qual. +-- Here the OR clause's restriction on "c" is extracted and also applied to +-- the scan of "c", so the same hashed SubPlan is evaluated in two places; +-- each must rebuild its hash table when the outer parameter changes. +-- +create temp table hsp_c (id int primary key, owner int); +create temp table hsp_p (id int primary key, cid int, pub bool); +create temp table hsp_f (cid int, uid int); +create temp table hsp_s (id int primary key, pid int, uid int); +insert into hsp_c values (1, 100), (2, 200); +insert into hsp_p values (10, 1, true), (20, 2, true); +insert into hsp_f values (1, 501), (2, 502); +insert into hsp_s values (1, 10, 501), (2, 20, 502); +analyze hsp_c, hsp_p, hsp_f, hsp_s; +explain (costs off) +select s.id, exists (select 1 from hsp_p p join hsp_c c on c.id = p.cid + where p.id = s.pid + and (c.owner = s.uid + or (p.pub and c.id in (select cid from hsp_f + where uid = s.uid)))) +from hsp_s s order by s.id; + QUERY PLAN +--------------------------------------------------------------------------------------------------------------------------------- + Sort + Sort Key: s.id + -> Seq Scan on hsp_s s + SubPlan exists_1 + -> Nested Loop + Join Filter: ((c.id = p.cid) AND ((c.owner = s.uid) OR (p.pub AND (ANY (c.id = (hashed SubPlan any_1).col1))))) + -> Seq Scan on hsp_p p + Filter: (id = s.pid) + -> Seq Scan on hsp_c c + Filter: ((owner = s.uid) OR (ANY (id = (hashed SubPlan any_1).col1))) + SubPlan any_1 + -> Seq Scan on hsp_f + Filter: (uid = s.uid) +(13 rows) + +select s.id, exists (select 1 from hsp_p p join hsp_c c on c.id = p.cid + where p.id = s.pid + and (c.owner = s.uid + or (p.pub and c.id in (select cid from hsp_f + where uid = s.uid)))) +from hsp_s s order by s.id; + id | exists +----+-------- + 1 | t + 2 | t +(2 rows) + -- -- Test rescan of a hashed SetOp node -- diff --git a/src/test/regress/sql/subselect.sql b/src/test/regress/sql/subselect.sql index c6cd275367..13bfe06470 100644 --- a/src/test/regress/sql/subselect.sql +++ b/src/test/regress/sql/subselect.sql @@ -738,6 +738,37 @@ select sum(ss.tst::int) from from onek i where i.unique1 = o.unique1 ) ss where o.ten = 0; +-- +-- Test rescan of a hashed subplan that appears in more than one qual. +-- Here the OR clause's restriction on "c" is extracted and also applied to +-- the scan of "c", so the same hashed SubPlan is evaluated in two places; +-- each must rebuild its hash table when the outer parameter changes. +-- +create temp table hsp_c (id int primary key, owner int); +create temp table hsp_p (id int primary key, cid int, pub bool); +create temp table hsp_f (cid int, uid int); +create temp table hsp_s (id int primary key, pid int, uid int); +insert into hsp_c values (1, 100), (2, 200); +insert into hsp_p values (10, 1, true), (20, 2, true); +insert into hsp_f values (1, 501), (2, 502); +insert into hsp_s values (1, 10, 501), (2, 20, 502); +analyze hsp_c, hsp_p, hsp_f, hsp_s; + +explain (costs off) +select s.id, exists (select 1 from hsp_p p join hsp_c c on c.id = p.cid + where p.id = s.pid + and (c.owner = s.uid + or (p.pub and c.id in (select cid from hsp_f + where uid = s.uid)))) +from hsp_s s order by s.id; + +select s.id, exists (select 1 from hsp_p p join hsp_c c on c.id = p.cid + where p.id = s.pid + and (c.owner = s.uid + or (p.pub and c.id in (select cid from hsp_f + where uid = s.uid)))) +from hsp_s s order by s.id; + -- -- Test rescan of a hashed SetOp node -- -- 2.50.1 (Apple Git-155)

view thread (9+ messages)  latest in thread

Message-ID: <P48k8Jv--N-9@rhyadav.dev>
Permalink:  ../P48k8Jv--N-9@rhyadav.dev/
Also on:    postgresql.org/message-id/P48k8Jv--N-9@rhyadav.dev

 ·  · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-bugs@postgresql.org
  Cc: rahul@rhyadav.dev, alexandra.wang.oss@gmail.com, dapsalmy@gmail.com
  Subject: Re: Wrong results: hashed SubPlan referenced twice after OR-qual extraction reuses a stale hash table (13 to 19beta4)
  In-Reply-To: <P48k8Jv--N-9@rhyadav.dev>
  References: <179087936257.78721.1170374394656748858@mail.gmail.com> <P2v_Ach--F-9@rhyadav.dev> <CAK98qZ29+QuHHYkpuCN5ZrvHyrSAQnuCzC-rVa+54Fp5pMTiRw@mail.gmail.com-P461mNg--J-9> <P48k8Jv--N-9@rhyadav.dev>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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