On Tue, Jan 11, 2022 at 11:05 AM Francisco Olarte <fola...@peoplecall.com> wrote:
> 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 <ddevie...@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
