Re: Avoid orphaned objects dependencies, take 3

On Wed, Jun 19, 2024 at 7:49 AM Ashutosh Sharma <ashu(dot)coek88(at)gmail(dot)com> wrote:
> If the dependency is more, this can hit max_locks_per_transaction
> limit very fast.

Your experiment doesn't support this conclusion. Very few users would
have 15 separate user-defined types in the same table, and even if
they did, and dropped the table, using 23 locks is no big deal. By
default, max_locks_per_transaction is 64, so the user would need to
have more like 45 separate user-defined types in the same table in
order to use more than 64 locks. So, yes, it is possible that if every
backend in the system were simultaneously trying to drop a table and
all of those tables had an average of at least 45 or so user-defined
types, all different from each other, you might run out of lock table
space.

But probably nobody will ever do that in real life, and if they did,
they could just raise max_locks_per_transaction.

When posting about potential problems like this, it is a good idea to
first do a careful thought experiment to assess how realistic the
problem is. I would consider an issue like this serious if there were
a realistic scenario under which a small number of backends could
exhaust the lock table for the whole system, but I think you can see
that this isn't the case here. Even your original scenario is more
extreme than what most people are likely to hit in real life, and it
only uses 23 locks.

--
Robert Haas
EDB: http://www.enterprisedb.com