2

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
2
  • Is the table partitioned? If so, how many partitions? Aug 4, 2021 at 8:38
  • @GerardH.Pille I created a hypertable over it and followed timescaledb's suggestion to set chunk size not over 25% of memory. I have 8GB memory and the table has 1900 chunks. Could it be the cause?
    – Hannah
    Aug 4, 2021 at 18:58

1 Answer 1

2

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.

1
  • 1
    I increased it to 500 and it worked! And yes, my table does have a ridiculous number of partitions. Please see my comment above. It has over 1900 chunks. But how can this be avoided if the table is too large and I don't want to get pass timescale's guidance of "25% memory" in setting chunk size?
    – Hannah
    Aug 4, 2021 at 19:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.