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

I accidentally replied to Tom only. Here's my response again.

Apparently, I'm using the default max_locks_per_transaction:
```
ddevienne=> show max_locks_per_transaction ;
max_locks_per_transaction
---------------------------
64
(1 row)
```

Given max_locks_per_transaction * (max_connections
<https://www.postgresql.org/docs/current/runtime-config-connection.html#GUC-MAX-CONNECTIONS>
+ max_prepared_transactions
<https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-MAX-PREPARED-TRANSACTIONS>)
from
https://www.postgresql.org/docs/current/runtime-config-locks.html, and
max_conn being 100, that's not many locks.

Tom wrote "relation" for the number of locks necessary for DROP OWNED BY.
What does it mean in this context? relation = table?

Given there's only 64 locks per conn by default, how can this work with
over 100 tables?
I'm confused... --DD

On Mon, Jan 10, 2022 at 7:06 PM Dominique Devienne <ddevienne(at)gmail(dot)com>
wrote:

> On Mon, Jan 10, 2022 at 6:39 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>> Dominique Devienne <ddevienne(at)gmail(dot)com> writes:
>> > I'm trying to DROP a ROLE that has 4 schemas:
>> > * 2 smallish ones (1 or 2 dozen tables each),
>> > * 2 largish ones (250 tables, totalling around 4000 columns each).
>>
>> > And of course there are various indexes, constraints, etc... on each
>> schema.
>>
>> You're going to need a lock per dropped relation. The number of
>> columns or rows doesn't enter into it, but the number of indexes does.
>>
>
> Here are the current stats of the larguish schema:
>
> const size_t expected_table_count = 244;
> const size_t expected_index_count = 409;
> const size_t expected_unique_index_count = 181;
> const size_t expected_cnstr_count = 989;
> const size_t expected_pk_cnstr_count = 243;
> const size_t expected_fk_cnstr_count = 506;
> const size_t expected_check_cnstr_count = 64;
> const size_t expected_unique_cnstr_count = 176;
>
> > DDL Error: DROP OWNED BY "Epos-PNS (a73e1fbdcc5b4460ba55703a0d6b6ad7)"
>> > CASCADE: #53200: ERROR: out of shared memory
>> > HINT: You might need to increase max_locks_per_transaction.
>>
>> I'd not have expected that when dropping 500-or-so tables, but maybe
>> you have a lot of indexes per table?
>>
>
> See above for the total.
> Since we use ON DELETE CASCADE and FKs, and coming from Oracle, I do index
> all my FKs...
>
>
>> > And please note that there could be dozens even hundreds of largish
>> schemas
>> > associated to the dropped ROLE (2 + N), not just the 2+2 it fails with
>> here.
>>
>> There's not a lot of penalty to increasing max_locks_per_transaction,
>> but no you can't make it "unbounded".
>>
>
> Is the HINT valid? How do I determine the current value, and change it?
> Could it be some other problem?
>

> Dropping each largish schema individually is certainly possible, but again
> coming from Oracle,
> I'm used to making transaction as big as they logically need to be, with
> little physical limitations.
> The action is drop all schemas of that instance of the "system", which has
> 2+N schemas. So the
> fact I'd need to use several transactions to work-around
> max_locks_per_transaction is a bummer... --DD
>