Обсуждение: How to diagnose max_locks_per_transaction is about to be exhausted?

Hello!

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

Best regads
   Alexey Bashtanov

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

On 01.04.2015 17:09, Kevin Grittner wrote:
>
> 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.
Hmm, pg_locks surely shows two lines for the locks on the same object
when the pids or the granted differ.

Alexey Bashtanov