Re: REASSIGN OWNED BY alters objects in other database.

On Thu, 1 Jan 2026 at 21:51, Álvaro Herrera <alvherre@kurilemu.de> wrote:
>
> On 2025-Dec-30, Kirill Reshke wrote:
>
> > Yep, they are shared, but subscriptions are created in database context...
>
> True.
>
> > I want to delete user, which has subscription s1 in db1 and subscription s2
> > in db2. I want to REASSIGN all object from db1 to db1 owner and same for
> > db2.
> > I will do REASSIGN OWNED BY ... to <db owner> in each of these database,
> > and then drop user. I excpect that sql I do in db1 does not affect objects
> > in db2... Am I wrong in this assumption? Like, subscriptions have knowledge
> > of which database they are belong... maybe we should use this knowledge
>
> Yeah, I can see that there is merit to this idea, and I think it's not
> very difficult to implement -- POC attached.  Does this solve your
> issue?
>
> --
> Álvaro Herrera               48°01'N 7°57'E  —  https://www.EnterpriseDB.com/

Hi!
Thank you for your interest in this thread and thank you for your patch.
Yes, this patch achieves behaviour I want from REASSIGN OWNED. This is
something I had in mind when I started this thread.
My internal resistance to post a patch like yours was because of the following:

I can see that REASSIGN owned will behave the way I want if the `dbid`
column in pg_shdepend would be non-zero for record with deptype = 'o'
(owner).
This would automatically drop only subscriptions from the current
database. But we create this record with dbid = 0 because of
shdepAddDependency, which thinks that classId is a shared relation
then dependency should have dbid = 0. I wonder if this is correct (for
subscriptions case).

If it is, then your patch WFM LGTM.

--
Best regards,
Kirill Reshke