Dominique: Not going to enter into the lock situation but... On Tue, 11 Jan 2022 at 10:24, Dominique Devienne <ddevienne@xxxxxxxxx> 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.
