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

On 2022-Jan-10, Dominique Devienne wrote:

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

6400 locks, to be precise. So if your schemas have on average 10 tables
each with 3 indexes per table, you could drop at most 160 schemas in one
go (but only if you're lucky.)

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

That value indicates the maximum number of locks that can be taken
across all sessions at a time. You can have a single session take that
number of locks, or all sessions take 64 locks each.

If you really have many more relations that need to be dropped, you
could try to issue "DROP SCHEMA...CASCADE" for each schema to drop.
It's a lot less convenient than DROP OWNED BY, but it doesn't require to
take as many locks simultaneously.

--
Álvaro Herrera 39°49'30"S 73°17'W — https://www.EnterpriseDB.com/
"Los trabajadores menos efectivos son sistematicamente llevados al lugar
donde pueden hacer el menor daño posible: gerencia." (El principio Dilbert)