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