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

On Tue, Jan 11, 2022 at 11:05 AM Francisco Olarte <folarte(at)peoplecall(dot)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 <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?

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