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