v3-0001-Fix-DROP-SUBSCRIPTION-deadlock-with-new-database-V16.patch

From e756358fd903e5b3432d0ec5dfbe8b895bd4ecdf Mon Sep 17 00:00:00 2001
From: Dilip Kumar <dilipkumarb@google.com>
Date: Wed, 13 Aug 2025 07:43:15 +0000
Subject: [PATCH v3] Fix DROP SUBSCRIPTION deadlock with new database creation

DROP SUBSCRIPTION previously acquired an AccessExclusiveLock on the
pg_subscription catalog to prevent the replication launcher from starting
a new worker. However, this caused a deadlock. New database creation also
need to acquire an AccessShareLock on this same catalog during their
initialization phase. This created a lock conflict where DROP SUBSCRIPTION
would block this, while simultaneously waiting for a connection to
complete for dropping the replication slot.

This commit resolves the deadlock by having DROP SUBSCRIPTION acquire a less
restrictive AccessShareLock on the catalog instead.

To address the original concern of orphaned workers, a new check is
implemented.  The replication worker now takes a shared object lock on the
subscription itself.

If a worker starts for a subscription that no longer exists, it immediately detects
this condition and exits. This ensures that no orphan workers are created without
the need for an overly broad AccessExclusiveLock on the system catalog.
---
 src/backend/commands/subscriptioncmds.c  |  6 +-----
 src/backend/replication/logical/worker.c | 17 ++++++++++++++++-
 2 files changed, 17 insertions(+), 6 deletions(-)

diff --git a/src/backend/commands/subscriptioncmds.c b/src/backend/commands/subscriptioncmds.c
index e1260fc0e90..77e6462736c 100644
--- a/src/backend/commands/subscriptioncmds.c
+++ b/src/backend/commands/subscriptioncmds.c
@@ -1490,11 +1490,7 @@ DropSubscription(DropSubscriptionStmt *stmt, bool isTopLevel)
 	List	   *rstates;
 	bool		must_use_password;
 
-	/*
-	 * Lock pg_subscription with AccessExclusiveLock to ensure that the
-	 * launcher doesn't restart new worker during dropping the subscription
-	 */
-	rel = table_open(SubscriptionRelationId, AccessExclusiveLock);
+	rel = table_open(SubscriptionRelationId, AccessShareLock);
 
 	tup = SearchSysCache2(SUBSCRIPTIONNAME, MyDatabaseId,
 						  CStringGetDatum(stmt->subname));
diff --git a/src/backend/replication/logical/worker.c b/src/backend/replication/logical/worker.c
index 4df0a594dc9..df455d65665 100644
--- a/src/backend/replication/logical/worker.c
+++ b/src/backend/replication/logical/worker.c
@@ -4492,6 +4492,12 @@ InitializeApplyWorker(void)
 	StartTransactionCommand();
 	oldctx = MemoryContextSwitchTo(ApplyContext);
 
+	/*
+	 * Lock the subscription to prevent it from being concurrently dropped,
+	 * then re-verify its existence.
+	 */
+	LockSharedObject(SubscriptionRelationId, MyLogicalRepWorker->subid, 0,
+					 AccessShareLock);
 	MySubscription = GetSubscription(MyLogicalRepWorker->subid, true);
 	if (!MySubscription)
 	{
@@ -4502,10 +4508,19 @@ InitializeApplyWorker(void)
 		/* Ensure we remove no-longer-useful entry for worker's start time */
 		if (!am_tablesync_worker() && !am_parallel_apply_worker())
 			ApplyLauncherForgetWorkerStartTime(MyLogicalRepWorker->subid);
+
+		/*
+		 * The shared object lock on subid is automatically released by
+		 * proc_exit(), so no explicit unlock is necessary here.
+		 */
 		proc_exit(0);
 	}
 
-	MySubscriptionValid = true;
+	/*
+	 * The shared object lock on subid will be released at transaction end.
+	 */
+
+	 MySubscriptionValid = true;
 	MemoryContextSwitchTo(oldctx);
 
 	if (!MySubscription->enabled)
-- 
2.51.0.rc0.205.g4a044479a3-goog