"Alfonso Moscato" <alfonso.moscato@merqurio.it> 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
