agora inbox for pgsql-bugs@postgresql.org
help / color / mirror / Atom feedBUG #19441: Backend waits for serializable snapshot indefinitely on removing temp relations
6+ messages / 4 participants
[nested] [flat]
* BUG #19441: Backend waits for serializable snapshot indefinitely on removing temp relations
@ 2026-03-29 10:00 PG Bug reporting form <noreply@postgresql.org>
2026-08-27 08:16 ` Re: BUG #19441: Backend waits for serializable snapshot indefinitely on removing temp relations Andrey Borodin <x4mmm@yandex-team.ru>
0 siblings, 1 reply; 6+ messages in thread
From: PG Bug reporting form @ 2026-03-29 10:00 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: exclusion@gmail.com
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #19441: Backend waits for serializable snapshot indefinitely on removing temp relations
2026-03-29 10:00 BUG #19441: Backend waits for serializable snapshot indefinitely on removing temp relations PG Bug reporting form <noreply@postgresql.org>
@ 2026-08-27 08:16 ` Andrey Borodin <x4mmm@yandex-team.ru>
2026-08-27 08:49 ` Re: BUG #19441: Backend waits for serializable snapshot indefinitely on removing temp relations Andrey Rachitskiy <pl0h0yp1@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Andrey Borodin @ 2026-08-27 08:16 UTC (permalink / raw)
To: Andrey Rachitskiy <pl0h0yp1@gmail.com>; +Cc: Alexander Lakhin <exclusion@gmail.com>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>; Andres Freund <andres@anarazel.de>
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #19441: Backend waits for serializable snapshot indefinitely on removing temp relations
2026-03-29 10:00 BUG #19441: Backend waits for serializable snapshot indefinitely on removing temp relations PG Bug reporting form <noreply@postgresql.org>
2026-08-27 08:16 ` Re: BUG #19441: Backend waits for serializable snapshot indefinitely on removing temp relations Andrey Borodin <x4mmm@yandex-team.ru>
@ 2026-08-27 08:49 ` Andrey Rachitskiy <pl0h0yp1@gmail.com>
2026-09-02 11:01 ` Re: BUG #19441: Backend waits for serializable snapshot indefinitely on removing temp relations Fujii Masao <masao.fujii@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Andrey Rachitskiy @ 2026-08-27 08:49 UTC (permalink / raw)
To: Andrey Borodin <x4mmm@yandex-team.ru>; +Cc: Alexander Lakhin <exclusion@gmail.com>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>; Andres Freund <andres@anarazel.de>
чт, 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
Attachments:
[text/x-patch] v2-0001-Fix-temp-cleanup-hang-under-SERIALIZABLE-DEFERRABLE.patch (4.3K, ../../CAB8bMis5iTAz6M158UNn=e9z-N5B+RFaa9OBhWNqO8YEu6-5AQ@mail.gmail.com/3-v2-0001-Fix-temp-cleanup-hang-under-SERIALIZABLE-DEFERRABLE.patch)
download | inline diff:
From: Andrey Rachitskiy <pl0h0yp1@gmail.com>
Date: Sat, 8 Aug 2026 12:40:00 +0500
Subject: [PATCH v2] Fix temp cleanup hang under SERIALIZABLE READ ONLY DEFERRABLE (BUG #19441)
RemoveTempRelationsCallback() pushes an active snapshot so toast fetches
during backend-exit temp drops succeed. Using GetTransactionSnapshot()
honors SERIALIZABLE READ ONLY DEFERRABLE session defaults and waits in
GetSafeSnapshot() for concurrent read/write serializable transactions.
A still-prepared serializable xact never finishes that wait, and during
proc_exit interrupts are held off, so the backend hangs until the
prepared transaction is resolved.
Switch to GetCatalogSnapshot(), which is sufficient for this catalog
cleanup and never enters the deferrable safe-snapshot wait.
Bug: #19441
Author: Andrey Rachitskiy <pl0h0yp1@gmail.com>
Reviewed-by: Andrey Borodin <x4mmm@yandex-team.ru>
Reported-by: Alexander Lakhin <exclusion@gmail.com>
Discussion: https://postgr.es/m/19441-ec29f3b1363b4a68@postgresql.org
---
diff --git a/src/backend/catalog/namespace.c b/src/backend/catalog/namespace.c
index 56b87d878e8..165a87e6397 100644
--- a/src/backend/catalog/namespace.c
+++ b/src/backend/catalog/namespace.c
@@ -4700,7 +4700,15 @@ RemoveTempRelationsCallback(int code, Datum arg)
/* Need to ensure we have a usable transaction. */
AbortOutOfAnyTransaction();
StartTransactionCommand();
- PushActiveSnapshot(GetTransactionSnapshot());
+
+ /*
+ * Need an active snapshot for toast fetches during deletion. Do not
+ * use GetTransactionSnapshot(): under SERIALIZABLE READ ONLY
+ * DEFERRABLE it may wait in GetSafeSnapshot(), and proc_exit holds
+ * off interrupts so that wait cannot be cancelled. A catalog
+ * snapshot is enough and avoids that path.
+ */
+ PushActiveSnapshot(GetCatalogSnapshot(RelationRelationId));
RemoveTempRelations(myTempNamespace);
diff --git a/src/test/modules/test_misc/meson.build b/src/test/modules/test_misc/meson.build
index ee290698b31..5d81f5b13be 100644
--- a/src/test/modules/test_misc/meson.build
+++ b/src/test/modules/test_misc/meson.build
@@ -23,6 +23,7 @@ tests += {
't/012_ddlutils.pl',
't/013_temp_obj_multisession.pl',
't/014_log_statement_max_length.pl',
+ 't/015_temp_schema_exit_deferrable.pl',
],
# The injection points are cluster-wide, so disable installcheck
'runningcheck': false,
diff --git a/src/test/modules/test_misc/t/015_temp_schema_exit_deferrable.pl b/src/test/modules/test_misc/t/015_temp_schema_exit_deferrable.pl
new file mode 100644
index 00000000000..07de36630a6
--- /dev/null
+++ b/src/test/modules/test_misc/t/015_temp_schema_exit_deferrable.pl
@@ -0,0 +1,58 @@
+# Copyright (c) 2026, PostgreSQL Global Development Group
+
+# Backend exit must finish temp-schema cleanup even when the session default
+# is SERIALIZABLE READ ONLY DEFERRABLE and a prepared serializable transaction
+# is still around.
+
+use strict;
+use warnings FATAL => 'all';
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+
+my $node = PostgreSQL::Test::Cluster->new('deferrable_temp_exit');
+$node->init;
+$node->append_conf('postgresql.conf', 'max_prepared_transactions = 1');
+$node->start;
+
+my $psql1 = $node->background_psql('postgres');
+
+$psql1->query_safe(
+ q{
+CREATE TEMPORARY TABLE tt (i int);
+SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE;
+});
+
+my $pid = $psql1->query_safe(q{SELECT pg_backend_pid();});
+chomp $pid;
+like($pid, qr/^\d+$/, "backend pid $pid");
+
+# Overlapping read/write serializable xact that outlives session 1.
+$node->safe_psql(
+ 'postgres',
+ q{
+CREATE TABLE t (i int);
+BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
+INSERT INTO t VALUES (1);
+PREPARE TRANSACTION 'pt';
+});
+
+# Disconnect: RemoveTempRelationsCallback runs during backend exit.
+$psql1->quit;
+
+ok( $node->poll_query_until(
+ 'postgres',
+ "SELECT count(*) = 0 FROM pg_stat_activity WHERE pid = $pid"),
+ 'backend exited despite prepared serializable xact');
+
+is( $node->safe_psql(
+ 'postgres',
+ q{SELECT count(*) FROM pg_class WHERE relname = 'tt' AND relpersistence = 't'}
+ ),
+ '0',
+ 'temporary table cleaned up on exit');
+
+$node->safe_psql('postgres', q{ROLLBACK PREPARED 'pt';});
+$node->safe_psql('postgres', q{DROP TABLE t;});
+
+done_testing();
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #19441: Backend waits for serializable snapshot indefinitely on removing temp relations
2026-03-29 10:00 BUG #19441: Backend waits for serializable snapshot indefinitely on removing temp relations PG Bug reporting form <noreply@postgresql.org>
2026-08-27 08:16 ` Re: BUG #19441: Backend waits for serializable snapshot indefinitely on removing temp relations Andrey Borodin <x4mmm@yandex-team.ru>
2026-08-27 08:49 ` Re: BUG #19441: Backend waits for serializable snapshot indefinitely on removing temp relations Andrey Rachitskiy <pl0h0yp1@gmail.com>
@ 2026-09-02 11:01 ` Fujii Masao <masao.fujii@gmail.com>
2026-09-02 11:44 ` Re: BUG #19441: Backend waits for serializable snapshot indefinitely on removing temp relations Andrey Rachitskiy <pl0h0yp1@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Fujii Masao @ 2026-09-02 11:01 UTC (permalink / raw)
To: Andrey Rachitskiy <pl0h0yp1@gmail.com>; +Cc: Andrey Borodin <x4mmm@yandex-team.ru>; Alexander Lakhin <exclusion@gmail.com>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>; Andres Freund <andres@anarazel.de>
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #19441: Backend waits for serializable snapshot indefinitely on removing temp relations
2026-03-29 10:00 BUG #19441: Backend waits for serializable snapshot indefinitely on removing temp relations PG Bug reporting form <noreply@postgresql.org>
2026-08-27 08:16 ` Re: BUG #19441: Backend waits for serializable snapshot indefinitely on removing temp relations Andrey Borodin <x4mmm@yandex-team.ru>
2026-08-27 08:49 ` Re: BUG #19441: Backend waits for serializable snapshot indefinitely on removing temp relations Andrey Rachitskiy <pl0h0yp1@gmail.com>
2026-09-02 11:01 ` Re: BUG #19441: Backend waits for serializable snapshot indefinitely on removing temp relations Fujii Masao <masao.fujii@gmail.com>
@ 2026-09-02 11:44 ` Andrey Rachitskiy <pl0h0yp1@gmail.com>
2026-09-03 03:20 ` Re: BUG #19441: Backend waits for serializable snapshot indefinitely on removing temp relations Fujii Masao <masao.fujii@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Andrey Rachitskiy @ 2026-09-02 11:44 UTC (permalink / raw)
To: Fujii Masao <masao.fujii@gmail.com>; +Cc: Andrey Borodin <x4mmm@yandex-team.ru>; Alexander Lakhin <exclusion@gmail.com>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>; Andres Freund <andres@anarazel.de>
ср, 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: BUG #19441: Backend waits for serializable snapshot indefinitely on removing temp relations
2026-03-29 10:00 BUG #19441: Backend waits for serializable snapshot indefinitely on removing temp relations PG Bug reporting form <noreply@postgresql.org>
2026-08-27 08:16 ` Re: BUG #19441: Backend waits for serializable snapshot indefinitely on removing temp relations Andrey Borodin <x4mmm@yandex-team.ru>
2026-08-27 08:49 ` Re: BUG #19441: Backend waits for serializable snapshot indefinitely on removing temp relations Andrey Rachitskiy <pl0h0yp1@gmail.com>
2026-09-02 11:01 ` Re: BUG #19441: Backend waits for serializable snapshot indefinitely on removing temp relations Fujii Masao <masao.fujii@gmail.com>
2026-09-02 11:44 ` Re: BUG #19441: Backend waits for serializable snapshot indefinitely on removing temp relations Andrey Rachitskiy <pl0h0yp1@gmail.com>
@ 2026-09-03 03:20 ` Fujii Masao <masao.fujii@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Fujii Masao @ 2026-09-03 03:20 UTC (permalink / raw)
To: Andrey Rachitskiy <pl0h0yp1@gmail.com>; +Cc: Andrey Borodin <x4mmm@yandex-team.ru>; Alexander Lakhin <exclusion@gmail.com>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>; Andres Freund <andres@anarazel.de>
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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2026-09-03 03:20 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-03-29 10:00 BUG #19441: Backend waits for serializable snapshot indefinitely on removing temp relations PG Bug reporting form <noreply@postgresql.org>
2026-08-27 08:16 ` Andrey Borodin <x4mmm@yandex-team.ru>
2026-08-27 08:49 ` Andrey Rachitskiy <pl0h0yp1@gmail.com>
2026-09-02 11:01 ` Fujii Masao <masao.fujii@gmail.com>
2026-09-02 11:44 ` Andrey Rachitskiy <pl0h0yp1@gmail.com>
2026-09-03 03:20 ` Fujii Masao <masao.fujii@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox