On Mon, Jul 13, 2026 at 5:04 PM Dilip Kumar <[email protected]> wrote: > > 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. >
The fix idea and the patch LGTM. One minor comment in the doc: 'and are reassigned when REASSIGN OWNED is executed in that database.' when --> only when (to make it more clear) thanks Shveta
