From: Amit Kapila <akapila@postgresql.org>
To: pgsql-committers@lists.postgresql.org
Subject: pgsql: Fix self-deadlock during DROP SUBSCRIPTION.
Date: Tue, 19 Aug 2025 06:00:24 +0000
Message-ID: <E1uoFOS-000b7P-0F@gemulon.postgresql.org> (raw)
Fix self-deadlock during DROP SUBSCRIPTION.
The DROP SUBSCRIPTION command performs several operations: it stops the
subscription workers, removes subscription-related entries from system
catalogs, and deletes the replication slot on the publisher server.
Previously, this command acquired an AccessExclusiveLock on
pg_subscription before initiating these steps.
However, while holding this lock, the command attempts to connect to the
publisher to remove the replication slot. In cases where the connection is
made to a newly created database on the same server as subscriber, the
cache-building process during connection tries to acquire an
AccessShareLock on pg_subscription, resulting in a self-deadlock.
To resolve this issue, we reduce the lock level on pg_subscription during
DROP SUBSCRIPTION from AccessExclusiveLock to RowExclusiveLock. Earlier,
the higher lock level was used to prevent the launcher from starting a new
worker during the drop operation, as a restarted worker could become
orphaned.
Now, instead of relying on a strict lock, we acquire an AccessShareLock on
the specific subscription being dropped and re-validate its existence
after acquiring the lock. If the subscription is no longer valid, the
worker exits gracefully. This approach avoids the deadlock while still
ensuring that orphan workers are not created.
Reported-by: Alexander Lakhin <exclusion@gmail.com>
Author: Dilip Kumar <dilipbalaut@gmail.com>
Reviewed-by: vignesh C <vignesh21@gmail.com>
Reviewed-by: Hayato Kuroda <kuroda.hayato@fujitsu.com>
Reviewed-by: Amit Kapila <amit.kapila16@gmail.com>
Backpatch-through: 13
Discussion: https://postgr.es/m/18988-7312c868be2d467f@postgresql.org
Branch
------
REL_15_STABLE
Details
-------
https://git.postgresql.org/pg/commitdiff/e41137155869da54118cfe80a1300a73bde58d61
Modified Files
--------------
src/backend/commands/subscriptioncmds.c | 8 +++++---
src/backend/replication/logical/worker.c | 7 +++++++
src/test/subscription/t/100_bugs.pl | 30 ++++++++++++++++++++++++++++++
3 files changed, 42 insertions(+), 3 deletions(-)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-committers@postgresql.org
Cc: akapila@postgresql.org, pgsql-committers@lists.postgresql.org
Subject: Re: pgsql: Fix self-deadlock during DROP SUBSCRIPTION.
In-Reply-To: <E1uoFOS-000b7P-0F@gemulon.postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox