Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit9eb6b65

Browse files
committed
Correctly re-use hash tables in buildSubPlanHash().
Commit356687b omitted to remove leftover code for destroyinga hashed subplan's hash tables, with the result that the tableswere always rebuilt not reused; this leads to severe memoryleakage if a hashed subplan is re-executed enough times.Moreover, the code for reusing the hashnulls table had a typothat would have made it do the wrong thing if it were reached.Looking at the code coverage report shows severe under-coverageof the potential callers of ResetTupleHashTable, so add some testcases that exercise them.Andreas Karlsson and Tom Lane, per reports from Ranier Vilelaand Justin Pryzby.Backpatch to v11, as the faulty commit was.Discussion:https://postgr.es/m/edb62547-c453-c35b-3ed6-a069e4d6b937@proxel.seDiscussion:https://postgr.es/m/CAEudQAo=DCebm1RXtig9OH+QivpS97sMkikt0A9qHmMUs+g6ZA@mail.gmail.comDiscussion:https://postgr.es/m/20200210032547.GA1412@telsasoft.com
1 parent7ea20a2 commit9eb6b65

File tree

3 files changed

+191
-3
lines changed

3 files changed

+191
-3
lines changed

‎src/backend/executor/nodeSubplan.c

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -496,8 +496,6 @@ buildSubPlanHash(SubPlanState *node, ExprContext *econtext)
496496
* need to store subplan output rows that contain NULL.
497497
*/
498498
MemoryContextReset(node->hashtablecxt);
499-
node->hashtable=NULL;
500-
node->hashnulls=NULL;
501499
node->havehashrows= false;
502500
node->havenullrows= false;
503501

@@ -533,7 +531,7 @@ buildSubPlanHash(SubPlanState *node, ExprContext *econtext)
533531
}
534532

535533
if (node->hashnulls)
536-
ResetTupleHashTable(node->hashtable);
534+
ResetTupleHashTable(node->hashnulls);
537535
else
538536
node->hashnulls=BuildTupleHashTableExt(node->parent,
539537
node->descRight,
@@ -548,6 +546,8 @@ buildSubPlanHash(SubPlanState *node, ExprContext *econtext)
548546
node->hashtempcxt,
549547
false);
550548
}
549+
else
550+
node->hashnulls=NULL;
551551

