From: ocean_li_996 <ocean_li_996@163.com>
To: mohen.lhy@alibaba-inc.com <mohen.lhy@alibaba-inc.com>
To: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Cc: feichanghong@qq.com
Cc: jdavis@postgresql.org
Subject: Re:BUG #19040: Memory leak in hashed subplan node due to missing hashtempcxt reset
Date: Wed, 3 Sep 2025 09:43:12 +0800 (CST)
Message-ID: <58062871.112.1990d3e06ea.Coremail.ocean_li_996@163.com> (raw)
In-Reply-To: <19040-c9b6073ef814f48c@postgresql.org>
References: <19040-c9b6073ef814f48c@postgresql.org>
For reasons I'm not yet aware of, my email reply to the bug report
did not show up in the discussion thread. I am therefore resending
the patch in a new email.
I've attached 'v01_fix_memory_leak_in_hashed_subplan_node.patch' to address this.
--
Thanks,
Haiyang Li
----- Original Message -----
From: "PG Bug reporting form" <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: mohen.lhy@alibaba-inc.com
Sent: Tue, 02 Sep 2025 15:58:49 +0000
Subject: BUG #19040: Memory leak in hashed subplan node due to missing hashtempcxt reset
The following bug has been logged on the website:
Bug reference: 19040
Logged by: haiyang li
Email address: mohen.lhy@alibaba-inc.com
PostgreSQL version: 18beta3
Operating system: centos7 5.10.84 x86_64
Description:
Hello, all!
I found a query which consumes a lot of memory and triggers OOM killer.
Memory leak occurs in hashed subplan node.
I was able to create reproducible test case on machine with default config
and postgresql 18beta3:
CREATE TABLE test1(
a numeric,
b int);
INSERT INTO
test1
SELECT
i,
i
FROM
generate_series(1, 30000000) i; -- Make the running time longer
EXPLAIN ANALYZE SELECT
*
FROM
test1
WHERE
a NOT IN(
SELECT
i
FROM
generate_series(1, 10000) i
);
plan:
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
Seq Scan on test1 (cost=125.00..612432.24 rows=15000108 width=10) (actual
time=135.191..25832.808 rows=29990000 loops=1)
Filter: (NOT (hashed SubPlan 1))
Rows Removed by Filter: 10000
SubPlan 1
-> Function Scan on generate_series i (cost=0.00..100.00 rows=10000
width=4) (actual time=36.999..38.296 rows=10000 loops=1)
Planning Time: 0.280 ms
JIT:
Functions: 15
Options: Inlining true, Optimization true, Expressions true, Deforming
true
Timing: Generation 1.155 ms, Inlining 25.929 ms, Optimization 60.700 ms,
Emission 23.018 ms, Total 110.802 ms
Execution Time: 28217.026 ms
(11 rows)
I observed that the process's RES (resident memory) was increasing rapidly
during SQL execution by using 'top -p <pid>' command.
Furthermore, during SQL execution, I ran 'select
pg_log_backend_memory_contexts(<pid>)'
to print memory context statistics. The context with abnormally high memory
usage was
"Subplan HashTable Temp Context." The key part of the log is as follows:
...
LOG: level: 5; Subplan HashTable Temp Context: 514834432 total in 62849
blocks; 973712 free (60695 chunks); 513860720 used
LOG: level: 5; Subplan HashTable Context: 524288 total in 7 blocks; 123808
free (5 chunks); 400480 used
...
Grand total: 518275344 bytes in 63116 blocks; 2025560 free (60976 chunks);
516249784 used
...
If I change the SQL from "a NOT IN" to "b NOT IN" and do the same action, I
can not
observe abnormally high memory usage. Likewise, the key part of the log is
as follows:
...
LOG: level: 5; Subplan HashTable Temp Context: 1024 total in 1 blocks; 784
free (0 chunks); 240 used
LOG: level: 5; Subplan HashTable Context: 524288 total in 7 blocks; 123808
free (5 chunks); 400480 used
...
Grand total: 3441936 bytes in 268 blocks; 1050520 free (281 chunks); 2391416
used
...
While analyzing the source code, I found that the hashed subplan node fails
to reset
the 'hashtempcxt' context after probing the hash table for each slot.
When variable-length datatypes (e.g., numeric) are processed, this can
trigger calls
to 'detoast_attr', which allocate memory in hashtempcxt. Without a reset,
this memory
is not reclaimed until the context itself is destroyed, resulting in a
memory leak
when processing large numbers of slots.
A patch implementing this fix will be included in the follow-up email.
--
Thanks,
Haiyang Li
Attachments:
[application/octet-stream] v01_fix_memory_leak_in_hashed_subplan_node.patch (4.2K, ../58062871.112.1990d3e06ea.Coremail.ocean_li_996@163.com/3-v01_fix_memory_leak_in_hashed_subplan_node.patch)
download | inline diff:diff --git a/src/backend/executor/nodeSubplan.c b/src/backend/executor/nodeSubplan.cindex f7f6fc2da0b..94968c9dbfc 100644--- a/src/backend/executor/nodeSubplan.c+++ b/src/backend/executor/nodeSubplan.c@@ -156,21 +156,36 @@ ExecHashSubPlan(SubPlanState *node,
*/
if (slotNoNulls(slot))
{
- if (node->havehashrows &&- FindTupleHashEntry(node->hashtable,- slot,- node->cur_eq_comp,- node->lhs_hash_expr) != NULL)+ if (node->havehashrows)
{
- ExecClearTuple(slot);- return BoolGetDatum(true);+ TupleHashEntry entry = FindTupleHashEntry(node->hashtable,+ slot,+ node->cur_eq_comp,+ node->lhs_hash_expr);+ + + /* Must reset temp context after each hashtable lookup */+ MemoryContextReset(node->hashtable->tempcxt);++ if (entry != NULL)+ {+ ExecClearTuple(slot);+ return BoolGetDatum(true);+ }
}
- if (node->havenullrows &&- findPartialMatch(node->hashnulls, slot, node->cur_eq_funcs))+ if (node->havenullrows)
{
- ExecClearTuple(slot);- *isNull = true;- return BoolGetDatum(false);+ bool found = findPartialMatch(node->hashnulls, slot, node->cur_eq_funcs);++ /* Must reset temp context after each hashtable lookup */+ MemoryContextReset(node->hashnulls->tempcxt);++ if (found)+ {+ ExecClearTuple(slot);+ *isNull = true;+ return BoolGetDatum(false);+ }
}
ExecClearTuple(slot);
return BoolGetDatum(false);
@@ -629,11 +644,15 @@ buildSubPlanHash(SubPlanState *node, ExprContext *econtext)
if (slotNoNulls(slot))
{
(void) LookupTupleHashEntry(node->hashtable, slot, &isnew, NULL);
+ /* Must reset temp context after each hashtable lookup */+ MemoryContextReset(node->hashtable->tempcxt);
node->havehashrows = true;
}
else if (node->hashnulls)
{
(void) LookupTupleHashEntry(node->hashnulls, slot, &isnew, NULL);
+ /* Must reset temp context after each hashtable lookup */+ MemoryContextReset(node->hashnulls->tempcxt);
node->havenullrows = true;
}
diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/expected/subselect.outindex 40d8056fcea..394568dbe4c 100644--- a/src/test/regress/expected/subselect.out+++ b/src/test/regress/expected/subselect.out@@ -889,6 +889,25 @@ select * from outer_text where (f1, f2) not in (select * from inner_text);
-- Another test case for cross-type hashed subplans: comparison of
-- inner-side values must be done with appropriate operator
--
+--+-- Test case for memory leak in hashed subplan node+--+create temp table memory_leak (c1 numeric, c2 int4);+insert into memory_leak+select i, i from generate_series(1, 3000000) i;+explain (verbose, costs off)+select c1, c2 from memory_leak where c1 not in( select i from generate_series(1, 10000) i);+ QUERY PLAN +-----------------------------------------------------------------------------+ Seq Scan on pg_temp.memory_leak+ Output: memory_leak.c1, memory_leak.c2+ Filter: (NOT (ANY (memory_leak.c1 = ((hashed SubPlan 1).col1)::numeric)))+ SubPlan 1+ -> Function Scan on pg_catalog.generate_series i+ Output: i.i+ Function Call: generate_series(1, 10000)+(7 rows)+
explain (verbose, costs off)
select 'foo'::text in (select 'bar'::name union all select 'bar'::name);
QUERY PLAN
diff --git a/src/test/regress/sql/subselect.sql b/src/test/regress/sql/subselect.sqlindex fec38ef85a6..9fd3d997e62 100644--- a/src/test/regress/sql/subselect.sql+++ b/src/test/regress/sql/subselect.sql@@ -503,6 +503,17 @@ select * from outer_text where (f1, f2) not in (select * from inner_text);
-- inner-side values must be done with appropriate operator
--
+--+-- Test case for memory leak in hashed subplan node+--+create temp table memory_leak (c1 numeric, c2 int4);++insert into memory_leak+select i, i from generate_series(1, 3000000) i;++explain (verbose, costs off)+select c1, c2 from memory_leak where c1 not in( select i from generate_series(1, 10000) i);+
explain (verbose, costs off)
select 'foo'::text in (select 'bar'::name union all select 'bar'::name);
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 using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: ocean_li_996@163.com, mohen.lhy@alibaba-inc.com, pgsql-bugs@lists.postgresql.org, feichanghong@qq.com, jdavis@postgresql.org
Subject: Re:BUG #19040: Memory leak in hashed subplan node due to missing hashtempcxt reset
In-Reply-To: <58062871.112.1990d3e06ea.Coremail.ocean_li_996@163.com>
* 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