Re: DROP OWNED BY fails with #53200: ERROR: out of shared memory - Mailing list pgsql-general

Dominique:

On Tue, 11 Jan 2022 at 11:57, Dominique Devienne <ddevienne@gmail.com> wrote:
> On Tue, Jan 11, 2022 at 11:05 AM Francisco Olarte <folarte@peoplecall.com> wrote:
>> 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.

It certainly does, yours is a extreme use case but should have a way
to make it work. From what I've understood it is just a problem of
hitting a limit, but it should work even if the solution is just "put
some more memory, increase that parameter".

Re: dropping a DB, I do not think you are going to hit this limit
there, as iy is a fundamentally different operation, someone more
experienced may page in, but I think it is a matter of checking nobody
uses it, updating a few rows in global catalogs and deleting all the
files. I've been out of DB programming for some years, but I remember
it was very fast. But know you say it, it may need tracking, but I
think depending object is only needed when dropping the role.


>> 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".
...
>> 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.

I've never used it for production ( I can see its usefulness for the
hosting people which have a lot of customers or sites which the same
database, but has never been my case ). For testing, at least for me,
create with template was just like a very fast sql script for
creation, or a very fast restore. They were normally databases with
little data ( typically only the control tables populated 9.

>> 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?

Well, they were not too "unity", creating the DB for a real unit ( of
behaviour ) test was too expensive, but I think you have that solved.

They were a batch of tests run either manually via single script (
pick a name, copy the database, run the tests in it, report, drop the
database  ( unless a flag was given, for foresincs ) ) or the same
script from the CI server.

> 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.

And you use this to create the template, via CI if you want.

> Plus ROLEs are CLUSTER-wide, so the DB template does nothing to help with SCHEMA-associated roles. --DD

You may avoid the need to create and drop roles. If the test dbs are
isolated, you should not need to drop the roles when they are "live"
in the testing setup. You just need to script role creation ( globally
), plus template creation after it, then create with template and drop
for tests, and when template needs changing you either script the
update directly ( no need to drop roles ) or drop it, drop the roles (
which now have nothing depending on them ) and recreate from start.
More knowledge of exact procedures is needed, but as it seems you have
everything scripted you may be able to test that, and I remember
copying databases by create template was very fast. Of course we did
not have much roles or schemas, but it seemed a natural way to go for
testing in what you described, and also having different databases for
each test runs can give you better test isolation, specially in your
case of concurrent testing. Just seemed the proper tool for the job,
and I thought you may not be familiar with it.

Francisco Olarte.