BUG #19345: MemoryContextSizeFailure after upgrade 14.11 to 17.7 in stored procedure

The following bug has been logged on the website:

Bug reference: 19345
Logged by: weijie JL
Email address: weijie1006jl(at)gmail(dot)com
PostgreSQL version: 17.7
Operating system: RockyLinux8
Description:

Hello PostgreSQL Team,

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.

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
STATEMENT: CALL PR_MD()
The stack trace indicates calls related to hash join and temporary file
creation during query execution inside PL/pgSQL function pr_md_item_org()
and pr_md().

error log:

left join o_scm_dictionary_item Di ON Di.Dictionary_Type_Code =
'INV_0200304' AND Di.Is_Deleted = '0' AND Di.Item_Value = Lc.Option_Value
AND Msib.Lot_Control_Code = 2"
PL/pgSQL function pr_md_item_org() line 20 at SQL statement
SQL statement "CALL PR_MD_ITEM_ORG()"
PL/pgSQL function pr_md() line 72 at CALL
2025-11-28 06:02:22.121 CST 10.21.18.87(53748) pgdb_md md [3717604]LOCATION:
MemoryContextSizeFailure, mcxt.c:1169
2025-11-28 06:02:22.121 CST 10.21.18.87(53748) pgdb_md md
[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]
postgres: postgres003: md pgdb_md 10.21.18.87(53748) CALL()
[0x6e251f]
postgres: postgres003: md pgdb_md 10.21.18.87(53748) CALL()
[0x6c910c]
postgres: postgres003: md pgdb_md 10.21.18.87(53748) CALL()
[0x6f0d57]
postgres: postgres003: md pgdb_md 10.21.18.87(53748) CALL()
[0x6c910c]
postgres: postgres003: md pgdb_md 10.21.18.87(53748) CALL()
[0x6f0d57]
postgres: postgres003: md pgdb_md 10.21.18.87(53748) CALL()
[0x6c910c]
postgres: postgres003: md pgdb_md 10.21.18.87(53748) CALL()
[0x6e2487]
postgres: postgres003: md pgdb_md 10.21.18.87(53748) CALL()
[0x6c910c]
postgres: postgres003: md pgdb_md 10.21.18.87(53748) CALL()
[0x6eefa6]
postgres: postgres003: md pgdb_md 10.21.18.87(53748) CALL()
[0x6c910c]
postgres: postgres003: md pgdb_md 10.21.18.87(53748)
CALL(standard_ExecutorRun+0x17c) [0x6c27ec]
/usr/pgsql-17/lib/pg_stat_statements.so(+0x3ec5) [0x7f4dac51dec5]
/usr/pgsql-17/lib/auto_explain.so(+0x1928) [0x7f4daa9e8928]
postgres: postgres003: md pgdb_md 10.21.18.87(53748) CALL()
[0x6feb6e]
postgres: postgres003: md pgdb_md 10.21.18.87(53748)
CALL(SPI_execute_plan_with_paramlist+0xa6) [0x6ff326]
/usr/pgsql-17/lib/plpgsql.so(+0x12267) [0x7f1f6c910267]
/usr/pgsql-17/lib/plpgsql.so(+0x144ab) [0x7f1f6c9124ab]
/usr/pgsql-17/lib/plpgsql.so(+0x16583) [0x7f1f6c914583]
/usr/pgsql-17/lib/plpgsql.so(+0x1665b) [0x7f1f6c91465b]
/usr/pgsql-17/lib/plpgsql.so(+0x16e45) [0x7f1f6c914e45]
/usr/pgsql-17/lib/plpgsql.so(plpgsql_call_handler+0x1f3)
[0x7f1f6c91e473]
postgres: postgres003: md pgdb_md 10.21.18.87(53748)
CALL(ExecuteCallStmt+0x222) [0x669202]
postgres: postgres003: md pgdb_md 10.21.18.87(53748)
CALL(standard_ProcessUtility+0x715) [0x86ff45]
/usr/pgsql-17/lib/pg_stat_statements.so(+0x5dbd) [0x7f4dac51fdbd]
postgres: postgres003: md pgdb_md 10.21.18.87(53748) CALL()
[0x6fe7fe]
postgres: postgres003: md pgdb_md 10.21.18.87(53748)
CALL(SPI_execute_plan_extended+0x5d) [0x6ff25d]
/usr/pgsql-17/lib/plpgsql.so(+0x13d10) [0x7f1f6c911d10]
/usr/pgsql-17/lib/plpgsql.so(+0x16583) [0x7f1f6c914583]
/usr/pgsql-17/lib/plpgsql.so(+0x1665b) [0x7f1f6c91465b]
/usr/pgsql-17/lib/plpgsql.so(+0x16e45) [0x7f1f6c914e45]
/usr/pgsql-17/lib/plpgsql.so(plpgsql_call_handler+0x1f3)
[0x7f1f6c91e473]
postgres: postgres003: md pgdb_md 10.21.18.87(53748)
CALL(ExecuteCallStmt+0x222) [0x669202]
postgres: postgres003: md pgdb_md 10.21.18.87(53748)
CALL(standard_ProcessUtility+0x715) [0x86ff45]
/usr/pgsql-17/lib/pg_stat_statements.so(+0x5dbd) [0x7f4dac51fdbd]
postgres: postgres003: md pgdb_md 10.21.18.87(53748) CALL()
[0x86e4cf]
postgres: postgres003: md pgdb_md 10.21.18.87(53748) CALL()
[0x86e603]
postgres: postgres003: md pgdb_md 10.21.18.87(53748)
CALL(PortalRun+0x27b) [0x86eb5b]
postgres: postgres003: md pgdb_md 10.21.18.87(53748)
CALL(PostgresMain+0x16ec) [0x86c7bc]
postgres: postgres003: md pgdb_md 10.21.18.87(53748) CALL()
[0x8672b5]
postgres: postgres003: md pgdb_md 10.21.18.87(53748)
CALL(postmaster_child_launch+0x8f) [0x7d850f]
postgres: postgres003: md pgdb_md 10.21.18.87(53748) CALL()
[0x7dbe5c]
postgres: postgres003: md pgdb_md 10.21.18.87(53748)
CALL(PostmasterMain+0xd18) [0x7dda78]
postgres: postgres003: md pgdb_md 10.21.18.87(53748)
CALL(main+0x223) [0x50f843]
/lib64/libc.so.6(__libc_start_main+0xe5) [0x7f4dbe4fe7e5]
postgres: postgres003: md pgdb_md 10.21.18.87(53748)
CALL(_start+0x2e) [0x50fe1e]
2025-11-28 06:02:22.121 CST 10.21.18.87(53748) pgdb_md md
[3717604]STATEMENT: CALL PR_MD()

Observations / Workarounds Tested:
In 14.11: procedure runs successfully (~8h). In 17.7: fails after ~6h.
SET enable_hashjoin = off; → procedure runs successfully.
SET hash_mem_multiplier = 1; → procedure runs successfully.

Best regards,
Jeslie