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

On Mon, Jan 10, 2022 at 11:13 PM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> On 1/10/22 13:58, Dominique Devienne wrote:
>
> > Given that Tom mentions max_locks_per_transaction can be safely
> increased,
> > and given the stats I mentioned in this thread, what would a
> > "reasonable" max_locks_per_transaction
> > be in my case? By reasonable, I mean "as large as possible w/o being
> too large"...
> >
> > Obviously 64*100 is not quite large enough to be safe in this case.
> I'd appreciate some advise. TIA, --DD
>
> I think at this point it might be a good idea to explore what the case is?
>

I go into use cases in my previous message, send a minute ago.

In your OP the error occurred here:
> DROP OWNED BY "Epos-PNS (a73e1fbdcc5b4460ba55703a0d6b6ad7)" CASCADE
> The CASCADE allows to command to recurse to objects that may not be
> owned by the specified role. Is that what you want?
>

Sure. Although that's irrelevant. There are no such other objects.
Each 2+N schemas "instance" is self-contained, and all ROLEs associated
to those SCHEMAs only have privileges associated to those schemas.

Actual LOGIN USERs are granted access to a subset of ROLEs in "instances"
based on individual permissions.
So when I nuke an "instance", I DROP all its SCHEMAs, and all their
associated ROLEs.

And DROP'ing those "instance"-specific ROLEs implicitly severs the GRANTs
made on LOGIN USERs, who remain.

> What is the purpose of the process, clearing out given schema, dropping
> objects only owned by a given role, or something else?
>

"dropping objects only owned by a given role" indeed.
I.e. exactly what DROP OWNED BY is designed to do, no?

It just so happens that it seems impossible to do that, because that
involves too many locks :(.

With 1400 relations for each of the N schemas, and actual in-prod instances
at client sites
that require hundreds such schemas (on the current system not based on
PostgreSQL), I'm guessing
I can't assign 1400 * 1000 = over a million locks on the cluster, can I?

So now the question I asked in that other message, is whether I should use
a dedicated DB per "instance" instead? --DD