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

Hi, Alexander and Andres!

One session creates a temp table and sets SERIALIZABLE READ ONLY
DEFERRABLE as the session default. Another prepares a SERIALIZABLE
transaction. After the first session disconnects, its backend stays in
GetSafeSnapshot() (wait_event SafeSnapshot). pg_terminate_backend()
does not clear it.

Reproduced on current master.

Cause:
Commit 7c38ef2a5 made RemoveTempRelationsCallback() push an
active snapshot so toast deletion does not fail with "cannot fetch toast
data without an active snapshot". It used GetTransactionSnapshot()
after StartTransactionCommand(). That inherits the session defaults.
Under SERIALIZABLE READ ONLY DEFERRABLE, GetTransactionSnapshot() goes
through GetSafeSnapshot() and waits for concurrent read/write
serializable xacts. A prepared one never finishes that wait.

Effect:
The wait happens during shmem_exit. proc_exit_prepare() clears
ProcDiePending and holds interrupts, so terminate cannot abort it. The
backend remains until the prepared transaction is resolved.

Proposed fix:
7c38ef2a5 only needed a durable active snapshot
across catalog invalidations while toast is fetched. Catalog scans
already use GetCatalogSnapshot() on their own. GetTransactionSnapshot()
was the idiomatic way to obtain a snapshot, not a requirement of the
cleanup. Switching to

PushActiveSnapshot(GetCatalogSnapshot(RelationRelationId));

keeps that contract (Push copies the snapshot onto the active stack, so
it survives InvalidateCatalogSnapshot) and never enters GetSafeSnapshot,
so DEFERRABLE session defaults are harmless.

Thoughts ?

вс, 29 мар. 2026 г. в 15:17, PG Bug reporting form <noreply(at)postgresql(dot)org>:

> 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.
>
>
>
>
>