BUG #17548: Aggregate queries on partitioned tables can cause OOM. - Mailing list pgsql-bugs

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