From: Bertrand Drouvot Date: Fri, 3 Jul 2026 12:28:42 +0000 Subject: [PATCH v4 1/4] Re-read subscription state after lock in AlterSubscription AlterSubscription() reads the subscription's catalog state via GetSubscription() before acquiring AccessExclusiveLock on the subscription object. A concurrent session that commits a DROP or ALTER between the read and the lock acquisition leaves the other session acting with stale information once it unblocks. Fix by moving the GetSubscription() call, the password_required privilege check, and the local variable assignments to after LockSharedObject(), with a re-read of the subscription tuple to ensure we operate on current catalog state. Remark: The ownership check is intentionally not re-done after the lock because AlterSubscriptionOwner() does not take AccessExclusiveLock on the subscription object: it only takes RowExclusiveLock on the pg_subscription catalog table. This means ownership can change regardless of our lock, making a re-check after lock acquisition pointless. The existing "tuple concurrently updated" error from CatalogTupleUpdate() already provides a protection if ownership changes concurrently. Author: Bertrand Drouvot Reviewed-by: Dilip Kumar Reviewed-by: Hayato Kuroda (Fujitsu) Reviewed-by: Zhijie Hou Reviewed-by: Amit Kapila Discussion: https://postgr.es/m/akZUpiDa1UfmzYxL%40bdtpg --- src/backend/commands/subscriptioncmds.c | 30 +++++++++++++++++++------ 1 file changed, 23 insertions(+), 7 deletions(-) 100.0% src/backend/commands/ diff --git a/src/backend/commands/subscriptioncmds.c b/src/backend/commands/subscriptioncmds.c index 4292e7fb8f4..517d46f47f9 100644 --- a/src/backend/commands/subscriptioncmds.c +++ b/src/backend/commands/subscriptioncmds.c @@ -1686,6 +1686,25 @@ AlterSubscription(ParseState *pstate, AlterSubscriptionStmt *stmt, orig_conninfo_needed = false; } + heap_freetuple(tup); + + /* Lock the subscription so nobody else can do anything with it. */ + LockSharedObject(SubscriptionRelationId, subid, 0, AccessExclusiveLock); + + /* + * Re-read the subscription tuple after acquiring the lock. A concurrent + * DROP or ALTER may have committed before we acquired the lock. + */ + tup = SearchSysCacheCopy1(SUBSCRIPTIONOID, ObjectIdGetDatum(subid)); + + if (!HeapTupleIsValid(tup)) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("subscription \"%s\" does not exist", + stmt->subname))); + + form = (Form_pg_subscription) GETSTRUCT(tup); + /* * Skip ACL checks on the subscription's foreign server, if any. If * changing the server (or replacing it with a raw connection), then the @@ -1695,11 +1714,6 @@ AlterSubscription(ParseState *pstate, AlterSubscriptionStmt *stmt, */ sub = GetSubscription(subid, false, orig_conninfo_needed, false); - retain_dead_tuples = sub->retaindeadtuples; - origin = sub->origin; - max_retention = sub->maxretention; - retention_active = sub->retentionactive; - /* * Don't allow non-superuser modification of a subscription with * password_required=false. @@ -1710,8 +1724,10 @@ AlterSubscription(ParseState *pstate, AlterSubscriptionStmt *stmt, errmsg("password_required=false is superuser-only"), errhint("Subscriptions with the password_required option set to false may only be created or modified by the superuser."))); - /* Lock the subscription so nobody else can do anything with it. */ - LockSharedObject(SubscriptionRelationId, subid, 0, AccessExclusiveLock); + retain_dead_tuples = sub->retaindeadtuples; + origin = sub->origin; + max_retention = sub->maxretention; + retention_active = sub->retentionactive; /* Form a new tuple. */ memset(values, 0, sizeof(values)); -- 2.34.1 --b+bwjVhifbzgesmw Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v4-0002-Re-read-subscription-state-after-lock-in-DropSubs.patch"