Subject: Re: VACUUM FULL, CLUSTER, and REPACK block on other sessions' temp tables

From: Chao Li <[email-masked]>
Date: Wed, 25 Mar 2026 09:55:29 +0800

> On Mar 24, 2026, at 23:35, Jim Jones <[email-masked]> wrote:
> 
> Hi,
> 
> While testing another patch [1], I noticed that REPACK is blocked when a
> temporary table is locked in another session. It also turns out that the
> same behaviour occurs with VACUUM FULL and CLUSTER:
> 
> == session 1 ==
> 
> $ psql postgres
> psql (19devel)
> Type "help" for help.
> 
> postgres=# CREATE TEMPORARY TABLE tmp (id int);
> CREATE TABLE
> postgres=# BEGIN;
> LOCK TABLE tmp IN SHARE MODE;
> BEGIN
> LOCK TABLE
> postgres=*#
> 
> == session 2 ==
> 
> $ psql postgres
> psql (19devel)
> Type "help" for help.
> 
> postgres=# REPACK;
> ^CCancel request sent
> ERROR:  canceling statement due to user request
> CONTEXT:  waiting for AccessExclusiveLock on relation 38458 of database 5
> postgres=# VACUUM FULL;
> ^CCancel request sent
> ERROR:  canceling statement due to user request
> CONTEXT:  waiting for AccessExclusiveLock on relation 38458 of database 5
> 
> Skipping temporary relations in get_tables_to_repack() and
> get_all_vacuum_rels() before they're appended to the list seems to do
> the trick -- see attached draft.
> 
> I can reproduce the same behaviour with CLUSTER and VACUUM FULL in
> PG14-PG18. I took a quick look at the code in PG17 and PG18 and the fix
> appears to be straightforward, but before I start working on it, I'd
> like to hear your thoughts. Is it worth the effort?
> 
> Best, Jim
> 
> 1 - https://www.postgresql.org/message-id/13637.1774342137%40localhost<v1-0001-Skip-other-sessions-temp-tables-in-REPACK-CLUSTER.patch>

I think skipping temp tables of another session is reasonable, because anyway they are not accessible from the current session, though visible via pg_class.

Looking at the patch:
```
+			/* Skip temp relations belonging to other sessions */
+			if (class->relpersistence == RELPERSISTENCE_TEMP &&
+				isOtherTempNamespace(class->relnamespace))
```

It uses isOtherTempNamespace(), but I noticed that the header comment of the function says:
```
 * isOtherTempNamespace - is the given namespace some other backend's
* temporary-table namespace (including temporary-toast-table namespaces)?
*
* Note: for most purposes in the C code, this function is obsolete. Use
* RELATION_IS_OTHER_TEMP() instead to detect non-local temp relations.
```

Then looking at RELATION_IS_OTHER_TEMP():
```
#define RELATION_IS_OTHER_TEMP(relation) \
	((relation)->rd_rel->relpersistence == RELPERSISTENCE_TEMP && \
	 !(relation)->rd_islocaltemp)
```

It takes a relation as parameter and check relation->rd_islocaltemp, however in the context of this patch, we have only Form_pg_class.

Then checking how rd_islocaltemp is set:
```
		case RELPERSISTENCE_TEMP:
			if (isTempOrTempToastNamespace(relation->rd_rel->relnamespace))
			{
				relation->rd_backend = ProcNumberForTempRelations();
				relation->rd_islocaltemp = true;
			}
			else
			{
				/*
				 * If it's a temp table, but not one of ours, we have to use
				 * the slow, grotty method to figure out the owning backend.
				 *
				 * Note: it's possible that rd_backend gets set to
				 * MyProcNumber here, in case we are looking at a pg_class
				 * entry left over from a crashed backend that coincidentally
				 * had the same ProcNumber we're using.  We should *not*
				 * consider such a table to be "ours"; this is why we need the
				 * separate rd_islocaltemp flag.  The pg_class entry will get
				 * flushed if/when we clean out the corresponding temp table
				 * namespace in preparation for using it.
				 */
				relation->rd_backend =
					GetTempNamespaceProcNumber(relation->rd_rel->relnamespace);
				Assert(relation->rd_backend != INVALID_PROC_NUMBER);
				relation->rd_islocaltemp = false;
			}
			break;
```

