Re: REASSIGN OWNED vs. relisshared dep on !relisshared

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.

--
With Regards,
Amit Kapila.