552552
/*
553553
* We are probably in a short-lived expression-evaluation context. Switch

‎src/test/regress/expected/subselect.out

Lines changed: 123 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -854,6 +854,129 @@ explain (verbose, costs off)
854854
One-Time Filter: ("*VALUES*".column1 = "*VALUES*".column1)
855855
(8 rows)
856856

857+
--
858+
-- Test rescan of a hashed subplan (the use of random() is to prevent the
859+
-- sub-select from being pulled up, which would result in not hashing)
860+
--
861+
explain (verbose, costs off)
862+
select sum(ss.tst::int) from
863+
onek o cross join lateral (
864+
select i.ten in (select f1 from int4_tbl where f1 <= o.hundred) as tst,
865+
random() as r
866+
from onek i where i.unique1 = o.unique1 ) ss
867+
where o.ten = 0;
868+
QUERY PLAN
869+
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
870+
Aggregate
871+
Output: sum((((hashed SubPlan 1)))::integer)
872+
-> Nested Loop
873+
Output: ((hashed SubPlan 1))
874+
-> Seq Scan on public.onek o
875+
Output: o.unique1, o.unique2, o.two, o.four, o.ten, o.twenty, o.hundred, o.thousand, o.twothousand, o.fivethous, o.tenthous, o.odd, o.even, o.stringu1, o.stringu2, o.string4
876+
Filter: (o.ten = 0)
877+
-> Index Scan using onek_unique1 on public.onek i
878+
Output: (hashed SubPlan 1), random()
879+
Index Cond: (i.unique1 = o.unique1)
880+
SubPlan 1
881+
-> Seq Scan on public.int4_tbl
882+
Output: int4_tbl.f1
883+
Filter: (int4_tbl.f1 <= $0)
884+
(14 rows)
885+
886+
select sum(ss.tst::int) from
887+
onek o cross join lateral (
888+
select i.ten in (select f1 from int4_tbl where f1 <= o.hundred) as tst,
889+
random() as r
890+
from onek i where i.unique1 = o.unique1 ) ss
891+
where o.ten = 0;
892+
sum
893+
-----
894+
100
895+
(1 row)
896+
897+
--
898+
-- Test rescan of a SetOp node
899+
--
900+
explain (costs off)
901+
select count(*) from
902+
onek o cross join lateral (
903+
select * from onek i1 where i1.unique1 = o.unique1
904+
except
905+
select * from onek i2 where i2.unique1 = o.unique2
906+
) ss
907+
where o.ten = 1;
908+
QUERY PLAN
909+
------------------------------------------------------------------------------
910+
Aggregate
911+
-> Nested Loop
912+
-> Seq Scan on onek o
913+
Filter: (ten = 1)
914+
-> Subquery Scan on ss
915+
-> HashSetOp Except
916+
-> Append
917+
-> Subquery Scan on "*SELECT* 1"
918+
-> Index Scan using onek_unique1 on onek i1
919+
Index Cond: (unique1 = o.unique1)
920+
-> Subquery Scan on "*SELECT* 2"
921+
-> Index Scan using onek_unique1 on onek i2
922+
Index Cond: (unique1 = o.unique2)
923+
(13 rows)
924+
925+
select count(*) from
926+
onek o cross join lateral (
927+
select * from onek i1 where i1.unique1 = o.unique1
928+
except
929+
select * from onek i2 where i2.unique1 = o.unique2
930+
) ss
931+
where o.ten = 1;
932+
count
933+
-------
934+
100
935+
(1 row)
936+
937+
--
938+
-- Test rescan of a RecursiveUnion node
939+
--
940+
explain (costs off)
941+
select sum(o.four), sum(ss.a) from
942+
onek o cross join lateral (
943+
with recursive x(a) as
944+
(select o.four as a
945+
union
946+
select a + 1 from x
947+
where a < 10)
948+
select * from x
949+
) ss
950+
where o.ten = 1;
951+
QUERY PLAN
952+
---------------------------------------------------
953+
Aggregate
954+
-> Nested Loop
955+
-> Seq Scan on onek o
956+
Filter: (ten = 1)
957+
-> CTE Scan on x
958+
CTE x
959+
-> Recursive Union
960+
-> Result
961+
-> WorkTable Scan on x x_1
962+
Filter: (a < 10)
963+
(10 rows)
964+
965+
select sum(o.four), sum(ss.a) from
966+
onek o cross join lateral (
967+
with recursive x(a) as
968+
(select o.four as a
969+
union
970+
select a + 1 from x
971+
where a < 10)
972+
select * from x
973+
) ss
974+
where o.ten = 1;
975+
sum | sum
976+
------+------
977+
1700 | 5350
978+
(1 row)
979+
857980
--
858981
-- Check we behave sanely in corner case of empty SELECT list (bug #8648)
859982
--

‎src/test/regress/sql/subselect.sql

Lines changed: 65 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -476,6 +476,71 @@ explain (verbose, costs off)
476476
select x, xfrom
477477
(select (select random()where y=y)as xfrom (values(1),(2)) v(y)) ss;
478478

479+
--
480+
-- Test rescan of a hashed subplan (the use of random() is to prevent the
481+
-- sub-select from being pulled up, which would result in not hashing)
482+
--
483+
explain (verbose, costs off)
484+
selectsum(ss.tst::int)from
485+
onek ocross join lateral (
486+
selecti.tenin (select f1from int4_tblwhere f1<=o.hundred)as tst,
487+
random()as r
488+
from onek iwherei.unique1=o.unique1 ) ss
489+
whereo.ten=0;
490+
491+
selectsum(ss.tst::int)from
492+
onek ocross join lateral (
493+
selecti.tenin (select f1from int4_tblwhere f1<=o.hundred)as tst,
494+
random()as r
495+
from onek iwherei.unique1=o.unique1 ) ss
496+
whereo.ten=0;
497+
498+
--
499+
-- Test rescan of a SetOp node
500+
--
501+
explain (costs off)
502+
selectcount(*)from
503+
onek ocross join lateral (
504+
select*from onek i1wherei1.unique1=o.unique1
505+
except
506+
select*from onek i2wherei2.unique1=o.unique2
507+
) ss
508+
whereo.ten=1;
509+
510+
selectcount(*)from
511+
onek ocross join lateral (
512+
select*from onek i1wherei1.unique1=o.unique1
513+
except
514+
select*from onek i2wherei2.unique1=o.unique2
515+
) ss
516+
whereo.ten=1;
517+
518+
--
519+
-- Test rescan of a RecursiveUnion node
520+
--
521+
explain (costs off)
522+
selectsum(o.four),sum(ss.a)from
523+
onek ocross join lateral (
524+
with recursive x(a)as
525+
(selecto.fouras a
526+
union
527+
select a+1from x
528+
where a<10)
529+
select*from x
530+
) ss
531+
whereo.ten=1;
532+
533+
selectsum(o.four),sum(ss.a)from
534+
onek ocross join lateral (
535+
with recursive x(a)as
536+
(selecto.fouras a
537+
union
538+
select a+1from x
539+
where a<10)
540+
select*from x
541+
) ss
542+
whereo.ten=1;
543+
479544
--
480545
-- Check we behave sanely in corner case of empty SELECT list (bug #8648)
481546
--

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp