The following bug has been logged on the website:
Bug reference: 17548
Logged by: Chen huajun
Email address: chjischj@163.com
PostgreSQL version: 12.11
Operating system: CentOS Linux release 7.9.2009 (Core)
Description:
Aggregate queries on partitioned tables can cause out of memory error.
Examples are as follows
CREATE TABLE tbpart (
id int,
c1 int
) PARTITION BY HASH (c1);
create table tbpart_0 partition of tbpart for values with (MODULUS
4,remainder 0);
create table tbpart_1 partition of tbpart for values with (MODULUS
4,remainder 1);
create table tbpart_2 partition of tbpart for values with (MODULUS
4,remainder 2);
create table tbpart_3 partition of tbpart for values with (MODULUS
4,remainder 3);
insert into tbpart select id,id from generate_series(1,50000000)id;
postgres=# set max_parallel_workers_per_gather =0;
SET
postgres=# select id,count(*) from tbpart group by id;
ERROR: out of memory
DETAIL: Failed on request of size 1610612736 in memory context
"ExecutorState".
The call stack output by gdb is as follows:
Breakpoint 1, errstart (elevel=20, filename=0xbde3c3 "mcxt.c", lineno=910,
funcname=0xbde4a0 <__func__.6618> "MemoryContextAllocExtended",
domain=0x0) at elog.c:235
235 bool output_to_client = false;
(gdb) bt
#0 errstart (elevel=20, filename=0xbde3c3 "mcxt.c", lineno=910,
funcname=0xbde4a0 <__func__.6618> "MemoryContextAllocExtended",
domain=0x0) at elog.c:235
#1 0x0000000000a1b9ed in MemoryContextAllocExtended (context=0x19fbf70,
size=1610612736, flags=5) at mcxt.c:906
#2 0x00000000006a5b06 in tuplehash_allocate (type=0x1a10e28,
size=1610612736)
at ../../../src/include/lib/simplehash.h:319
#3 0x00000000006a5d33 in tuplehash_grow (tb=0x1a10e28, newsize=67108864)
at ../../../src/include/lib/simplehash.h:401
#4 0x00000000006a5f74 in tuplehash_insert (tb=0x1a10e28, key=0x0,
found=0x7ffc0c4c7457)
at ../../../src/include/lib/simplehash.h:526
#5 0x00000000006a6e8d in LookupTupleHashEntry (hashtable=0x1a11128,
slot=0x1a11098, isnew=0x7ffc0c4c7497)
at execGrouping.c:320
#6 0x00000000006c202f in lookup_hash_entry (aggstate=0x19fc330) at
nodeAgg.c:1480
#7 0x00000000006c214f in lookup_hash_entries (aggstate=0x19fc330) at
nodeAgg.c:1524
#8 0x00000000006c2905 in agg_fill_hash_table (aggstate=0x19fc330) at
nodeAgg.c:1937
#9 0x00000000006c21eb in ExecAgg (pstate=0x19fc330) at nodeAgg.c:1556
#10 0x00000000006b2be0 in ExecProcNodeFirst (node=0x19fc330) at
execProcnode.c:445
#11 0x00000000006a8b84 in ExecProcNode (node=0x19fc330) at
../../../src/include/executor/executor.h:242
#12 0x00000000006aaecb in ExecutePlan (estate=0x19fc080,
planstate=0x19fc330, use_parallel_mode=false,
operation=CMD_SELECT, sendTuples=true, numberTuples=0,
direction=ForwardScanDirection, dest=0x19d95a8,
execute_once=true) at execMain.c:1632
#13 0x00000000006a9061 in standard_ExecutorRun (queryDesc=0x19bf010,
direction=ForwardScanDirection, count=0,
execute_once=true) at execMain.c:350
#14 0x00000000006a8f06 in ExecutorRun (queryDesc=0x19bf010,
direction=ForwardScanDirection, count=0,
execute_once=true) at execMain.c:294
#15 0x00000000008819e3 in PortalRunSelect (portal=0x1982bf0, forward=true,
count=0, dest=0x19d95a8) at pquery.c:938
#16 0x00000000008816bd in PortalRun (portal=0x1982bf0,
count=9223372036854775807, isTopLevel=true, run_once=true,
dest=0x19d95a8, altdest=0x19d95a8, completionTag=0x7ffc0c4c7880 "") at
pquery.c:779
#17 0x000000000087bc09 in exec_simple_query (query_string=0x191cda0 "select
id,count(*) from tbpart group by id;")
at postgres.c:1215
#18 0x000000000087fbb9 in PostgresMain (argc=1, argv=0x1946d30,
dbname=0x1946bf8 "postgres",
username=0x1946bd8 "postgres") at postgres.c:4281
#19 0x00000000007e6bc9 in BackendRun (port=0x193ebd0) at postmaster.c:4510
#20 0x00000000007e63b0 in BackendStartup (port=0x193ebd0) at
postmaster.c:4193
#21 0x00000000007e2a87 in ServerLoop () at postmaster.c:1725
#22 0x00000000007e2360 in PostmasterMain (argc=3, argv=0x1917a40) at
postmaster.c:1398
#23 0x000000000070f54e in main (argc=3, argv=0x1917a40) at main.c:228
note: PG 14 does not have this problem