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. -- With Regards, Amit Kapila.
