Dominique:
Not going to enter into the lock situation but...
OK :). But it does matter. those locks. Especially if DROP'ing a DB (as you suggest) requires just the same.
On Tue, 11 Jan 2022 at 10:24, Dominique Devienne <ddevienne@gmail.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?
No, I haven't. I did see that feature, in the doc, and wondered about it for Production, but not for testing.
seems much easier/faster than building and dropping all this schemas/roles,specially for testing.
Good to here. But when you write "I've done tests", do you mean manual tests?
Or automated unit-tests that create DBs (from a template) on-the-fly and DROP them?
Concurrently from different CI agents?
The reason I didn't consider DB templates for unit-testing, is that the schemas are changing often.
And creating the schemas is all automated in code already.
Plus ROLEs are CLUSTER-wide, so the DB template does nothing to help with SCHEMA-associated roles. --DD