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.cindex 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.cindex 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.hindex 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.outindex 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.sqlindex 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)
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