Re: BUG #18988: DROP SUBSCRIPTION locks not-yet-accessed database

Amit Kapila <amit.kapila16@gmail.com>

From: Amit Kapila <amit.kapila16@gmail.com>

To: Dilip Kumar <dilipbalaut@gmail.com>

Cc: vignesh C <vignesh21@gmail.com>, Álvaro Herrera <alvherre@kurilemu.de>, "Hayato Kuroda (Fujitsu)" <kuroda.hayato@fujitsu.com>, "exclusion@gmail.com" <exclusion@gmail.com>, "pgsql-bugs@lists.postgresql.org" <pgsql-bugs@lists.postgresql.org>

Date: 2025-08-14T06:09:04Z

Lists: pgsql-bugs

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

Fix self-deadlock during DROP SUBSCRIPTION.
- aa21e49225a1 19 (unreleased) landed
- a5d4c04150d4 18.0 landed
- 288a817bcb04 17.7 landed
- 7ece7612906e 16.11 landed
- e41137155869 15.15 landed
- ef77502746fe 14.20 landed
- 0a98f24a65cd 13.23 landed

Attachments

v3_amit.1.patch.txt (text/plain)

On Wed, Aug 13, 2025 at 3:00 PM Dilip Kumar <dilipbalaut@gmail.com> wrote:
>
> I have prepared back branch patches.
>

@@ -1802,11 +1802,7 @@ DropSubscription(DropSubscriptionStmt *stmt,
bool isTopLevel)
...
- /*
- * 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);

We need to acquire RowExclusiveLock here as we are deleting the
subscription row during this operation. See docs [1] and
AlterSubscription() code for reference. Attached is a top-up patch to
fix this for HEAD. Additionally, I have edited a few comments. If you
agree with these changes then please prepare backbranch patches
accordingly.

[1] - https://www.postgresql.org/docs/devel/explicit-locking.html
-- 
With Regards,
Amit Kapila.