Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vSvw8-009XXl-24 for pgsql-bugs@arkaria.postgresql.org; Tue, 09 Dec 2025 11:31:20 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vSvw6-004Ijl-01 for pgsql-bugs@arkaria.postgresql.org; Tue, 09 Dec 2025 11:31:18 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vSvw5-004Ijd-2H for pgsql-bugs@lists.postgresql.org; Tue, 09 Dec 2025 11:31:18 +0000 Received: from mail-pl1-x62e.google.com ([2607:f8b0:4864:20::62e]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1vSvw1-0041ub-0W for pgsql-bugs@lists.postgresql.org; Tue, 09 Dec 2025 11:31:17 +0000 Received: by mail-pl1-x62e.google.com with SMTP id d9443c01a7336-29586626fbeso63514535ad.0 for ; Tue, 09 Dec 2025 03:31:12 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1765279871; x=1765884671; darn=lists.postgresql.org; h=to:references:message-id:content-transfer-encoding:cc:date :in-reply-to:from:subject:mime-version:from:to:cc:subject:date :message-id:reply-to; bh=zfnLOjqqx6OO/FhvnU6NzI9Du2oYWEJjHVOv/jO5wB4=; b=MLfWjtQKUnYanu1WAsuBInRFhuRjONKH4SLu3ccYNhD4qaX/TUG7Ai0DpNigfBbCPs taXY9n1E6FQAOgVcXpxxO9toNODn6Ca0wUOIJtiYM4ylVT6d/wUR/lllM5KBsmCb0EYz 52Iw86oD9ShimK6N8DmFWSST/y+te6DwKKn20tMB23Kwr/S4DQO2+7WeUedPG3pIbyNM TawX6IkX0khi8imvadFEUPD04ULPDxsDwC9feqtRdcnHpX8dZU5H4VBFoXKTUh4B7V42 QcUEmsFQDyi7hBTSjwb0SGD7ofyUadD5puJiAsSCE93HSsL1y3kLjrsqmBs7XljNBkhq nKew== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1765279871; x=1765884671; h=to:references:message-id:content-transfer-encoding:cc:date :in-reply-to:from:subject:mime-version:x-gm-gg:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=zfnLOjqqx6OO/FhvnU6NzI9Du2oYWEJjHVOv/jO5wB4=; b=QRsUG6dNK1Pv9j5M7MKzRBAy4ORch53+hvu4OArOxvC1udOoM1rXOGMnmKWVouCnLN bV7sJGGhAccb3iHK0BrbX5NcfwlFdTFQj+WoIYi66zkojxRgc/HalOtnNp3NPaWx+4SD TtKFqLpT72QTwMcuAOUomySpyqkJconf1g1xhWMGwHtXs5qbAtMtYG1DtkSryU5DSH5I +PBdmwUI/0U6F1BusLnHJp4PGbCIyl6m1H2pznfhozz0c6g5LXpU/lJHPD6piF7N1l0k sp+Ml4cfzQM1k0YMF/681U5fy4MOB5vOl0v+cN5eu35r7Z5JGE9Rm2upE9rJ5W2duKJ4 nBnw== X-Gm-Message-State: AOJu0Yxj0BqHg/UnVMEF1gmJeHZbtXeiUTZggOGiSIqAowNokH49I8O6 /OMRZSsaceMIbZ0OXS06gWFTAAp1MxcKb2tIZ/VTSvyfoVicsi3QkujZ X-Gm-Gg: AY/fxX7VWEQ5+8Hs+XxDwBUtrxDTgCyFQMtyrgbUw0sri3+8YBXO4q+jyfxoQjTXUML SwqBUM6V23YOXoMM8d5y7yJAmiP6BciJwYh9KZD/+NY5V2NOAe0+JgiFtjyfyJ+ecf8OToEAEye sXAKti5DGq/YVuiBTepwD/h9prY2Vj1PpOSBPK6/Aj+9VPCznmdNMhmJk6ZJu9T6dhu/1wCV/Fh SpAt2zriQZhTNPEpCns7BeGoRNg/ra0Y/7fM2l9eoTuwtwUQ6jHnOT2qOKjLIXobptM60rKHrPO jBgQj5WA2LHATxSgRw/qIG3c3jyMkU8eCGEluCY2QPsXqMBW8g3KrZVAXXVPxw28WEMi4PpprbF OJ5vm9NGurJ9+7rnAL8zMCX4jR3mM54Q/0EeqD7z4/7UXF4i4v0uEGg+LSL1/f233yIG1d9sHQY Vhie2hu2T85Qv6mFBp5HQZ5Fm0NDx+fTiX2x0= X-Google-Smtp-Source: AGHT+IGKvnfa5S/VgOinG5PLAWQow8DaSqrzmiCged1d1YqN2t8WDcpB/5vhx3Y7RLr4In6FbrRmsg== X-Received: by 2002:a17:903:1247:b0:295:5b68:9967 with SMTP id d9443c01a7336-29df6116e54mr123580745ad.49.1765279870269; Tue, 09 Dec 2025 03:31:10 -0800 (PST) Received: from smtpclient.apple ([203.90.233.89]) by smtp.gmail.com with ESMTPSA id d9443c01a7336-29dae49c8b6sm155304405ad.13.2025.12.09.03.31.08 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Tue, 09 Dec 2025 03:31:09 -0800 (PST) Content-Type: text/plain; charset=utf-8 Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3864.200.81.1.6\)) Subject: Re: BUG #19345: MemoryContextSizeFailure after upgrade 14.11 to 17.7 in stored procedure From: weijie JL In-Reply-To: <2390310.1765055088@sss.pgh.pa.us> Date: Tue, 9 Dec 2025 19:30:57 +0800 Cc: pgsql-bugs@lists.postgresql.org Content-Transfer-Encoding: quoted-printable Message-Id: <810E2091-07B7-440B-92CE-44860FB5696D@gmail.com> References: <19345-5b02bba7a48be396@postgresql.org> <2390310.1765055088@sss.pgh.pa.us> To: Tom Lane X-Mailer: Apple Mail (2.3864.200.81.1.6) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk I found this information in the log=EF=BC=9A 2025-12-09 09:01:43.384 CST 10.21.18.87(44346) pgdb_md md [269407]LOG: = 00000: temporary file: path "base/pgsql_tmp/pgsql_tmp269407.25892120", = size 1073741824 2025-12-09 09:01:43.384 CST 10.21.18.87(44346) pgdb_md md = [269407]STATEMENT: CALL PR_MD() 2025-12-09 09:01:43.451 CST 10.21.18.87(44346) pgdb_md md [269407]LOG: = 00000: temporary file: path "base/pgsql_tmp/pgsql_tmp269407.25892121", = size 1073741824 2025-12-09 09:01:43.451 CST 10.21.18.87(44346) pgdb_md md = [269407]STATEMENT: CALL PR_MD() 2025-12-09 09:01:43.518 CST 10.21.18.87(44346) pgdb_md md [269407]LOG: = 00000: temporary file: path "base/pgsql_tmp/pgsql_tmp269407.25892122", = size 1073741824 2025-12-09 09:01:43.518 CST 10.21.18.87(44346) pgdb_md md = [269407]STATEMENT: CALL PR_MD() 2025-12-09 09:01:43.604 CST 10.21.18.87(44346) pgdb_md md [269407]LOG: = 00000: temporary file: path "base/pgsql_tmp/pgsql_tmp269407.25892123", = size 1073741824 2025-12-09 09:01:43.604 CST 10.21.18.87(44346) pgdb_md md = [269407]STATEMENT: CALL PR_MD() 2025-12-09 09:01:43.672 CST 10.21.18.87(44346) pgdb_md md [269407]LOG: = 00000: temporary file: path "base/pgsql_tmp/pgsql_tmp269407.25892124", = size 1073741824 2025-12-09 09:01:43.672 CST 10.21.18.87(44346) pgdb_md md = [269407]STATEMENT: CALL PR_MD() 2025-12-09 09:01:43.737 CST 10.21.18.87(44346) pgdb_md md [269407]LOG: = 00000: temporary file: path "base/pgsql_tmp/pgsql_tmp269407.25892125", = size 1073741824 2025-12-09 09:01:43.737 CST 10.21.18.87(44346) pgdb_md md = [269407]STATEMENT: CALL PR_MD() 2025-12-09 09:01:43.806 CST 10.21.18.87(44346) pgdb_md md [269407]LOG: = 00000: temporary file: path "base/pgsql_tmp/pgsql_tmp269407.25892126", = size 1073741824 2025-12-09 09:01:43.806 CST 10.21.18.87(44346) pgdb_md md = [269407]STATEMENT: CALL PR_MD() 2025-12-09 09:01:43.874 CST 10.21.18.87(44346) pgdb_md md [269407]LOG: = 00000: temporary file: path "base/pgsql_tmp/pgsql_tmp269407.25892127", = size 1073741824 2025-12-09 09:01:43.874 CST 10.21.18.87(44346) pgdb_md md = [269407]STATEMENT: CALL PR_MD() 2025-12-09 09:01:43.940 CST 10.21.18.87(44346) pgdb_md md [269407]LOG: = 00000: temporary file: path "base/pgsql_tmp/pgsql_tmp269407.25892128", = size 1073741824 2025-12-09 09:01:43.940 CST 10.21.18.87(44346) pgdb_md md = [269407]STATEMENT: CALL PR_MD() 2025-12-09 09:01:44.007 CST 10.21.18.87(44346) pgdb_md md [269407]LOG: = 00000: temporary file: path "base/pgsql_tmp/pgsql_tmp269407.25892129", = size 1073741824 2025-12-09 09:01:44.007 CST 10.21.18.87(44346) pgdb_md md = [269407]STATEMENT: CALL PR_MD() 2025-12-09 09:01:44.073 CST 10.21.18.87(44346) pgdb_md md [269407]LOG: = 00000: temporary file: path "base/pgsql_tmp/pgsql_tmp269407.25892130", = size 1073741824 2025-12-09 09:01:44.073 CST 10.21.18.87(44346) pgdb_md md = [269407]STATEMENT: CALL PR_MD() 2025-12-09 09:01:44.140 CST 10.21.18.87(44346) pgdb_md md [269407]LOG: = 00000: temporary file: path "base/pgsql_tmp/pgsql_tmp269407.25892131", = size 1073741824 2025-12-09 09:01:44.140 CST 10.21.18.87(44346) pgdb_md md = [269407]STATEMENT: CALL PR_MD() 2025-12-09 09:01:44.207 CST 10.21.18.87(44346) pgdb_md md [269407]LOG: = 00000: temporary file: path "base/pgsql_tmp/pgsql_tmp269407.25892132", = size 1073741824 2025-12-09 09:01:44.207 CST 10.21.18.87(44346) pgdb_md md = [269407]STATEMENT: CALL PR_MD() 2025-12-09 09:01:44.272 CST 10.21.18.87(44346) pgdb_md md [269407]LOG: = 00000: temporary file: path "base/pgsql_tmp/pgsql_tmp269407.25892133", = size 1073741824 2025-12-09 09:01:44.272 CST 10.21.18.87(44346) pgdb_md md = [269407]STATEMENT: CALL PR_MD() 2025-12-09 09:01:44.340 CST 10.21.18.87(44346) pgdb_md md [269407]LOG: = 00000: temporary file: path "base/pgsql_tmp/pgsql_tmp269407.25892134", = size 1073741824 2025-12-09 09:01:44.340 CST 10.21.18.87(44346) pgdb_md md = [269407]STATEMENT: CALL PR_MD() 2025-12-09 09:01:44.406 CST 10.21.18.87(44346) pgdb_md md [269407]LOG: = 00000: temporary file: path "base/pgsql_tmp/pgsql_tmp269407.25892135", = size 1069645824 2025-12-09 09:01:44.406 CST 10.21.18.87(44346) pgdb_md md = [269407]STATEMENT: CALL PR_MD() 2025-12-09 09:01:46.030 CST 10.10.17.192(44780) pgdb_md md [467056]LOG: = 00000: temporary file: path "base/pgsql_tmp/pgsql_tmp467056.0", size = 356745216 2025-12-09 09:12:24.606 CST 10.21.18.87(44346) pgdb_md md = [269407]STATEMENT: CALL PR_MD() 2025-12-09 09:12:24.674 CST 10.21.18.87(44346) pgdb_md md [269407]LOG: = 00000: temporary file: path "base/pgsql_tmp/pgsql_tmp269407.25892137", = size 1073741824 2025-12-09 09:12:24.674 CST 10.21.18.87(44346) pgdb_md md = [269407]STATEMENT: CALL PR_MD() 2025-12-09 09:12:24.747 CST 10.21.18.87(44346) pgdb_md md [269407]LOG: = 00000: temporary file: path "base/pgsql_tmp/pgsql_tmp269407.25892138", = size 1073741824 2025-12-09 09:12:24.747 CST 10.21.18.87(44346) pgdb_md md = [269407]STATEMENT: CALL PR_MD() 2025-12-09 09:12:24.814 CST 10.21.18.87(44346) pgdb_md md [269407]LOG: = 00000: temporary file: path "base/pgsql_tmp/pgsql_tmp269407.25892139", = size 1073741824 2025-12-09 09:12:24.814 CST 10.21.18.87(44346) pgdb_md md = [269407]STATEMENT: CALL PR_MD() 2025-12-09 09:12:27.941 CST 10.10.17.192(46184) pgdb_md md [479218]LOG: = 00000: temporary file: path "base/pgsql_tmp/pgsql_tmp479218.4", size = 356745216 2025-12-09 09:41:05.279 CST 10.21.18.87(44346) pgdb_md md = [269407]STATEMENT: CALL PR_MD() 2025-12-09 09:41:24.385 CST 10.21.18.87(44346) pgdb_md md = [269407]STATEMENT: CALL PR_MD() 2025-12-09 09:41:32.003 CST 10.10.17.192(51364) pgdb_md md [518956]LOG: = 00000: temporary file: path "base/pgsql_tmp/pgsql_tmp518956.10", size = 356761600 2025-12-09 10:23:18.997 CST 10.21.18.87(44346) pgdb_md md = [269407]STATEMENT: CALL PR_MD() 2025-12-09 10:23:19.043 CST 10.21.18.87(44346) pgdb_md md [269407]LOG: = 00000: temporary file: path "base/pgsql_tmp/pgsql_tmp269407.25892166", = size 392716288 2025-12-09 10:23:19.043 CST 10.21.18.87(44346) pgdb_md md = [269407]STATEMENT: CALL PR_MD() 2025-12-09 10:23:19.089 CST 10.21.18.87(44346) pgdb_md md [269407]LOG: = 00000: temporary file: path "base/pgsql_tmp/pgsql_tmp269407.25892146", = size 571425269 2025-12-09 10:23:19.089 CST 10.21.18.87(44346) pgdb_md md = [269407]STATEMENT: CALL PR_MD() 2025-12-09 10:23:19.159 CST 10.21.18.87(44346) pgdb_md md [269407]LOG: = 00000: temporary file: path "base/pgsql_tmp/pgsql_tmp269407.25892149", = size 1073741824 2025-12-09 10:23:19.159 CST 10.21.18.87(44346) pgdb_md md = [269407]STATEMENT: CALL PR_MD() 2025-12-09 10:23:19.222 CST 10.21.18.87(44346) pgdb_md md [269407]LOG: = 00000: temporary file: path "base/pgsql_tmp/pgsql_tmp269407.25892148", = size 965312512 2025-12-09 10:23:19.222 CST 10.21.18.87(44346) pgdb_md md = [269407]STATEMENT: CALL PR_MD() 2025-12-09 10:23:19.308 CST 10.21.18.87(44346) pgdb_md md [269407]LOG: = 00000: temporary file: path "base/pgsql_tmp/pgsql_tmp269407.25892145", = size 1073741824 2025-12-09 10:23:19.308 CST 10.21.18.87(44346) pgdb_md md = [269407]STATEMENT: CALL PR_MD() 2025-12-09 10:23:19.350 CST 10.21.18.87(44346) pgdb_md md [269407]LOG: = 00000: temporary file: path "base/pgsql_tmp/pgsql_tmp269407.25892150", = size 369754112 2025-12-09 10:23:19.350 CST 10.21.18.87(44346) pgdb_md md = [269407]STATEMENT: CALL PR_MD() 2025-12-09 10:23:21.524 CST 10.10.17.192(58298) pgdb_md md [571449]LOG: = 00000: temporary file: path "base/pgsql_tmp/pgsql_tmp571449.9", size = 356769792 2025-12-09 10:25:02.481 CST 10.21.18.87(44346) pgdb_md md = [269407]STATEMENT: CALL PR_MD() 2025-12-09 10:47:41.771 CST 10.21.18.87(44346) pgdb_md md [269407]LOG: = 00000: duration: 35252734.115 ms execute : CALL PR_MD() 2025-12-09 10:47:41.771 CST 10.21.18.87(44346) pgdb_md md = [269407]LOCATION: exec_execute_message, postgres.c:2348 > 2025=E5=B9=B412=E6=9C=887=E6=97=A5 05:04=EF=BC=8CTom Lane = =E5=86=99=E9=81=93=EF=BC=9A >=20 > PG Bug reporting form writes: >> After upgrading from PostgreSQL 14.11 to PostgreSQL 17.7 on our = production >> environment (RockyLinux8, RPM package), we encountered a crash when >> executing a specific stored procedure. >=20 >> This procedure ran successfully in 14.11 (approximately 8 hours = execution >> time), but on 17.7 it runs for about 6 hours before failing with: >> LOCATION: MemoryContextSizeFailure, mcxt.c:1169 >> [3717604]BACKTRACE: >> postgres: postgres003: md pgdb_md 10.21.18.87(53748) CALL() >> [0x50d5e0] >> postgres: postgres003: md pgdb_md 10.21.18.87(53748) CALL() >> [0x9c96dc] >> postgres: postgres003: md pgdb_md 10.21.18.87(53748) >> CALL(MemoryContextAllocZero+0x14) [0x9cfb54] >> postgres: postgres003: md pgdb_md 10.21.18.87(53748) >> CALL(ResourceOwnerEnlarge+0x9f) [0x9d218f] >> postgres: postgres003: md pgdb_md 10.21.18.87(53748) >> CALL(OpenTemporaryFile+0x64) [0x83dfc4] >> postgres: postgres003: md pgdb_md 10.21.18.87(53748) >> CALL(BufFileCreateTemp+0x18) [0x83a288] >> postgres: postgres003: md pgdb_md 10.21.18.87(53748) >> CALL(ExecHashJoinSaveTuple+0x68) [0x6e1ce8] >=20 > Hmm. Apparently, your hash join tried to use so many temporary files > that it needed a more-than-1GB array just to track them all. One = could > guess that it had been in swap hell for some hours before reaching > this point, because that'd imply about a terabyte worth of I/O > buffers, never mind the actual hashtable data. >=20 > Too-many-batches (resulting in too-many-temp-files) is a known failure > mechanism for our hash join code. We've tried to improve that in v18 > (cf commits a1b4f289b, aa151022e), but I don't think there's any > appetite for back-patching that work into older branches. >=20 >> Observations / Workarounds Tested: >> SET enable_hashjoin =3D off; =E2=86=92 procedure runs successfully. >> SET hash_mem_multiplier =3D 1; =E2=86=92 procedure runs successfully. >=20 > I'm curious whether the change of hash_mem_multiplier causes it > to not use a hash join, or that is just successful at limiting > the hash table growth to a point short of failure. >=20 > regards, tom lane