"Alfonso Moscato" <alfonso.moscato@xxxxxxxxxxx> writes: > I have some extensions installed, namely: > "plpgsql" > "mysql_fdw" > "multicorn" > "pg_cron" > "tds_fdw" > "pg_stat_statements" plpgsql doesn't use any shared memory. pg_stat_statements does but it's a one-time, fixed-size allocation. The others you mention perhaps deserve investigation; I'm not familiar with their innards. The fact that the problem seems to be related to the passage of time and not to any particular application action might perhaps lead one to suspect pg_cron. But that's a leap of logic unsupported by any facts. > I have 200 connections and 384 max_locks_per_transaction in postgresql.conf, > that brings the ttal number og locks to 76.800. Right, or more accurately, enough shmem space for that many locks will be reserved at postmaster start. > I have surely very big queries, some with more than 50 tables, subqueries, > and so on. Well, 50-table queries are not your problem, no matter how much data they process. Transactions that touch, say, 100000 different tables might be your problem. I've heard of people having to raise max_locks_per_transaction in order to run pg_dump against databases with lots of tables, for example. > 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. You would really need to catch the state of the lock table just when the error is reported --- a check even a few seconds before that might not show any distress. > How could I understand which extension is consuming shared memory? I can't > figure how. AFAIK there isn't any good way except to check their source code for ShmemAlloc* and ShmemInit* calls. > Is there some way other than increasing max_locks_per_transaction to drive > postgres to allocate more shared memory? That's the only knob that wouldn't also produce a matching increase in the size of postmaster-startup allocations. There isn't any real downside to raising it; the only effect is to increase the size of the shmem segment. regards, tom lane
