Re: BUG #18909: Query creates millions of temporary files and stalls - Mailing list pgsql-bugs

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@\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
buidheanncarthannais a th’ ann an Oilthigh Dhùn Èideann, clàraichte an Alba, àireamh clàraidh SC005336.