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