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.
This thread has been committed, so CI has stopped here. Anything below is the last result it produced.
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 v4 (message #4), September 02, 2026 at 09: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_4 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_4 && git checkout t139161_4Patchset v4 (message #4) is on t139161_4
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.
Hi Andrey, Alexander,
I think using GetCatalogSnapshot() here is correct. It is an ordinary
MVCC snapshot, so it pins xmin, while get_toast_snapshot() still returns
SnapshotToast. PushActiveSnapshot() copies it, so later catalog
invalidations do not remove that protection.
This does not make uncommitted catalog changes from a prepared
transaction visible: its XID remains in progress to the catalog
snapshot.
A transaction that accessed temporary objects cannot be prepared, and
the cleanup only writes temporary and system relations, which do not
participate in predicate locking. I additionally tried the reproducer
with a prepared CREATE TABLE.
The existing temp-schema-cleanup isolation test, including its toasted
catalog object, passes with the change. The new test fails without the
fix and passes with it.
The only issue I found is that the new TAP test is missing from the
test_misc list in meson.build.
With that fixed, this looks ready for committer to me.
Thank you!
Best regards, Andrey Borodin.
чт, 27 авг. 2026 г. в 13:16, Andrey Borodin <x4mmm@yandex-team.ru>:
With that fixed, this looks ready for committer to me.
Hi Andrey!
Thanks for the review, forgot to add to make, fixed in v2.
--
Regards,
Rachitskiy Andrey
On Thu, Aug 27, 2026 at 5:49 PM Andrey Rachitskiy <pl0h0yp1@gmail.com> wrote:
Thanks for the review, forgot to add to make, fixed in v2.
Thanks for updating the patch! It looks good to me.
This patch should be backpatched to v15, where commit 7c38ef2a5 was introduced?
Regards,
--
Fujii Masao
ср, 2 сент. 2026 г. в 16:01, Fujii Masao <masao.fujii@gmail.com>:
Thanks for updating the patch! It looks good to me.
Dear Fujii-san,
Thanks for the review.
This patch should be backpatched to v15, where commit 7c38ef2a5 was
introduced?
Yes. 7c38ef2a5 is the master commit (v15 line); the same change was
backpatched the same day as far as 10-, including 7bbfe599416 on
REL_14_STABLE. So I think we should backpatch through 14.
--
Regards,
Rachitskiy Andrey
On Wed, Sep 2, 2026 at 8:45 PM Andrey Rachitskiy <pl0h0yp1@gmail.com> wrote:
Yes. 7c38ef2a5 is the master commit (v15 line); the same change was backpatched the same day as far as 10-, including 7bbfe599416 on REL_14_STABLE. So I think we should backpatch through 14.
So I've pushed the patch and backpatched it through v14. Thanks!
Regards,
--
Fujii Masao