Re: REASSIGN OWNED vs. relisshared dep on !relisshared

On Sat, Jul 11, 2026 at 9:31 AM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>
> On Sat, Jul 11, 2026 at 3:41 AM Jeff Davis <pgsql(at)j-davis(dot)com> 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