Re: REASSIGN OWNED vs. relisshared dep on !relisshared

On Mon, Jul 13, 2026 at 5:04 PM Dilip Kumar <dilipbalaut(at)gmail(dot)com> wrote:
>
> 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.
>

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