BUG #19441: Backend waits for serializable snapshot indefinitely on removing temp relations

The following bug has been logged on the website:

Bug reference: 19441
Logged by: Alexander Lakhin
Email address: exclusion(at)gmail(dot)com
PostgreSQL version: 18.3
Operating system: Ubuntu 24.04
Description:

The following script:
echo "
CREATE TEMPORARY TABLE tt (i int);
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE READ
ONLY DEFERRABLE;
SELECT pg_sleep(2);
" | psql &
sleep 1

echo "
CREATE TABLE t (i int);
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
INSERT INTO t VALUES (1);
PREPARE TRANSACTION 'pt';
" | psql

wait
psql -c "SELECT pid, pg_terminate_backend(pg_stat_activity.pid) FROM
pg_stat_activity WHERE backend_type='client backend' AND query NOT LIKE
'%pg_stat_activity%'"
sleep 5

psql -c "SELECT * FROM pg_stat_activity WHERE backend_type='client backend'
AND query NOT LIKE '%pg_stat_activity%'"

(with max_prepared_transactions = 1 in postgresql.conf) instantiates a
backend hanging on exit, waiting for a snapshot:

pid | pg_terminate_backend
---------+----------------------
2143677 | t

gdb -p 2143677

(gdb) bt
#0 0x000077c27fb2a007 in epoll_wait (epfd=5, events=0x5d3d166cd868,
maxevents=1, timeout=timeout(at)entry=-1)
at ../sysdeps/unix/sysv/linux/epoll_wait.c:30
#1 0x00005d3ce11b11d2 in WaitEventSetWaitBlock
(set=set(at)entry=0x5d3d166cd800, cur_timeout=cur_timeout(at)entry=-1,
occurred_events=occurred_events(at)entry=0x7ffeb661f160,
nevents=nevents(at)entry=1) at waiteventset.c:1193
#2 0x00005d3ce11b1bd4 in WaitEventSetWait (set=0x5d3d166cd800,
timeout=timeout(at)entry=-1,
occurred_events=occurred_events(at)entry=0x7ffeb661f160,
nevents=nevents(at)entry=1,
wait_event_info=wait_event_info(at)entry=134217779) at waiteventset.c:1141
#3 0x00005d3ce11a4b78 in WaitLatch (latch=<optimized out>,
wakeEvents=wakeEvents(at)entry=33, timeout=timeout(at)entry=0,
wait_event_info=wait_event_info(at)entry=134217779) at latch.c:196
#4 0x00005d3ce11c8188 in ProcWaitForSignal
(wait_event_info=wait_event_info(at)entry=134217779) at proc.c:2005
#5 0x00005d3ce11c42bf in GetSafeSnapshot
(origSnapshot=origSnapshot(at)entry=0x5d3ce17753e0 <CurrentSnapshotData>)
at predicate.c:1600
#6 0x00005d3ce11c4436 in GetSerializableTransactionSnapshot
(snapshot=snapshot(at)entry=0x5d3ce17753e0 <CurrentSnapshotData>)
at predicate.c:1716
#7 0x00005d3ce137077d in GetTransactionSnapshot () at snapmgr.c:320
#8 0x00005d3ce0e67c65 in RemoveTempRelationsCallback (code=<optimized out>,
arg=<optimized out>) at namespace.c:4703
#9 0x00005d3ce11a3cad in shmem_exit (code=code(at)entry=0) at ipc.c:250
#10 0x00005d3ce11a3da6 in proc_exit_prepare (code=code(at)entry=0) at ipc.c:199
#11 0x00005d3ce11a3e3c in proc_exit (code=code(at)entry=0) at ipc.c:112
#12 0x00005d3ce11d7c07 in PostgresMain (dbname=<optimized out>,
username=<optimized out>) at postgres.c:5046
#13 0x00005d3ce11d0fac in BackendMain (startup_data=<optimized out>,
startup_data_len=<optimized out>)
at backend_startup.c:124
...

Reproduced starting from 7c38ef2a5.