Re: How to diagnose max_locks_per_transaction is about to be exhausted?

Alexey Bashtanov <bashtanov@imap.cc> wrote:

> max_locks_per_transaction enforces some restriction:
> the_locks_count <= the_threshold
>
> the_threshold is max_locks_per_transaction * (max_connections +
> max_prepared_transactions), the documentation is quite clear.
>
> What is the_locks_count?
> In docs we see "no more than this many distinct objects can be
> locked at any one time".
> What kinds of locks should we include? does the limit really
> restricts *distinct* objects count and what is the distinction
> key for every lock kind?
>
> What should one change in the following query to make it show the
> value limited by the_threshold?
>
> select
>     count(distinct relation) + count(distinct (classid, objid))
>   from pg_locks

I think the "distinct" reference is because if one connection takes
out the same lock on the same object (e.g., a table) you don't need
to count it more than once.  The pg_locks view only shows it once
anyway.  The view does contain both locks limited by
max_locks_per_transaction and those limited by
max_pred_locks_per_transaction.  I think you will get the number
you are looking for simply by excluding the latter from a count of
all rows in the view:

select count(*) from pg_locks where mode <> 'SIReadLock';

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company