It uses isTempOrTempToastNamespace(relation->rd_rel->relnamespace) to decide relation->rd_islocaltemp.

So, I think this patch should also use "!isTempOrTempToastNamespace(classForm->relnamespace)" instead of isOtherTempNamespace(class->relnamespace). I tried that locally, and it works for me.

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/

---

From: Jim Jones <[email-masked]>
Date: Wed, 25 Mar 2026 11:32:09 +0100

I forgot to create a CF entry. Here it is:
https://commitfest.postgresql.org/patch/6616/

Best, Jim

---

From: Zsolt Parragi <[email-masked]>
Date: Wed, 25 Mar 2026 20:38:25 +0000

Hello!

Shouldn't the patch also include a tap test to verify that the change
works / fails without it?

+ /* Skip temp relations belonging to other sessions */
+ {
+ Oid nsp = get_rel_namespace(index->indrelid);
+
+ if (!isTempOrTempToastNamespace(nsp) && isAnyTempNamespace(nsp))
+ {

Doesn't this result in several repeated syscache lookups?

There's already a SearchSysCacheExsists1 directly above this, then a
get_rel_namespace, then an isAnyTempNamespace. While this probably
isn't performance critical, this should be doable with a single
SearchSysCache1(RELOID...) and then a few conditions, similarly to the
else branch below this?

---

From: Jim Jones <[email-masked]>
Date: Thu, 26 Mar 2026 00:00:02 +0100

Hi

> works / fails without it?

Definitely. I just didn't want to invest much time on tests before
getting feedback on the issue itself.

> SearchSysCache1(RELOID...) and then a few conditions, similarly to the

You're right. Although it is not performance critical we can solve it
with a single SearchSysCache1.

PFA v3 with the improved fix (0001) and tests (0002).

Thanks for the review!

Best, Jim

From 6699534c03772b0e5b06680b2e382a36eb108a67 Mon Sep 17 00:00:00 2001
From: Jim Jones <[email-masked]>
Date: Wed, 25 Mar 2026 23:14:12 +0100
Subject: [PATCH v3 1/2] Skip other sessions' temp tables in REPACK, CLUSTER,
 and VACUUM FULL

get_tables_to_repack() was including other sessions' temporary tables
in the work list, causing REPACK and CLUSTER (without arguments) to
attempt to acquire AccessExclusiveLock on them, potentially blocking
for an extended time. Fix by skipping other-session temp tables early
in get_tables_to_repack(), before they are added to the list. Because
an AccessShareLock has already been acquired per relation at that
point, release it before continuing.

Similarly, get_all_vacuum_rels() suffered from the same problem for
VACUUM FULL. Since no per-relation lock is held during list-building
there, a plain skip suffices.

Author: Jim Jones <[email-masked]>
Reviewed-by: Chao Li <[email-masked]>
Reviewed-by: Zsolt Parragi <[email-masked]>
---
 src/backend/commands/cluster.c | 25 ++++++++++++++++++++++++-
 src/backend/commands/vacuum.c  |  5 +++++
 2 files changed, 29 insertions(+), 1 deletion(-)

diff --git a/src/backend/commands/cluster.c b/src/backend/commands/cluster.c
index 09066db0956..14c11e8e532 100644
--- a/src/backend/commands/cluster.c
+++ b/src/backend/commands/cluster.c
@@ -1679,6 +1679,8 @@ get_tables_to_repack(RepackCommand cmd, bool usingindex, MemoryContext permcxt)
 		{
 			RelToCluster *rtc;
 			Form_pg_index index;
+			HeapTuple	classtup;
+			Form_pg_class classForm;
 			MemoryContext oldcxt;
 
 			index = (Form_pg_index) GETSTRUCT(tuple);
@@ -1693,11 +1695,24 @@ get_tables_to_repack(RepackCommand cmd, bool usingindex, MemoryContext permcxt)
 				continue;
 
 			/* Verify that the table still exists; skip if not */
-			if (!SearchSysCacheExists1(RELOID, ObjectIdGetDatum(index->indrelid)))
+			classtup = SearchSysCache1(RELOID, ObjectIdGetDatum(index->indrelid));
+			if (!HeapTupleIsValid(classtup))
 			{
 				UnlockRelationOid(index->indrelid, AccessShareLock);
 				continue;
 			}
+			classForm = (Form_pg_class) GETSTRUCT(classtup);
+
+			/* Skip temp relations belonging to other sessions */
+			if (classForm->relpersistence == RELPERSISTENCE_TEMP &&
+				!isTempOrTempToastNamespace(classForm->relnamespace))
+			{
+				ReleaseSysCache(classtup);
+				UnlockRelationOid(index->indrelid, AccessShareLock);
+				continue;
+			}
+
+			ReleaseSysCache(classtup);
 
 			/* noisily skip rels which the user can't process */
 			if (!repack_is_permitted_for_relation(cmd, index->indrelid,
@@ -1753,6 +1768,14 @@ get_tables_to_repack(RepackCommand cmd, bool usingindex, MemoryContext permcxt)
 				continue;
 			}
 
+			/* Skip temp relations belonging to other sessions */
+			if (class->relpersistence == RELPERSISTENCE_TEMP &&
+				!isTempOrTempToastNamespace(class->relnamespace))
+			{
+				UnlockRelationOid(class->oid, AccessShareLock);
+				continue;
+			}
+
 			/* noisily skip rels which the user can't process */
 			if (!repack_is_permitted_for_relation(cmd, class->oid,
 												  GetUserId()))
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index bce3a2daa24..9b0a5a38a8a 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -1062,6 +1062,11 @@ get_all_vacuum_rels(MemoryContext vac_context, int options)
 			classForm->relkind != RELKIND_PARTITIONED_TABLE)
 			continue;
 
+		/* Skip temp relations belonging to other sessions */
+		if (classForm->relpersistence == RELPERSISTENCE_TEMP &&
+			!isTempOrTempToastNamespace(classForm->relnamespace))
+			continue;
+
 		/* check permissions of relation */
 		if (!vacuum_is_permitted_for_relation(relid, classForm, options))
 			continue;
-- 
2.43.0

From 97d1b2023b8c4ac5d92610e9761d112ab68cbe23 Mon Sep 17 00:00:00 2001
From: Jim Jones <[email-masked]>
Date: Wed, 25 Mar 2026 23:30:55 +0100
Subject: [PATCH v3 2/2] Test VACUUM FULL, CLUSTER, and REPACK with locked temp
 tables

This test creates a background session with a temp table and marks
its index as clustered (making it visible to both the pg_class scan
used by VACUUM FULL and REPACK, and the pg_index scan used by CLUSTER),
then holds ACCESS SHARE LOCK in an open transaction.  Each command
runs with lock_timeout = '1ms'.  Since lock_timeout only fires when a
backend actually blocks waiting for a lock, 1ms is sufficient.
---
 src/test/modules/test_misc/meson.build        |  1 +
 .../t/011_vacuum_cluster_temp_tables.pl       | 65 +++++++++++++++++++
 2 files changed, 66 insertions(+)
 create mode 100644 src/test/modules/test_misc/t/011_vacuum_cluster_temp_tables.pl

diff --git a/src/test/modules/test_misc/meson.build b/src/test/modules/test_misc/meson.build
index 6e8db1621a7..d64e8df56bf 100644
--- a/src/test/modules/test_misc/meson.build
+++ b/src/test/modules/test_misc/meson.build
@@ -19,6 +19,7 @@ tests += {
       't/008_replslot_single_user.pl',
       't/009_log_temp_files.pl',
       't/010_index_concurrently_upsert.pl',
+      't/011_vacuum_cluster_temp_tables.pl',
     ],
     # The injection points are cluster-wide, so disable installcheck
     'runningcheck': false,
diff --git a/src/test/modules/test_misc/t/011_vacuum_cluster_temp_tables.pl b/src/test/modules/test_misc/t/011_vacuum_cluster_temp_tables.pl
new file mode 100644
index 00000000000..a612b4d6361
--- /dev/null
+++ b/src/test/modules/test_misc/t/011_vacuum_cluster_temp_tables.pl
@@ -0,0 +1,65 @@
+# Copyright (c) 2026, PostgreSQL Global Development Group
+#
+# Verify that no-argument VACUUM FULL, CLUSTER, and REPACK skip temporary
+# tables belonging to other sessions.
+#
+# A background session creates a temp table and marks its index as clustered —
+# making it visible to both the pg_class scan (VACUUM FULL, REPACK) and the
+# pg_index scan (CLUSTER) — then holds ACCESS SHARE LOCK in an open transaction.
+# Each command runs with lock_timeout = '1ms'. Since lock_timeout only
+# fires when a backend actually blocks waiting for a lock, 1ms is sufficient.
+
+use strict;
+use warnings;
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+
+my $node = PostgreSQL::Test::Cluster->new('vacuum_cluster_temp');
+$node->init;
+$node->start;
+
+# Session 1: build the temp table and hold a conflicting lock.
+my $psql1 = $node->background_psql('postgres');
+
+$psql1->query_safe(
+	q{CREATE TEMP TABLE temp_repack_test (val int);
+	  INSERT INTO temp_repack_test VALUES (1);
+	  CREATE INDEX temp_repack_idx ON temp_repack_test (val);
+	  CLUSTER temp_repack_test USING temp_repack_idx;});
+
+$psql1->query_safe(q{BEGIN});
+$psql1->query_safe(q{LOCK TABLE temp_repack_test IN ACCESS SHARE MODE});
+
+my ($stdout, $stderr, $ret);
+
+# VACUUM FULL — pg_class scan path.
+$ret = $node->psql(
+	'postgres',
+	"SET lock_timeout = '1ms'; VACUUM FULL;",
+	stdout => \$stdout,
+	stderr => \$stderr);
+is($ret, 0,
+	'VACUUM FULL completes without blocking on another session temp table');
+
+# CLUSTER — pg_index scan path (indisclustered entries).
+$ret = $node->psql(
+	'postgres',
+	"SET lock_timeout = '1ms'; CLUSTER;",
+	stdout => \$stdout,
+	stderr => \$stderr);
+is($ret, 0,
+	'CLUSTER completes without blocking on another session temp table');
+
+# REPACK — pg_class scan path.
+$ret = $node->psql(
+	'postgres',
+	"SET lock_timeout = '1ms'; REPACK;",
+	stdout => \$stdout,
+	stderr => \$stderr);
+is($ret, 0,
+	'REPACK completes without blocking on another session temp table');
+
+$psql1->quit;
+
+done_testing();
-- 
2.43.0

---

From: Jim Jones <[email-masked]>
Date: Mon, 6 Apr 2026 20:13:45 +0200

rebase

Jim

From 7a50ea8527634a39ccb9abbf8f573883faccd353 Mon Sep 17 00:00:00 2001
From: Jim Jones <[email-masked]>
Date: Wed, 25 Mar 2026 23:14:12 +0100
Subject: [PATCH v4 1/2] Skip other sessions' temp tables in REPACK, CLUSTER,
 and VACUUM FULL

index f241e18b153..ffbc5ed6f85 100644

 
 
index 0ed363d1c85..913dfb4ea8d 100644

From 3e689b85190fc074a4fe7c9e2e1b3f618faf08bf Mon Sep 17 00:00:00 2001
From: Jim Jones <[email-masked]>
Date: Mon, 6 Apr 2026 19:40:56 +0200
Subject: [PATCH v4 2/2] Test VACUUM FULL, CLUSTER, and REPACK with locked temp
 tables

 .../t/012_vacuum_cluster_temp_tables.pl       | 65 +++++++++++++++++++
 create mode 100644 src/test/modules/test_misc/t/012_vacuum_cluster_temp_tables.pl

index 1b25d98f7f3..8995bc837f2 100644
@@ -20,6 +20,7 @@ tests += {
       't/011_lock_stats.pl',
+      't/012_vacuum_cluster_temp_tables.pl',
diff --git a/src/test/modules/test_misc/t/012_vacuum_cluster_temp_tables.pl b/src/test/modules/test_misc/t/012_vacuum_cluster_temp_tables.pl
+++ b/src/test/modules/test_misc/t/012_vacuum_cluster_temp_tables.pl

---

From: Jim Jones <[email-masked]>
Date: Tue, 7 Apr 2026 00:52:20 +0200

rebase

Jim

From f7876b7c225146fe3deef681841d0ad6d9782a5d Mon Sep 17 00:00:00 2001
From: Jim Jones <[email-masked]>
Date: Tue, 7 Apr 2026 00:27:59 +0200
Subject: [PATCH v5 1/2] Skip other sessions' temp tables in REPACK, CLUSTER,
 and VACUUM FULL

 src/backend/commands/repack.c | 25 ++++++++++++++++++++++++-

diff --git a/src/backend/commands/repack.c b/src/backend/commands/repack.c
index 17b639b3b44..1806efddb8f 100644
--- a/src/backend/commands/repack.c
+++ b/src/backend/commands/repack.c
@@ -2107,6 +2107,8 @@ get_tables_to_repack(RepackCommand cmd, bool usingindex, MemoryContext permcxt)
 		{
 			RelToCluster *rtc;
 			Form_pg_index index;
+			HeapTuple classtup;
+			Form_pg_class classForm;
 			MemoryContext oldcxt;
 
 			index = (Form_pg_index) GETSTRUCT(tuple);
@@ -2121,11 +2123,24 @@ get_tables_to_repack(RepackCommand cmd, bool usingindex, MemoryContext permcxt)
 				continue;

 			/* noisily skip rels which the user can't process */
 			if (!repack_is_permitted_for_relation(cmd, index->indrelid,
@@ -2181,6 +2196,14 @@ get_tables_to_repack(RepackCommand cmd, bool usingindex, MemoryContext permcxt)
 				continue;
 			}
 
index 99d0db82ed7..a4abb29cf64 100644
@@ -1063,6 +1063,11 @@ get_all_vacuum_rels(MemoryContext vac_context, int options)

From 4cc9f3d4b5aa8c3544599c47ab3ec9a62a96381d Mon Sep 17 00:00:00 2001
From: Jim Jones <[email-masked]>
Date: Mon, 6 Apr 2026 19:40:56 +0200
Subject: [PATCH v5 2/2] Test VACUUM FULL, CLUSTER, and REPACK with locked temp
 tables

---

From: Jim Jones <[email-masked]>
Date: Fri, 1 May 2026 21:34:42 +0200

rebase

Jim

From def7fbf223f6f2db2e71eeb42fa10536f16b4c0b Mon Sep 17 00:00:00 2001
From: Jim Jones <[email-masked]>
Date: Fri, 1 May 2026 21:31:25 +0200
Subject: [PATCH v6 2/2] Test VACUUM FULL, CLUSTER, and REPACK with locked temp
 tables

 .../t/013_vacuum_cluster_temp_tables.pl       | 65 +++++++++++++++++++
 create mode 100644 src/test/modules/test_misc/t/013_vacuum_cluster_temp_tables.pl

index 356d8454b39..e74d461db3a 100644
@@ -21,6 +21,7 @@ tests += {
       't/012_ddlutils.pl',
+      't/013_vacuum_cluster_temp_tables.pl',
diff --git a/src/test/modules/test_misc/t/013_vacuum_cluster_temp_tables.pl b/src/test/modules/test_misc/t/013_vacuum_cluster_temp_tables.pl
+++ b/src/test/modules/test_misc/t/013_vacuum_cluster_temp_tables.pl

From a0e7497f095377add06a65b97b79fd0162fabeec Mon Sep 17 00:00:00 2001
From: Jim Jones <[email-masked]>
Date: Tue, 7 Apr 2026 00:27:59 +0200
Subject: [PATCH v6 1/2] Skip other sessions' temp tables in REPACK, CLUSTER,
 and VACUUM FULL

diff --git a/src/backend/commands/repack.c b/src/backend/commands/repack.c
index 9d162957bc3..da4b309c101 100644
--- a/src/backend/commands/repack.c
+++ b/src/backend/commands/repack.c
@@ -2106,6 +2106,8 @@ get_tables_to_repack(RepackCommand cmd, bool usingindex, MemoryContext permcxt)
 		{
 			RelToCluster *rtc;
 			Form_pg_index index;
+			HeapTuple classtup;
+			Form_pg_class classForm;
 			MemoryContext oldcxt;
 
 			index = (Form_pg_index) GETSTRUCT(tuple);
@@ -2120,11 +2122,24 @@ get_tables_to_repack(RepackCommand cmd, bool usingindex, MemoryContext permcxt)
 				continue;

 			/* noisily skip rels which the user can't process */
 			if (!repack_is_permitted_for_relation(cmd, index->indrelid,
@@ -2180,6 +2195,14 @@ get_tables_to_repack(RepackCommand cmd, bool usingindex, MemoryContext permcxt)
 				continue;
 			}