On Thu, 2022-07-07 at 16:36 +0200, Dominique Devienne wrote: > > > So my goal is to delete all those "db specific" ROLEs, then the DB > > > with all its schemas. > > > Which implies REVOKE'ing grants on all those "db specific" ROLEs first. > > > > You should not really have to revoke those manually. > > The normal process for that is to use DROP OWNED BY. > > Except we already went through that, that DROP OWNED BY acquires too many locks. > Increasing max_locks_per_transaction when it fails is just not an option IMHO. > One user had to raise it to 32K for his particular DB, which is not > even that large. > > Or are you saying setting it to 1M or 1B is "safe", and should be > required setup for users? If you want to do this on a routine basis, you are doing something wrong. Never grant a user privileges if the user could be removed. Use groups in that case. For a one-time cleanup operation, increasing "max_locks_per_transaction" and restarting is painful, but not impossible. See it as down time. > Is revoking privileges taking locks? Yes. > Is dropping a DB taking locks? Not a lot. That should never be a problem. > If neither are, then I can work around the limitations of DROP OWNED BY. > > So will the community help me figure this out? > > BTW, I'm also hoping revoking privs, and dropping roles and dbs will > be faster than DROP OWNED BY. > That DB took 30min to 1h to get rid of, via DROP OWNED BY, which seems > to long to delete a bunch of files, no? As I wrote, avoid getting there in the first place. Yours, Laurenz Albe
