From: =?UTF-8?q?=C3=81lvaro=20Herrera?= <alvherre@kurilemu.de>
Date: Thu, 1 Jan 2026 13:47:51 -0300
Subject: [PATCH] REASSIGN OWNED: ignore subscriptions in other databases

XXX do we also need DROP OWNED to work this way? Probably not.

Discussion: https://postgr.es/m/CALdSSPhjONb+EftRD=J1pqajkB+pjT0=tbMJs16C6q9+xT8NNg@mail.gmail.com
---
 src/backend/catalog/pg_shdepend.c   |  9 +++++++++
 src/backend/utils/cache/lsyscache.c | 22 ++++++++++++++++++++++
 src/include/utils/lsyscache.h       |  1 +
 3 files changed, 32 insertions(+)

diff --git a/src/backend/catalog/pg_shdepend.c b/src/backend/catalog/pg_shdepend.c
index fe4e4536676..0e6fe843b35 100644
--- a/src/backend/catalog/pg_shdepend.c
+++ b/src/backend/catalog/pg_shdepend.c
@@ -1592,6 +1592,15 @@ shdepReassignOwned(List *roleids, Oid newrole)
 				sdepForm->dbid != InvalidOid)
 				continue;
 
+			/*
+			 * Subscriptions are linked to specific databases, even though
+			 * they are nominally shared objects.  Skip those that aren't
+			 * in this database.
+			 */
+			if (sdepForm->classid == SubscriptionRelationId &&
+				get_subscription_database(sdepForm->objid) != MyDatabaseId)
+				continue;
+
 			/*
 			 * The various DDL routines called here tend to leak memory in
 			 * CurrentMemoryContext.  That's not a problem when they're only
diff --git a/src/backend/utils/cache/lsyscache.c b/src/backend/utils/cache/lsyscache.c
index a14fef3ba9e..ac5411314c3 100644
--- a/src/backend/utils/cache/lsyscache.c
+++ b/src/backend/utils/cache/lsyscache.c
@@ -3864,3 +3864,25 @@ get_subscription_name(Oid subid, bool missing_ok)
 
 	return subname;
 }
+
+/*
+ * Return the OID of the database the given subscription is in.
+ */
+Oid
+get_subscription_database(Oid subid)
+{
+	HeapTuple	tup;
+	Form_pg_subscription	subform;
+	Oid			subdbid;
+
+	tup = SearchSysCache1(SUBSCRIPTIONOID, ObjectIdGetDatum(subid));
+	if (!HeapTupleIsValid(tup))
+		elog(ERROR, "cache lookup failed for subscription %u", subid);
+
+	subform = (Form_pg_subscription) GETSTRUCT(tup);
+	subdbid = subform->subdbid;
+
+	ReleaseSysCache(tup);
+
+	return subdbid;
+}
diff --git a/src/include/utils/lsyscache.h b/src/include/utils/lsyscache.h
index 50fb149e9ac..e0a342d7a16 100644
--- a/src/include/utils/lsyscache.h
+++ b/src/include/utils/lsyscache.h
@@ -210,6 +210,7 @@ extern Oid	get_publication_oid(const char *pubname, bool missing_ok);
 extern char *get_publication_name(Oid pubid, bool missing_ok);
 extern Oid	get_subscription_oid(const char *subname, bool missing_ok);
 extern char *get_subscription_name(Oid subid, bool missing_ok);
+extern Oid get_subscription_database(Oid subid);
 
 #define type_is_array(typid)  (get_element_type(typid) != InvalidOid)
 /* type_is_array_domain accepts both plain arrays and domains over arrays */
-- 
2.47.3


--cjf25q7rpiw4yq5z--