On Thu, Apr 23, 2026 at 2:59 AM Zach Manifold <[email protected]> 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=# create role joe; CREATE ROLE rhaas=# begin; BEGIN rhaas=*# reassign owned by joe to fred; REASSIGN OWNED rhaas=*# drop role joe; DROP ROLE rhaas=*# 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
