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
From d57d212305abd2cfb15c6c61f98e503e1a736ab2 Mon Sep 17 00:00:00 2001
From: Jim Jones <[email protected]>
Date: Tue, 24 Mar 2026 13:50:26 +0100
Subject: [PATCH v1] 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.
---
src/backend/commands/cluster.c | 15 +++++++++++++++
src/backend/commands/vacuum.c | 5 +++++
2 files changed, 20 insertions(+)
diff --git a/src/backend/commands/cluster.c b/src/backend/commands/cluster.c
index 09066db095..f701d426dd 100644
--- a/src/backend/commands/cluster.c
+++ b/src/backend/commands/cluster.c
@@ -1699,6 +1699,13 @@ get_tables_to_repack(RepackCommand cmd, bool usingindex, MemoryContext permcxt)
continue;
}
+ /* Skip temp relations belonging to other sessions */
+ if (isOtherTempNamespace(get_rel_namespace(index->indrelid)))
+ {
+ UnlockRelationOid(index->indrelid, AccessShareLock);
+ continue;
+ }
+
/* noisily skip rels which the user can't process */
if (!repack_is_permitted_for_relation(cmd, index->indrelid,
GetUserId()))
@@ -1753,6 +1760,14 @@ get_tables_to_repack(RepackCommand cmd, bool usingindex, MemoryContext permcxt)
continue;
}
+ /* Skip temp relations belonging to other sessions */
+ if (class->relpersistence == RELPERSISTENCE_TEMP &&
+ isOtherTempNamespace(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 bce3a2daa2..642f4fee1c 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 &&
+ isOtherTempNamespace(classForm->relnamespace))
+ continue;
+
/* check permissions of relation */
if (!vacuum_is_permitted_for_relation(relid, classForm, options))
continue;
--
2.43.0
