MIME-Version: 1.0
References: 
 <CACLQnSmysDOQEn9HJ+AEXMxQ0KvNg=T3+Emz65jkVSqxXaNcPQ@mail.gmail.com>
In-Reply-To: 
 <CACLQnSmysDOQEn9HJ+AEXMxQ0KvNg=T3+Emz65jkVSqxXaNcPQ@mail.gmail.com>
From: Robert Haas <robertmhaas@gmail.com>
Date: Fri, 24 Apr 2026 20:29:43 -0400
Message-ID: 
 <CA+Tgmoavbii-G1uN9tB=gDFJqwp2W-mVM80NifURWb+mDmFc3g@mail.gmail.com>
Subject: Re: Proposal: DROP ROLE ... REASSIGN OWNED TO ...
To: Zach Manifold <zachlweaver00@gmail.com>
Cc: pgsql-hackers@postgresql.org
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CA%2BTgmoavbii-G1uN9tB%3DgDFJqwp2W-mVM80NifURWb%2BmDmFc3g%40mail.gmail.com>
Precedence: bulk

On Thu, Apr 23, 2026 at 2:59=E2=80=AFAM Zach Manifold <zachlweaver00@gmail.=
com> wrote:
> My one concern is the possible failure mode - is it possible for
> the reassignment to work but the role to fail to drop? Is this
> preventable? I'm not sure how to "cleanly" approach this type
> of safety where I can assure that both of these operations
> must succeed rather than reassigning ownership and failing
> to drop a role.

There's no problem of this type -- the whole statement would execute
as a single transaction, and any failure would role the whole thing
back.

But I'm a little bit skeptical of the underlying proposal for related
reasons. This doesn't really let you do anything that you can't easily
do already:

rhaas=3D# create role joe;
CREATE ROLE
rhaas=3D# begin;
BEGIN
rhaas=3D*# reassign owned by joe to fred;
REASSIGN OWNED
rhaas=3D*# drop role joe;
DROP ROLE
rhaas=3D*# commit;
COMMIT

This would fail if the user to be dropped owned objects in another
database, but your hypothetical version of DROP ROLE would have that
issue, too. Even if you couldn't wrap both commands in a single
transaction -- we have some DDL commands that are like that -- running
them one after another wouldn't lose much. So I'm just not sure I
really see the point. If we add a bunch of stuff like this, it will
take work to maintain, but most users won't be able to remember all
the variations that exist at the moment when they might benefit from
them. We might also end up with a patchwork where some things are
supported and seemingly related things are not supported, just because
of the idiosyncrasies of what got implemented and what didn't. I'm not
saying nobody would ever benefit from something like this -- probably
some people would -- but I don't know that there would be all that
many of them or that the benefit would be all that much.

--=20
Robert Haas
EDB: http://www.enterprisedb.com