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