On Sat, Jul 11, 2026 at 9:31 AM Amit Kapila <[email protected]> wrote: > > On Sat, Jul 11, 2026 at 3:41 AM Jeff Davis <[email protected]> wrote: > > > > On Fri, 2026-07-10 at 12:25 -0700, Noah Misch wrote: > > > Perhaps one could fix this by having REASSIGN OWNED process only > > > pg_subscription rows where subdbid is the current database. > > > > That was my first thought, as well. I will look into it. > > > > > I'm nervous that we'll have other reasons to regret letting a shared > > > object > > > depend on a non-shared object, but I've not come up with anything > > > else > > > concrete. > > > > That's a reasonable concern. We discussed it here: > > > > https://www.postgresql.org/message-id/CAA4eK1LyHvRoNzZPpPQqo7a%3D5Wov8F-7%2BKDduy-9ymcRm%3DBatg%40mail.gmail.com > > > > Though the root of the problem might be that pg_subscription is shared > > in the first place. > > > > Yeah, but actually a subscription is really tied to a specific > database; the catalog is shared only because the replication launcher > must see all subscriptions to start their workers, as the header > comment notes: "Technically, the subscriptions live inside the > database, so a shared catalog seems weird, but the replication > launcher process needs to access all of them to be able to start the > workers, so we have to put them in a shared, nailed catalog." > > Note that the subscription commands already behave per-database in > code: CREATE SUBSCRIPTION stores subdbid = MyDatabaseId, and > ALTER/DROP SUBSCRIPTION and ALTER SUBSCRIPTION ... OWNER all look the > subscription up by (MyDatabaseId, subname) via the unique index on > (subdbid, subname), so they only ever act on subscriptions in the > current database. > > Given that, and that REASSIGN OWNED is already documented as a > per-database operation "Because REASSIGN OWNED does not affect objects > within other databases, it is usually necessary to execute this > command in each database that contains objects owned by a role that is > to be removed.", processing only the pg_subscription rows whose > subdbid is the current database sounds like a fix (which is consistent > with other commands operating on a subscription) for this as mentioned > by both you and Noah.
PFA a patch for skipping subscriptions belonging to different databases. -- Regards, Dilip Kumar Google
v1-0001-Do-not-reassign-subscriptions-belonging-to-other-.patch
Description: Binary data
