MIME-Version: 1.0
References: 
 <CACLQnSmysDOQEn9HJ+AEXMxQ0KvNg=T3+Emz65jkVSqxXaNcPQ@mail.gmail.com>
 <CA+Tgmoavbii-G1uN9tB=gDFJqwp2W-mVM80NifURWb+mDmFc3g@mail.gmail.com>
In-Reply-To: 
 <CA+Tgmoavbii-G1uN9tB=gDFJqwp2W-mVM80NifURWb+mDmFc3g@mail.gmail.com>
From: Zach Manifold <zachlweaver00@gmail.com>
Date: Fri, 24 Apr 2026 17:41:50 -0700
Message-ID: 
 <CACLQnSnFJ9Eq3D_RZUhuTSCxT4jj39wQeRLW3EX2SmW9=EKYYA@mail.gmail.com>
Subject: Re: Proposal: DROP ROLE ... REASSIGN OWNED TO ...
To: Robert Haas <robertmhaas@gmail.com>
Cc: pgsql-hackers@postgresql.org
Content-Type: multipart/alternative; boundary="00000000000035722806503e2638"
Archived-At: 
 <https://www.postgresql.org/message-id/CACLQnSnFJ9Eq3D_RZUhuTSCxT4jj39wQeRLW3EX2SmW9%3DEKYYA%40mail.gmail.com>
Precedence: bulk

--00000000000035722806503e2638
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

> 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.

That's a fair point. I agree the value add is fairly minimal, since my
intention was a tiny quality of life enhancement, but to your point
if there are different variations of doing the same thing it can either
end up being confusing or not utilized.

Thanks for reviewing.

- Zach Weaver

On Fri, Apr 24, 2026 at 5:29=E2=80=AFPM Robert Haas <robertmhaas@gmail.com>=
 wrote:

> On Thu, Apr 23, 2026 at 2:59=E2=80=AFAM Zach Manifold <zachlweaver00@gmai=
l.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.
>
> --
> Robert Haas
> EDB: http://www.enterprisedb.com
>

--00000000000035722806503e2638
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">&gt; If we add a bunch of stuff like this, it will<br styl=
e=3D"color:initial;text-decoration-color:initial">&gt; take work to maintai=
n, but most users won&#39;t be able to remember all<br style=3D"color:initi=
al;text-decoration-color:initial">&gt; the variations that exist at the mom=
ent when they might benefit from<br style=3D"color:initial;text-decoration-=
color:initial">&gt; them.<div><br></div><div>That&#39;s a fair point. I agr=
ee the value add is fairly minimal, since my</div><div>intention was a tiny=
 quality of life enhancement, but to your point</div><div>if there are diff=
erent variations of doing the same thing it can either</div><div>end up bei=
ng confusing or not utilized.</div><div><br></div><div>Thanks for reviewing=
.</div><div><br></div><div>- Zach Weaver</div></div><br><div class=3D"gmail=
_quote gmail_quote_container"><div dir=3D"ltr" class=3D"gmail_attr">On Fri,=
 Apr 24, 2026 at 5:29=E2=80=AFPM Robert Haas &lt;<a href=3D"mailto:robertmh=
aas@gmail.com">robertmhaas@gmail.com</a>&gt; wrote:<br></div><blockquote cl=
ass=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid=
 rgb(204,204,204);padding-left:1ex">On Thu, Apr 23, 2026 at 2:59=E2=80=AFAM=
 Zach Manifold &lt;<a href=3D"mailto:zachlweaver00@gmail.com" target=3D"_bl=
ank">zachlweaver00@gmail.com</a>&gt; wrote:<br>
&gt; My one concern is the possible failure mode - is it possible for<br>
&gt; the reassignment to work but the role to fail to drop? Is this<br>
&gt; preventable? I&#39;m not sure how to &quot;cleanly&quot; approach this=
 type<br>
&gt; of safety where I can assure that both of these operations<br>
&gt; must succeed rather than reassigning ownership and failing<br>
&gt; to drop a role.<br>
<br>
There&#39;s no problem of this type -- the whole statement would execute<br=
>
as a single transaction, and any failure would role the whole thing<br>
back.<br>
<br>
But I&#39;m a little bit skeptical of the underlying proposal for related<b=
r>
reasons. This doesn&#39;t really let you do anything that you can&#39;t eas=
ily<br>
do already:<br>
<br>
rhaas=3D# create role joe;<br>
CREATE ROLE<br>
rhaas=3D# begin;<br>
BEGIN<br>
rhaas=3D*# reassign owned by joe to fred;<br>
REASSIGN OWNED<br>
rhaas=3D*# drop role joe;<br>
DROP ROLE<br>
rhaas=3D*# commit;<br>
COMMIT<br>
<br>
This would fail if the user to be dropped owned objects in another<br>
database, but your hypothetical version of DROP ROLE would have that<br>
issue, too. Even if you couldn&#39;t wrap both commands in a single<br>
transaction -- we have some DDL commands that are like that -- running<br>
them one after another wouldn&#39;t lose much. So I&#39;m just not sure I<b=
r>
really see the point. If we add a bunch of stuff like this, it will<br>
take work to maintain, but most users won&#39;t be able to remember all<br>
the variations that exist at the moment when they might benefit from<br>
them. We might also end up with a patchwork where some things are<br>
supported and seemingly related things are not supported, just because<br>
of the idiosyncrasies of what got implemented and what didn&#39;t. I&#39;m =
not<br>
saying nobody would ever benefit from something like this -- probably<br>
some people would -- but I don&#39;t know that there would be all that<br>
many of them or that the benefit would be all that much.<br>
<br>
-- <br>
Robert Haas<br>
EDB: <a href=3D"http://www.enterprisedb.com" rel=3D"noreferrer" target=3D"_=
blank">http://www.enterprisedb.com</a><br>
</blockquote></div>

--00000000000035722806503e2638--