What is the cause of the error "Out of shared memory: You might need to increase max_locks_per_transaction"?

Good day.
Today I received this error in the logs. Before the server restart without spinning somewhere three months, the database isn't very large, but in some tables with hundreds of thousands of rows. Increased this is the "max_locks_per_transaction" raised "shared_buffers" and "work_mem", restarted postgres, the problem disappeared. But there forever?
Was:
shared_buffers = 128MB # min 128kB
work_mem = 4MB # min 64kB
temp_buffers = 8MB # min 800kB
max_locks_per_transaction = 64 # min 10

Was:
shared_buffers = 256MB # min 128kB
work_mem = 16MB # min 64kB
temp_buffers = 32MB # min 800kB
max_locks_per_transaction = 1024 # min 10


Querying pg_locks sane information is not given (or I just don't know how to read between the lines).

Actually, the question follows: where to dig, it is possible to set additional logging? I want to understand exactly why it's appeared, in order to prevent further relapses. Maybe I have crooked queries that produce didlake? Or just increase the dB requires an increase amount of allocated memory? Something else?

Thanks in advance.
March 20th 20 at 11:20
1 answer
March 20th 20 at 11:22
Solution
https://www.postgresql.org/docs/current/runtime-co...
This parameter controls the average number of object locks allocated for each transaction; individual transactions can lock more objects as long as the locks of all transactions fit in the lock table. This is not the number of rows that can be locked; that value is unlimited. The default, 64, has historically proven sufficient, but you might need to raise this value if you have queries that touch many different tables in a single transaction, e.g. query of a parent table with many children.


Has nothing to do with the size of the tables. Is related to the size of the internal table locks and the number of affected objects (e.g., tables) in a transaction. Strictly speaking: the maximum number of lock objects at the same time by all active transactions.
Has nothing to shared_buffers (not subtracted from and added on top. shared_buffers - strictly the buffer pages, the data structures for managing this buffer. Shared memory segment are always more shared_buffers) or work_mem or something else (other than max_connections + max_prepared_transactions). The more irrelevant to delaram.

The default value is usually more than enough. Can be abused protezirovanie.

Find more questions by tags PostgreSQL