Feedback-ID: ie3de48e3:Fastmail
Date: Tue, 7 Jul 2026 18:53:39 +0200
From: =?utf-8?Q?=C3=81lvaro?= Herrera <alvherre@kurilemu.de>
To: cca5507 <cca5507@qq.com>
Cc: pgsql-hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: Do not lock tables in get_tables_to_repack
Message-ID: <ak0ujOl6WeakTEqx@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="op6mnexl7cn72cto"
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <tencent_9F290B256A3F52B66542F1140E32ECC64309@qq.com>
Archived-At: 
 <https://www.postgresql.org/message-id/ak0ujOl6WeakTEqx%40alvherre.pgsql>
Precedence: bulk


--op6mnexl7cn72cto
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit

On 2026-Jun-16, cca5507 wrote:

> Hi hackers,
> 
> When doing a whole database repack, we build a list of repackable
> tables and take a lock on them to prevent concurrent drops. But
> concurrent drops can always happen after we build the list because
> we process each table in a separate transaction.

Not only that.  We have actually three ways to obtain the list of tables
to repack, and only one of these obtains the locks.  So this code is
internally inconsistent.  I agree that we should do something like your
patch.  I wanted to be a little more defensive though; how about the
attached?

-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/

--op6mnexl7cn72cto
Content-Type: text/x-diff; charset=utf-8
Content-Disposition: attachment;
	filename=v2-0001-Do-not-lock-tables-in-get_tables_to_repack.patch

From 5ae359740406ae10db552811424f38aa4faeabdc Mon Sep 17 00:00:00 2001
From: ChangAo Chen <cca5507@qq.com>
Date: Tue, 16 Jun 2026 14:49:49 +0800
Subject: [PATCH v2 1/2] Do not lock tables in get_tables_to_repack().

When doing a whole database repack, we build a list of repackable
tables and take a lock on them to prevent concurrent drops. But
concurrent drops can always happen after we build the list because
we process each table in a separate transaction. The
ConditionalLockRelationOid() also makes the default behavior like
SKIP_LOCKED, which is unexpected.

To remove the locks, we need to make repack_is_permitted_for_relation()
handles concurrent drops correctly: it should not report an error
when failing to search the syscache in pg_class_aclcheck(). Use
pg_class_aclcheck_ext() instead to detect a concurrent drop. Also
check the return value of get_rel_name().

While at it, replace relation_close() with table_close() to match
the table_open().
---
 src/backend/commands/repack.c | 67 ++++++++++-------------------------
 1 file changed, 19 insertions(+), 48 deletions(-)

diff --git a/src/backend/commands/repack.c b/src/backend/commands/repack.c
index faa07d1a118..2879c8af574 100644
--- a/src/backend/commands/repack.c
+++ b/src/backend/commands/repack.c
@@ -2169,22 +2169,9 @@ get_tables_to_repack(RepackCommand cmd, bool usingindex, MemoryContext permcxt)
 
 			index = (Form_pg_index) GETSTRUCT(tuple);
 
-			/*
-			 * Try to obtain a light lock on the index's table, to ensure it
-			 * doesn't go away while we collect the list.  If we cannot, just
-			 * disregard it.  Be sure to release this if we ultimately decide
-			 * not to process the table!
-			 */
-			if (!ConditionalLockRelationOid(index->indrelid, AccessShareLock))
-				continue;
-
-			/* Verify that the table still exists; skip if not */
 			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 */
@@ -2192,7 +2179,6 @@ get_tables_to_repack(RepackCommand cmd, bool usingindex, MemoryContext permcxt)
 				!isTempOrTempToastNamespace(classForm->relnamespace))
 			{
 				ReleaseSysCache(classtup);
-				UnlockRelationOid(index->indrelid, AccessShareLock);
 				continue;
 			}
 
@@ -2201,10 +2187,7 @@ get_tables_to_repack(RepackCommand cmd, bool usingindex, MemoryContext permcxt)
 			/* noisily skip rels which the user can't process */
 			if (!repack_is_permitted_for_relation(cmd, index->indrelid,
 												  GetUserId()))
