Hi Tom, i agree with you, there is something allocating and not releasing memory. This because the error starts after many hours. I have some extensions installed, namely: "plpgsql" "mysql_fdw" "multicorn" "pg_cron" "tds_fdw" "pg_stat_statements" I have 200 connections and 384 max_locks_per_transaction in postgresql.conf, that brings the ttal number og locks to 76.800. I have surely very big queries, some with more than 50 tables, subqueries, and so on. As for your questions: 1) lock table entries needed: we check regularly the number of locks. It doesn't seem they usually exceed some hundreds. I will create a cron to check them regularly. 2) We have some very complex functions, touching many tables and returning 1/2 million rows 3) we don't use any kind of personalised locks. The db is set to read_committed isolation level. Just two question: How could I understand which extension is consuming shared memory? I can't figure how. Is there some way other than increasing max_locks_per_transaction to drive postgres to allocate more shared memory? -----Messaggio originale----- Da: Tom Lane <tgl@xxxxxxxxxxxxx> Inviato: venerdì 20 luglio 2018 16:23 A: Alfonso Moscato <alfonso.moscato@xxxxxxxxxxx> Cc: pgsql-admin@xxxxxxxxxxxxxxxxxxxx Oggetto: Re: repeated out of shared memory error - not related to max_locks_per_transaction "Alfonso Moscato" <alfonso.moscato@xxxxxxxxxxx> writes: > We are getting crazy with "out of shared memory" errors, and we can't > figure the reason. I don't think any of the advice posted so far has anything to do with your problem --- certainly, fooling with work_mem is unrelated. PG shared memory is a fixed-size arena (for any one setting of shared_buffers, max_connections, max_locks_per_transaction, and a couple other variables) and most of its contents are pre-allocated at postmaster start. What you are describing sounds like a long-term leak of additional, post-startup shmem allocations, eventually running out of the available slop in the shmem arena. work_mem, and other user-visible knobs, have nothing to do with this because those control allocations in process private memory not shmem. I'm pretty sure that the *only* post-startup shmem allocations in the core code are for lock table entries. However, if you're running any non-core extensions, it's possible that one of them does such allocations and has a logic error that results in a shmem leak. As an amelioration measure, you could raise max_locks_per_transaction, which will increase the arena size without actually eating any additional space immediately at startup. That might not cure the problem, but at least it would increase the interval at which you have to restart the server. As for real solutions, I'd first look harder at the question of how many lock table entries you need. The fact that you only see a few dozen active entries when you look (after a failure) doesn't prove a thing about what the max transient requirement is. Do you have any applications that touch a whole lot of tables in a single transaction? Are you using any user-defined (advisory) locks, and if so what's the usage pattern like for those? The "bug in an extension" theory also needs investigation. regards, tom lane