On 2025-Dec-30, Kirill Reshke wrote: > Yep, they are shared, but subscriptions are created in database context...
True. > I want to delete user, which has subscription s1 in db1 and subscription s2 > in db2. I want to REASSIGN all object from db1 to db1 owner and same for > db2. > I will do REASSIGN OWNED BY ... to <db owner> in each of these database, > and then drop user. I excpect that sql I do in db1 does not affect objects > in db2... Am I wrong in this assumption? Like, subscriptions have knowledge > of which database they are belong... maybe we should use this knowledge Yeah, I can see that there is merit to this idea, and I think it's not very difficult to implement -- POC attached. Does this solve your issue? -- Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
>From a0e96539f85092997d195e44b4b5508123ad98e3 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?=C3=81lvaro=20Herrera?= <[email protected]> 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+xt8...@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