-			{
-				UnlockRelationOid(index->indrelid, AccessShareLock);
 				continue;
-			}
 
 			/* Use a permanent memory context for the result list */
 			oldcxt = MemoryContextSwitchTo(permcxt);
@@ -2228,45 +2211,20 @@ get_tables_to_repack(RepackCommand cmd, bool usingindex, MemoryContext permcxt)
 
 			class = (Form_pg_class) GETSTRUCT(tuple);
 
-			/*
-			 * Try to obtain a light lock on the table, to ensure it doesn't
-			 * go away while we collect the list.  If we cannot, just
-			 * disregard the table.  Be sure to release this if we ultimately
-			 * decide not to process the table!
-			 */
-			if (!ConditionalLockRelationOid(class->oid, AccessShareLock))
-				continue;
-
-			/* Verify that the table still exists */
-			if (!SearchSysCacheExists1(RELOID, ObjectIdGetDatum(class->oid)))
-			{
-				UnlockRelationOid(class->oid, AccessShareLock);
-				continue;
-			}
-
 			/* Can only process plain tables and matviews */
 			if (class->relkind != RELKIND_RELATION &&
 				class->relkind != RELKIND_MATVIEW)
-			{
-				UnlockRelationOid(class->oid, AccessShareLock);
 				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()))
-			{
-				UnlockRelationOid(class->oid, AccessShareLock);
 				continue;
-			}
 
 			/* Use a permanent memory context for the result list */
 			oldcxt = MemoryContextSwitchTo(permcxt);
@@ -2279,7 +2237,7 @@ get_tables_to_repack(RepackCommand cmd, bool usingindex, MemoryContext permcxt)
 	}
 
 	table_endscan(scan);
-	relation_close(catalog, AccessShareLock);
+	table_close(catalog, AccessShareLock);
 
 	return rtcs;
 }
