Re: Adding REPACK [concurrently]

[Jim Jones]

Hi,

while reviewing another patch I noticed that REPACK is trying to access
temp tables from other sessions.

== session 1 ==

$ psql postgres
psql (19devel)
Type "help" for help.

postgres=# SELECT pg_backend_pid();
 pg_backend_pid
----------------
         730392
(1 row)

postgres=# CREATE TEMP TABLE tmp AS SELECT generate_series(1, 1000) AS id;
SELECT 1000
postgres=# BEGIN;
LOCK TABLE tmp IN SHARE MODE;
BEGIN
LOCK TABLE
postgres=*#

== session 2 ==

$ psql postgres
psql (19devel)
Type "help" for help.

postgres=# REPACK;
(waits for LOCK)

== session 3 ==

$ psql postgres
psql (19devel)
Type "help" for help.

postgres=# SELECT pid, relation::regclass, mode, granted
FROM pg_locks
WHERE relation::regclass::text ~~ '%.tmp%';
  pid   |    relation    |        mode         | granted
--------+----------------+---------------------+---------
 730608 | pg_temp_12.tmp | AccessExclusiveLock | f
 730392 | pg_temp_12.tmp | ShareLock           | t
(2 rows)

The same applies for REPACK USING INDEX if indisclustered is true.

I played a bit with the code and perhaps skipping temp relations in
get_tables_to_repack() before they're added to the list can do the
trick. I tried the draft attached and REPACK could run despite the LOCK
in the other session... in case it helps.


Best, Jim
From be0f2aeeaa53e775d5312be87d0ad34f6cd8c271 Mon Sep 17 00:00:00 2001
From: Jim Jones <jim.jo...@uni-muenster.de>
Date: Mon, 23 Mar 2026 16:20:09 +0100
Subject: [PATCH v1] Fix REPACK to skip temporary tables of other sessions

get_tables_to_repack() was adding other sessions' temporary tables
to the work list, causing REPACK to attempt to acquire
AccessExclusiveLock on them. Fix by skipping other-session temp
relations early in get_tables_to_repack(), before they are added
to the list.
---
 src/backend/commands/cluster.c | 15 +++++++++++++++
 1 file changed, 15 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()))
-- 
2.43.0