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

ddevienne=> select relnamespace::regnamespace::text, count(*) from pg_class where relnamespace::regnamespace::text like '"PNS:%"' group by relnamespace;
              relnamespace              | count
----------------------------------------+-------
 "PNS:ec44cdde757b572a8f367277e67f1e3f" |  1308

...

ddevienne=> select relnamespace::regnamespace::text, relkind, count(*) from pg_class where relnamespace::regnamespace::text like '"PNS:%"' group by relnamespace, relkind;
              relnamespace              | relkind | count
----------------------------------------+---------+-------
 "PNS:ec44cdde757b572a8f367277e67f1e3f" | S       |   229
 "PNS:ec44cdde757b572a8f367277e67f1e3f" | i       |   828
 "PNS:ec44cdde757b572a8f367277e67f1e3f" | r       |   244
 "PNS:ec44cdde757b572a8f367277e67f1e3f" | v       |     7

...

So from what I was told in this thread, 1308 locks need to be taken, just for that one schema.

And my "system" can have N of those (and there can be N systems in a DB).

(the other two "fixed" schemas have way fewer relations, 32 for one).

 

But the point is that there's no limit on how many schema a given ROLE can own.

So you can't pick a value that will always work.So that makes DROP OWNED BY

pretty much useless as too unreliable in my case. Unless I'm missing something?

That's definitely something that's not mentioned in the doc of DROP OWNED BY. Deserve a mention IMHO.

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?

What kind of other limitations I'm not aware of, and the doc glosses over (or that I missed/haven't read yet), that I'd discover then?

This is for this kind of insights that I turn to experts on MLs. Thanks, --DD