[check_postgres] Checking PostgreSQL lock count

[Ben Primrose]

Pg_stat_activity will show a row per session, but a session can hold
multiple locks.  Join it to pg_locks to get those details:

SELECT locks.pid     AS pid
     , activity.usename  AS user
     , locks.relation::regclass AS relation
     , locks.mode as lock_mode
--     , activity.query    AS statement
  FROM pg_catalog.pg_locks         locks
  JOIN pg_catalog.pg_stat_activity activity
    ON activity.pid = locks.pid
 WHERE locks.GRANTED
 ORDER BY 1, 3;

-----Original Message-----
From: check_postgres-bounces at bucardo.org
[mailto:check_postgres-bounces at bucardo.org] On Behalf Of Thomas Güttler
Sent: Thursday, December 17, 2015 8:39 AM
To: check_postgres at bucardo.org
Subject: [check_postgres] Checking PostgreSQL lock count

Hi

We use check_postgres.pl to monitor our database.

We use this to check the count of the locks:

https://bucardo.org/check_postgres/check_postgres.pl.html#locks

We often see more than 150 locks.

The question was: What is going on? We patched the script to output this
sql statement, if the lock count was exceeded:

select * from pg_stat_activity order by datname

Unfortunately the result is not what I was expecting. Although there are
more than 150 locks, pg_stat_activity shows only few (less then 10)
queries.

This has happened about twice a day during the last days, and every time
only few lines where returned by pg_stat_activity.

What is going on here?

How can I further debug where these locks come from?

BTW, same question here:

http://dba.stackexchange.com/questions/123945/checking-postgresql-lock-cou
nt-with-check-postgres-pl

--
Thomas Guettler http://www.thomas-guettler.de/
_______________________________________________
Check_postgres mailing list
Check_postgres at bucardo.org
https://mail.endcrypt.com/mailman/listinfo/check_postgres