BUG #19441: Backend waits for serializable snapshot indefinitely on removing temp relations
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
You can run a PostgreSQL built from this patch straight from Docker, with no checkout and no build:
docker run --rm -p 5432:5432 ghcr.io/hackorum-dev/postgres-patch:t139161psql -h localhost -U postgresBuilt from patchset v2 (message #2), August 25, 2026 at 08:34 AM.
Every patchset is also pushed to a branch of our PostgreSQL fork, so you can check out the same tree CI built. Without a PostgreSQL checkout:
git clone --branch t139161_2 https://github.com/hackorum-dev/postgres.gitIn a checkout you already have, add the fork once:
git remote add hackorum https://github.com/hackorum-dev/postgres.gitthen, for this patchset and every later one:
git fetch hackorum t139161_2 && git checkout t139161_2Patchset v2 (message #2) is on t139161_2
The following bug has been logged on the website:
Bug reference: 19441
Logged by: Alexander Lakhin
Email address: exclusion@gmail.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@entry=-1)
at ../sysdeps/unix/sysv/linux/epoll_wait.c:30
#1 0x00005d3ce11b11d2 in WaitEventSetWaitBlock
(set=set@entry=0x5d3d166cd800, cur_timeout=cur_timeout@entry=-1,
occurred_events=occurred_events@entry=0x7ffeb661f160,
nevents=nevents@entry=1) at waiteventset.c:1193
#2 0x00005d3ce11b1bd4 in WaitEventSetWait (set=0x5d3d166cd800,
timeout=timeout@entry=-1,
occurred_events=occurred_events@entry=0x7ffeb661f160,
nevents=nevents@entry=1,
wait_event_info=wait_event_info@entry=134217779) at waiteventset.c:1141
#3 0x00005d3ce11a4b78 in WaitLatch (latch=<optimized out>,
wakeEvents=wakeEvents@entry=33, timeout=timeout@entry=0,
wait_event_info=wait_event_info@entry=134217779) at latch.c:196
#4 0x00005d3ce11c8188 in ProcWaitForSignal
(wait_event_info=wait_event_info@entry=134217779) at proc.c:2005
#5 0x00005d3ce11c42bf in GetSafeSnapshot
(origSnapshot=origSnapshot@entry=0x5d3ce17753e0 <CurrentSnapshotData>)
at predicate.c:1600
#6 0x00005d3ce11c4436 in GetSerializableTransactionSnapshot
(snapshot=snapshot@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@entry=0) at ipc.c:250
#10 0x00005d3ce11a3da6 in proc_exit_prepare (code=code@entry=0) at ipc.c:199
#11 0x00005d3ce11a3e3c in proc_exit (code=code@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.
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@postgresql.org>:
Show quoted text
The following bug has been logged on the website:
Bug reference: 19441
Logged by: Alexander Lakhin
Email address: exclusion@gmail.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 1echo "
CREATE TABLE t (i int);
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
INSERT INTO t VALUES (1);
PREPARE TRANSACTION 'pt';
" | psqlwait
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 5psql -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 | tgdb -p 2143677
(gdb) bt
#0 0x000077c27fb2a007 in epoll_wait (epfd=5, events=0x5d3d166cd868,
maxevents=1, timeout=timeout@entry=-1)
at ../sysdeps/unix/sysv/linux/epoll_wait.c:30
#1 0x00005d3ce11b11d2 in WaitEventSetWaitBlock
(set=set@entry=0x5d3d166cd800, cur_timeout=cur_timeout@entry=-1,
occurred_events=occurred_events@entry=0x7ffeb661f160,
nevents=nevents@entry=1) at waiteventset.c:1193
#2 0x00005d3ce11b1bd4 in WaitEventSetWait (set=0x5d3d166cd800,
timeout=timeout@entry=-1,
occurred_events=occurred_events@entry=0x7ffeb661f160,
nevents=nevents@entry=1,
wait_event_info=wait_event_info@entry=134217779) at
waiteventset.c:1141
#3 0x00005d3ce11a4b78 in WaitLatch (latch=<optimized out>,
wakeEvents=wakeEvents@entry=33, timeout=timeout@entry=0,
wait_event_info=wait_event_info@entry=134217779) at latch.c:196
#4 0x00005d3ce11c8188 in ProcWaitForSignal
(wait_event_info=wait_event_info@entry=134217779) at proc.c:2005
#5 0x00005d3ce11c42bf in GetSafeSnapshot
(origSnapshot=origSnapshot@entry=0x5d3ce17753e0 <CurrentSnapshotData>)
at predicate.c:1600
#6 0x00005d3ce11c4436 in GetSerializableTransactionSnapshot
(snapshot=snapshot@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@entry=0) at ipc.c:250
#10 0x00005d3ce11a3da6 in proc_exit_prepare (code=code@entry=0) at
ipc.c:199
#11 0x00005d3ce11a3e3c in proc_exit (code=code@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.