BUG #18354: Aborted transaction aborted during cleanup when temp_file_limit exceeded

The following bug has been logged on the website:

Bug reference:      18354
Logged by:          Alexander Lakhin
Email address:      exclusion@gmail.com
PostgreSQL version: 16.2
Operating system:   Ubuntu 22.04
Description:

The following script:
SET temp_file_limit = 100;
CREATE TABLE t (a text);
INSERT INTO t VALUES ('test');
SELECT 'INSERT INTO t SELECT * FROM t RETURNING *' FROM generate_series(1,
18)
\gexec

triggers two errors, a warning, and an assertion failure:
ERROR:  temporary file size exceeds temp_file_limit (100kB)
WARNING:  AbortTransaction while in ABORT state
ERROR:  temporary file size exceeds temp_file_limit (100kB)
server closed the connection unexpectedly

The backtrace for the first error is:
2024-02-21 11:40:06.947 UTC|law|regression|65d5e116.13cfcb|LOG:  statement:
INSERT INTO t SELECT * FROM t RETURNING *
2024-02-21 11:40:06.999 UTC|law|regression|65d5e116.13cfcb|ERROR:  temporary
file size exceeds temp_file_limit (100kB)
2024-02-21 11:40:06.999 UTC|law|regression|65d5e116.13cfcb|BACKTRACE:  
FileWrite at fd.c:2183:5
BufFileDumpBuffer at buffile.c:537:18
BufFileWrite at buffile.c:698:28
writetup_heap at tuplestore.c:1513:5
dumptuples at tuplestore.c:1210:35
tuplestore_puttuple_common at tuplestore.c:833:4
MemoryContextSwitchTo at palloc.h:142:23
 (inlined by) tuplestore_puttupleslot at tuplestore.c:722:2
tstoreReceiveSlot_notoast at tstoreReceiver.c:129:1
ExecutePlan at execMain.c:1701:7
standard_ExecutorRun at execMain.c:365:3
ExecutorRun at execMain.c:310:1
ProcessQuery at pquery.c:165:5
PortalRunMulti at pquery.c:1293:7
FillPortalStore at pquery.c:1042:8
PortalRun at pquery.c:763:6
exec_simple_query at postgres.c:1282:3
PostgresMain at postgres.c:4641:27
report_fork_failure_to_client at postmaster.c:4242:1
BackendStartup at postmaster.c:4199:22
ServerLoop at postmaster.c:1788:6
main at main.c:185:3

For the second error:
2024-02-21 11:40:07.001 UTC|law|regression|65d5e116.13cfcb|ERROR:  temporary
file size exceeds temp_file_limit (100kB)
2024-02-21 11:40:07.001 UTC|law|regression|65d5e116.13cfcb|BACKTRACE:  
FileWrite at fd.c:2183:5
BufFileDumpBuffer at buffile.c:537:18
BufFileFlush at buffile.c:723:3
BufFileClose at buffile.c:419:9
tuplestore_end at tuplestore.c:459:5
MemoryContextSwitchTo at palloc.h:142:23
 (inlined by) PortalDrop at portalmem.c:587:3
AtCleanup_Portals at portalmem.c:907:3
CleanupTransaction at xact.c:2903:2
AbortCurrentTransaction at xact.c:3341:18
PostgresMain at postgres.c:4356:6
report_fork_failure_to_client at postmaster.c:4242:1
BackendStartup at postmaster.c:4199:22
ServerLoop at postmaster.c:1788:6
main at main.c:185:3

For the failed assertion:
2024-02-21 11:40:07.001 UTC|law|regression|65d5e116.13cfcb|WARNING:
AbortTransaction while in ABORT state
TRAP: failed Assert("TransactionIdIsValid(proc->xid)"), File: "procarray.c",
Line: 680, PID: 1298379
ExceptionalCondition at assert.c:52:13
ProcArrayEndTransactionInternal at procarray.c:742:2
 (inlined by) ProcArrayEndTransaction at procarray.c:689:4
AbortTransaction at xact.c:2841:5
AbortCurrentTransaction at xact.c:3340:4
PostgresMain at postgres.c:4356:6
report_fork_failure_to_client at postmaster.c:4242:1
BackendStartup at postmaster.c:4199:22
ServerLoop at postmaster.c:1788:6
BackgroundWorkerInitializeConnection at postmaster.c:5604:1
main at main.c:185:3

Reproduced on REL_12_STABLE .. master.