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

On Mon, Jan 10, 2022 at 10:40 PM Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>
wrote:

> On 2022-Jan-10, Dominique Devienne wrote:
>
> > Btw, is there a catalog accurately count a schémas relations from
> the catalog?
>
> pg_class
>

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

> > Of course I can do that. But it doesn’t feel right.
>
> Then you should increase max_locks_per_transaction to a value that better
> suits you.
>

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