@@ -2357,15 +2315,28 @@ get_tables_to_repack_partitioned(RepackCommand cmd, Oid relid,
 static bool
 repack_is_permitted_for_relation(RepackCommand cmd, Oid relid, Oid userid)
 {
+	bool		is_missing = false;
+
 	Assert(cmd == REPACK_COMMAND_CLUSTER || cmd == REPACK_COMMAND_REPACK);
 
-	if (pg_class_aclcheck(relid, userid, ACL_MAINTAIN) == ACLCHECK_OK)
+	if (pg_class_aclcheck_ext(relid, userid, ACL_MAINTAIN, &is_missing) == ACLCHECK_OK)
 		return true;
 
-	ereport(WARNING,
-			errmsg("permission denied to execute %s on \"%s\", skipping it",
-				   RepackCommandAsString(cmd),
-				   get_rel_name(relid)));
+	/* Report a warning if the relation still exists. */
+	if (!is_missing)
+	{
+		char	   *relname;
+
+		relname = get_rel_name(relid);
+		if (relname != NULL)
+		{
+			ereport(WARNING,
+					errmsg("permission denied to execute %s on \"%s\", skipping it",
+						   RepackCommandAsString(cmd), relname));
+
+			pfree(relname);
+		}
+	}
 
 	return false;
 }
-- 
2.47.3


--op6mnexl7cn72cto
Content-Type: text/x-diff; charset=utf-8
Content-Disposition: attachment; filename=v2-0002-fixups.patch

From 12395a8ba804799dedc0e58ceec60bc0822a3a49 Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?=C3=81lvaro=20Herrera?= <alvherre@kurilemu.de>
Date: Tue, 7 Jul 2026 18:35:28 +0200
Subject: [PATCH v2 2/2] fixups

---
 src/backend/commands/repack.c | 54 ++++++++++++++++++++++-------------
 1 file changed, 34 insertions(+), 20 deletions(-)

diff --git a/src/backend/commands/repack.c b/src/backend/commands/repack.c
index 2879c8af574..fcc401ccdb9 100644
--- a/src/backend/commands/repack.c
+++ b/src/backend/commands/repack.c
@@ -2152,6 +2152,10 @@ get_tables_to_repack(RepackCommand cmd, bool usingindex, MemoryContext permcxt)
 
 		/*
 		 * For USING INDEX, scan pg_index to find those with indisclustered.
+		 *
+		 * Note we don't obtain lock of any kind on the index, which means the
+		 * index or its owning table could be gone or change at any point.  We
+		 * have to be extra careful when examining catalog state for them.
 		 */
 		catalog = table_open(IndexRelationId, AccessShareLock);
 		ScanKeyInit(&entry,
@@ -2169,7 +2173,7 @@ get_tables_to_repack(RepackCommand cmd, bool usingindex, MemoryContext permcxt)
 
 			index = (Form_pg_index) GETSTRUCT(tuple);
 
-			classtup = SearchSysCache1(RELOID, ObjectIdGetDatum(index->indrelid));
+			classtup = SearchSysCacheCopy1(RELOID, ObjectIdGetDatum(index->indrelid));
 			if (!HeapTupleIsValid(classtup))
 				continue;
 			classForm = (Form_pg_class) GETSTRUCT(classtup);
@@ -2178,11 +2182,11 @@ get_tables_to_repack(RepackCommand cmd, bool usingindex, MemoryContext permcxt)
 			if (classForm->relpersistence == RELPERSISTENCE_TEMP &&
 				!isTempOrTempToastNamespace(classForm->relnamespace))
 			{
-				ReleaseSysCache(classtup);
+				heap_freetuple(classtup);
 				continue;
 			}
 
-			ReleaseSysCache(classtup);
+			heap_freetuple(classtup);
 
 			/* noisily skip rels which the user can't process */
 			if (!repack_is_permitted_for_relation(cmd, index->indrelid,
@@ -2274,7 +2278,9 @@ get_tables_to_repack_partitioned(RepackCommand cmd, Oid relid,
 			if (get_rel_relkind(child_oid) != RELKIND_INDEX)
 				continue;
 
-			table_oid = IndexGetRelation(child_oid, false);
+			table_oid = IndexGetRelation(child_oid, true);
+			if (!OidIsValid(table_oid))
+				continue;
 			index_oid = child_oid;
 		}
 		else
@@ -2309,33 +2315,41 @@ get_tables_to_repack_partitioned(RepackCommand cmd, Oid relid,
 
 
 /*
- * Return whether userid has privileges to REPACK relid.  If not, this
- * function emits a WARNING.
+ * Return whether userid has privileges to execute REPACK on relid.
+ *
+ * Caller may not have a lock on the relation, so it could have been
+ * dropped concurrently.  In that case, silently return false.
+ *
+ * If the relation does exist but the user doesn't have the required
+ * privs, emit a WARNING and return false.  Otherwise, return true.
  */
 static bool
 repack_is_permitted_for_relation(RepackCommand cmd, Oid relid, Oid userid)
 {
 	bool		is_missing = false;
+	AclResult	result;
+	char	   *relname;
 
 	Assert(cmd == REPACK_COMMAND_CLUSTER || cmd == REPACK_COMMAND_REPACK);
 
-	if (pg_class_aclcheck_ext(relid, userid, ACL_MAINTAIN, &is_missing) == ACLCHECK_OK)
+	result = pg_class_aclcheck_ext(relid, userid, ACL_MAINTAIN, &is_missing);
+	if (is_missing)
+		return false;
+
+	if (result == ACLCHECK_OK)
 		return true;
 
-	/* Report a warning if the relation still exists. */
-	if (!is_missing)
+	/*
+	 * The relation can also be dropped after we tested its ACL and before we
+	 * read its relname, so be careful.
+	 */
+	relname = get_rel_name(relid);
+	if (relname != NULL)
 	{
-		char	   *relname;
-
-		relname = get_rel_name(relid);
-		if (relname != NULL)
-		{
-			ereport(WARNING,
-					errmsg("permission denied to execute %s on \"%s\", skipping it",
-						   RepackCommandAsString(cmd), relname));
-
-			pfree(relname);
-		}
+		ereport(WARNING,
+				errmsg("permission denied to execute %s on \"%s\", skipping it",
+					   RepackCommandAsString(cmd), relname));
+		pfree(relname);
 	}
 
 	return false;
-- 
2.47.3


--op6mnexl7cn72cto--