Re: DROP OWNED BY fails with #53200: ERROR: out of shared memory

Dominique:

Not going to enter into the lock situation but...

On Tue, 11 Jan 2022 at 10:24, Dominique Devienne <ddevienne(at)gmail(dot)com> wrote:
...
> I need for unit testing purposes to be able to support multiple (2+N schemas) "instances".
> Each instance (of 2+N schemas) is owned by a separate ROLE, created for that express purpose.
> I designed / coded it to be able to have several "instances" per DB, that come and go for unit testing purpose,
> and they will come and go concurrently (when CI kicks in, on several platforms/configurations in parallel).
> And I thought DROP OWNED BY was going to be convenient (fewer client-server round-trips, perfectly models the *intent*).
> But obviously given the limitations I'm discovering, that's not the case.
>
> In production, there will typically be a single "instance" per DB.
>
> So, should I redesign for each instance to be in its own DB? And instead of just creating schemas on the fly when running tests, creating DBs on the fly?
> That means I'd could then DROP the whole DB (I wish for DB-specific ROLEs BTW...). Does that buy me anything? Does that help with locks-per-tx at all?
> I'm happy to do that, if necessary. But is using a dedicated DB per 2+N schemas "instance" the right approach?

I'm not sure if you are going to hit other limitations, but I've
normally done tests with the "template database" approach ( using
create database template=, dropping the DB at the end ). It is fast,
it is simple, it is easy. Have you tried that? seems much
easier/faster than building and dropping all this
schemas/roles,specially for testing.

Francisco Olarte.