Re: BUG #18909: Query creates millions of temporary files and stalls

If helpful here's the bt full during the problem (pg was compiled with -O0 -g3 -ggdb)

#0 0x00007f229bd96c8b in __libc_open64 (file=0x7ffffb4c9b80 "base/pgsql_tmp/pgsql_tmp35816.189548", oflag=578)
at ../sysdeps/unix/sysv/linux/open64.c:48
resultvar = 495
sc_ret = <optimized out>
mode = 384
#1 0x00005615d86aef93 in BasicOpenFilePerm (fileName=0x7ffffb4c9b80 "base/pgsql_tmp/pgsql_tmp35816.189548", fileFlags=578,
fileMode=384) at fd.c:1125
fd = 22037
__func__ = "BasicOpenFilePerm"
#2 0x00005615d86af8d6 in PathNameOpenFilePerm (fileName=0x7ffffb4c9b80 "base/pgsql_tmp/pgsql_tmp35816.189548",
fileFlags=578, fileMode=384) at fd.c:1603
fnamecopy = 0x56173e322160 "base/pgsql_tmp/pgsql_tmp35816.189548"
file = 190724
vfdP = 0x7f1a5539a8f0
__func__ = "PathNameOpenFilePerm"
#3 0x00005615d86af822 in PathNameOpenFile (fileName=0x7ffffb4c9b80 "base/pgsql_tmp/pgsql_tmp35816.189548", fileFlags=578)
at fd.c:1568
No locals.
#4 0x00005615d86afcea in OpenTemporaryFileInTablespace (tblspcOid=1663, rejectError=true) at fd.c:1806
tempdirpath = "base/pgsql_tmp\000\000\070\231L\373\377\177\000\000\240\037\000\000\377\377", '\000' <repeats 89 times>, "\200\377?", '\000' <repeats 13
times>,
"\200\206\373p\330\025V\000\000\000\000\000\000\000\000\000\000\f\000\000\000\000\000\000\000X\240L\373\377\177\000\000(at)\300b\332\025V\000\000РL\373\377\177\000\
000\305\003q\330\025V\000\000\253t\334<\000\000\000\000G"...
tempfilepath =
"base/pgsql_tmp/pgsql_tmp35816.189548\000V\000\000\022\000\000\000\000\000\000\000\220\235L\373\377\177\000\000\340l\322g\032\177\000\000\002", '\000' <repeats
63 times>, "t\234L\373\377\177\000\000x\234L\373\377\177\000\000_\244?$<\327\002\000\205\303\r\004<\327\002\000\000\000\000\000\000\000\000\000\332\340\061
\000\000\000\000\260\234L\373\377\177\000\000\060\227\212\330\025V\000\000\200\234L\373\377\177\000\000"...
file = 22037
__func__ = "OpenTemporaryFileInTablespace"
#5 0x00005615d86afb6e in OpenTemporaryFile (interXact=false) at fd.c:1743
file = 0
#6 0x00005615d86ac995 in BufFileCreateTemp (interXact=false) at buffile.c:204
file = 0x100000000000003
pfile = -1879610502
#7 0x00005615d84fb31c in ExecHashJoinSaveTuple (tuple=0x5615da85f5e8, hashvalue=2415356794, fileptr=0x7f1a4f685a80)
at nodeHashjoin.c:1248
file = 0x0
#8 0x00005615d84f6a59 in ExecHashTableInsert (hashtable=0x5615da85e5c0, slot=0x5615da823378, hashvalue=2415356794)
at nodeHash.c:1714
shouldFree = true
tuple = 0x5615da85f5e8
bucketno = 32992122
batchno = 3521863
#9 0x00005615d84f3fb1 in MultiExecPrivateHash (node=0x5615da6c2f18) at nodeHash.c:188
bucketNumber = -1
outerNode = 0x5615da6c2638
hashkeys = 0x5615da85ba48
hashtable = 0x5615da85e5c0
slot = 0x5615da823378
econtext = 0x5615da7a9c28
hashvalue = 2415356794
#10 0x00005615d84f3e63 in MultiExecHash (node=0x5615da6c2f18) at nodeHash.c:115
No locals.
#11 0x00005615d84d532b in MultiExecProcNode (node=0x5615da6c2f18) at execProcnode.c:520
result = 0x1
__func__ = "MultiExecProcNode"
#12 0x00005615d84f9ca8 in ExecHashJoinImpl (pstate=0x5615da6c18e8, parallel=false) at nodeHashjoin.c:297
node = 0x5615da6c18e8
outerNode = 0x5615da6c1bd8
hashNode = 0x5615da6c2f18
joinqual = 0x0
otherqual = 0x0
econtext = 0x5615da6c1af8
hashtable = 0x5615da85e5c0
outerTupleSlot = 0x0
hashvalue = 0
batchno = 0
parallel_state = 0x0
__func__ = "ExecHashJoinImpl"
#13 0x00005615d84fa350 in ExecHashJoin (pstate=0x5615da6c18e8) at nodeHashjoin.c:621
No locals.
#14 0x00005615d84d5241 in ExecProcNodeFirst (node=0x5615da6c18e8) at execProcnode.c:464
No locals.
#15 0x00005615d84c9dc3 in ExecProcNode (node=0x5615da6c18e8) at ../../../src/include/executor/executor.h:262
No locals.
#16 0x00005615d84cc539 in ExecutePlan (queryDesc=0x5615da6d6cc8, operation=CMD_SELECT, sendTuples=true, numberTuples=100000,
direction=ForwardScanDirection, dest=0x5615da56e0d8) at execMain.c:1640
estate = 0x5615da6c1688
planstate = 0x5615da6c18e8
use_parallel_mode = false
slot = 0x5615da56e0d8
current_tuple_count = 0
#17 0x00005615d84ca298 in standard_ExecutorRun (queryDesc=0x5615da6d6cc8, direction=ForwardScanDirection, count=100000,
execute_once=false) at execMain.c:362
estate = 0x5615da6c1688
operation = CMD_SELECT
dest = 0x5615da56e0d8
sendTuples = true
oldcontext = 0x5615da6c3580
#18 0x00007f229c17f6d5 in explain_ExecutorRun (queryDesc=0x5615da6d6cc8, direction=ForwardScanDirection, count=100000,
execute_once=<optimized out>) at auto_explain.c:322
_save_exception_stack = 0x7ffffb4ca900
_save_context_stack = 0x0
_local_sigjmp_buf = {{
__jmpbuf = {1, -1720014361324494981, 94651820578848, 140737409496096, 0, 0, -1720014361087516805,
-1613547901269909637},
__mask_was_saved = 0,
__saved_mask = {
__val = {0, 94651857416032, 0, 0, 0, 0, 0, 140737409492960, 94651827305323, 24, 94651857416032, 0,
94651857416456, 140737409493008, 94651827395998, 4294967296}
}
}}
_do_rethrow = false
#19 0x00005615d84ca18b in ExecutorRun (queryDesc=0x5615da6d6cc8, direction=ForwardScanDirection, count=100000,
execute_once=false) at execMain.c:309
No locals.
#20 0x00005615d86ed0a7 in PortalRunSelect (portal=0x5615da616338, forward=true, count=100000, dest=0x5615da56e0d8)
at pquery.c:922
queryDesc = 0x5615da6d6cc8
direction = ForwardScanDirection
nprocessed = 0
__func__ = "PortalRunSelect"
#21 0x00005615d86ee021 in DoPortalRunFetch (portal=0x5615da616338, fdirection=FETCH_FORWARD, count=100000,
dest=0x5615da56e0d8) at pquery.c:1674
forward = true
__func__ = "DoPortalRunFetch"
#22 0x00005615d86eda7f in PortalRunFetch (portal=0x5615da616338, fdirection=FETCH_FORWARD, count=100000, dest=0x5615da56e0d8)
at pquery.c:1425
_save_exception_stack = 0x7ffffb4caca0
_save_context_stack = 0x0
_local_sigjmp_buf = {{
__jmpbuf = {1, -1720014361253191813, 94651820578848, 140737409496096, 0, 0, -1720014361318203525,
-4902996909213425797},
__mask_was_saved = 0,
__saved_mask = {
__val = {2786264749430538240, 94651857065712, 94651858125368, 140737409493456, 64, 94651827132028,
94651858128616, 7, 0, 7, 94651858125512, 94651858128672, 94651858131912, 140737409493472, 94651827135315, 0}
}
}}
_do_rethrow = false
result = 94651858117432
saveActivePortal = 0x5615da616228
saveResourceOwner = 0x5615da571c20
savePortalContext = 0x5615da56df90
oldContext = 0x5615da56df90
__func__ = "PortalRunFetch"
#23 0x00005615d84553a9 in PerformPortalFetch (stmt=0x5615da515710, dest=0x5615da56e0d8, qc=0x7ffffb4cac30)
at portalcmds.c:198
portal = 0x5615da616338
nprocessed = 140737409493584
__func__ = "PerformPortalFetch"
#24 0x00005615d86eec72 in standard_ProcessUtility (pstmt=0x5615da515a30,
queryString=0x5615da514d08 "FETCH FORWARD 100000 FROM \"sqlutilcursor\"", readOnlyTree=false,
context=PROCESS_UTILITY_TOPLEVEL, params=0x0, queryEnv=0x0, dest=0x5615da56e0d8, qc=0x7ffffb4cac30) at utility.c:710
parsetree = 0x5615da515710
isTopLevel = true
isAtomicContext = true
pstate = 0x5615da56e168
readonly_flags = 5
__func__ = "standard_ProcessUtility"
#25 0x00005615d86ee768 in ProcessUtility (pstmt=0x5615da515a30,
queryString=0x5615da514d08 "FETCH FORWARD 100000 FROM \"sqlutilcursor\"", readOnlyTree=false,
context=PROCESS_UTILITY_TOPLEVEL, params=0x0, queryEnv=0x0, dest=0x5615da56e0d8, qc=0x7ffffb4cac30) at utility.c:530
No locals.
#26 0x00005615d86ed654 in PortalRunUtility (portal=0x5615da616228, pstmt=0x5615da515a30, isTopLevel=true,
setHoldSnapshot=true, dest=0x5615da56e0d8, qc=0x7ffffb4cac30) at pquery.c:1156
No locals.
#27 0x00005615d86ed3b7 in FillPortalStore (portal=0x5615da616228, isTopLevel=true) at pquery.c:1029
treceiver = 0x5615da56e0d8
qc = {
commandTag = CMDTAG_UNKNOWN,
nprocessed = 0
}
__func__ = "FillPortalStore"
#28 0x00005615d86ecd74 in PortalRun (portal=0x5615da616228, count=9223372036854775807, isTopLevel=true, run_once=true,
dest=0x5615da515b10, altdest=0x5615da515b10, qc=0x7ffffb4cae00) at pquery.c:761
_save_exception_stack = 0x7ffffb4caf20
_save_context_stack = 0x0
_local_sigjmp_buf = {{
__jmpbuf = {0, -1720014361414672517, 94651820578848, 140737409496096, 0, 0, -1720014361467101317,
-4902996908842098821},
__mask_was_saved = 0,
__saved_mask = {
__val = {20843699160, 94651857066752, 128, 94651857062896, 94651857428368, 94651857065712, 4336,
94651857428632, 94651857063120, 140737409494400, 94651827307209, 7, 112, 94651857066880, 112, 4216106384}
}
}}
_do_rethrow = false
result = false
nprocessed = 94651857066768
saveTopTransactionResourceOwner = 0x5615da5774a8
saveTopTransactionContext = 0x5615da56af60
saveActivePortal = 0x0
saveResourceOwner = 0x5615da5774a8
savePortalContext = 0x0
saveMemoryContext = 0x5615da56af60
__func__ = "PortalRun"
#29 0x00005615d86e6808 in exec_simple_query (query_string=0x5615da514d08 "FETCH FORWARD 100000 FROM \"sqlutilcursor\"")
at postgres.c:1254
snapshot_set = false
per_parsetree_context = 0x0
plantree_list = 0x5615da515ac0
parsetree = 0x5615da515740
commandTag = CMDTAG_FETCH
qc = {
commandTag = CMDTAG_UNKNOWN,
nprocessed = 0
}
querytree_list = 0x5615da5159e0
portal = 0x5615da616228
receiver = 0x5615da515b10
format = 0
parsetree_item__state = {
l = 0x5615da515770,
i = 0
}
dest = DestRemote
oldcontext = 0x5615da56af60
parsetree_list = 0x5615da515770
parsetree_item = 0x5615da515788
save_log_statement_stats = false
was_logged = false
use_implicit_block = false
msec_str = "\bMQ\332\025V\000\000)\000\000\000\006\000\000\000p\256L\373\377\177\000\000\333\070~\000)\000\000"
__func__ = "exec_simple_query"
#30 0x00005615d86eb14c in PostgresMain (dbname=0x5615da5703e8 "wsdb", username=0x5615da510588 "koposov") at postgres.c:4691
query_string = 0x5615da514d08 "FETCH FORWARD 100000 FROM \"sqlutilcursor\""
firstchar = 81
input_message = {
data = 0x5615da514d08 "FETCH FORWARD 100000 FROM \"sqlutilcursor\"",
len = 42,
maxlen = 1024,
cursor = 42
}
local_sigjmp_buf = {{
__jmpbuf = {0, -1720014361601319045, 94651820578848, 140737409496096, 0, 0, -1720014361379020933,
-4902996912041042053},
__mask_was_saved = 1,
__saved_mask = {
__val = {4194304, 18446744073709551536, 0, 0, 139786617312675, 0, 0, 140737409495072, 94651820578848,
140737409496096, 94651827270967, 129792, 94651857036688, 8248, 42949672960, 94651857748440}
}
}}
send_ready_for_query = false
idle_in_transaction_timeout_enabled = false
idle_session_timeout_enabled = false
__func__ = "PostgresMain"
#31 0x00005615d8638aa1 in BackendRun (port=0x5615da56a760) at postmaster.c:4515
No locals.
#32 0x00005615d86383ed in BackendStartup (port=0x5615da56a760) at postmaster.c:4243
bn = 0x5615da567980
pid = 0
__func__ = "BackendStartup"
#33 0x00005615d8634a2f in ServerLoop () at postmaster.c:1811
port = 0x5615da56a760
i = 0
rmask = {
fds_bits = {64, 0 <repeats 15 times>}
}
selres = 1
now = 1746287454
readmask = {
fds_bits = {4032, 0 <repeats 15 times>}
}
nSockets = 12
last_lockfile_recheck_time = 1746287454
last_touch_time = 1746287454
__func__ = "ServerLoop"
#34 0x00005615d8634288 in PostmasterMain (argc=3, argv=0x5615da50e4f0) at postmaster.c:1483
opt = -1
status = 0
userDoption = 0x5615da531d40 "/mnt/bigdata/pgdata15"
listen_addr_saved = true
i = 64
output_config_variable = 0x0
__func__ = "PostmasterMain"
#35 0x00005615d8544c28 in main (argc=3, argv=0x5615da50e4f0) at main.c:204
do_check_root = true
quit
Please answer y or n.
Detaching from program: /opt/pgsql15/bin/postgres, process 35816
[Inferior 1 (process 35816) detached]

S
The University of Edinburgh is a charitable body, registered in Scotland, with registration number SC005336. Is e buidheann carthannais a th’ ann an Oilthigh Dhùn Èideann, clàraichte an Alba, àireamh clàraidh SC005336.