Can't do simple query or drop table due to out of memory error, but pg_locks return no lock

Question

I received this out of memory error when trying to drop a large table (70GB including index) but couldn't identify the root cause from pg_locks view

DROP TABLE big_table;

ERROR:  out of shared memory
HINT:  You might need to increase max_locks_per_transaction.

I had this error several times when updating this table and has increased max_locks_per_transaction from 64 to 100. The updating went smoothly and I could do some query on it with no issue for a while. But a day later, I couldn't do anything with this table anymore. For some reasons, now I have to drop this table but can't do it due to this error, even after I increased max_locks_per_transaction to 150 (the current max_connections is 20). The strange thing is when I tried to find if there's any lock, I didn't note any unusual query.

SELECT relation::regclass, * FROM pg_locks WHERE not granted;
(0 rows)

I could try increase max_locks_per_transaction again but it seems there's other issue that I need to address. Also, this might not be relevant but just in case it's helpful: the script to update the table in question (big_table) had a query to another table which is updated hourly. Anyway, the transaction to update big_table only ran once and has ended by the time I tried to drop it. Both tables are hypertables created with timescaledb. Below is the full view from pg_locks in case it's helpful.

SELECT * FROM pg_locks;

-[ RECORD 1 ]------+----------------
locktype           | relation
database           | 16384
relation           | 12143
page               |
tuple              |
virtualxid         |
transactionid      |
classid            |
objid              |
objsubid           |
virtualtransaction | 4/33
pid                | 344280
mode               | AccessShareLock
granted            | t
fastpath           | t
-[ RECORD 2 ]------+----------------
locktype           | virtualxid
database           |
relation           |
page               |
tuple              |
virtualxid         | 4/33
transactionid      |
classid            |
objid              |
objsubid           |
virtualtransaction | 4/33
pid                | 344280
mode               | ExclusiveLock
granted            | t
fastpath           | t

Answer 1

It sounds like your operation is single-handedly filling up the lock table. Then as soon as the error is thrown, it drops all of its locks, so you don't see any evidence left behind in the lock table by the time you look.

There is no reason to be particularly conservative with the max_locks_per_transaction on modern hardware. Just set it to something like 10000 and see if that solves the problem.

But it does suggest your table might have a ridiculous number of partitions.