On 26th of January, Magnus Hagander committed patch:
Adds a counter that tracks number of deadlocks that occurred in each database to pg_stat_database. Magnus Hagander, reviewed by Jaime Casanova
It's similar to previously described patch – another column in pg_stat_database:
$ SELECT datname, deadlocks FROM pg_stat_database; datname │ deadlocks ───────────┼─────────── template0 │ 0 depesz │ 0 jab │ 0 pgdba │ 0 postgres │ 0 template1 │ 0 (6 ROWS)
This is ever increasing counter, that gets incremented whenever Pg will detect deadlock. So, let's see:
psql1> CREATE TABLE z AS SELECT i FROM generate_series(1,2) AS i; SELECT 2 psql1> BEGIN; BEGIN psql1> DELETE FROM z WHERE i = 1; DELETE 1 psql2> BEGIN; BEGIN psql2> DELETE FROM z WHERE i = 2; DELETE 1 psql2> DELETE FROM z WHERE i = 1; DELETE 1 psql1> DELETE FROM z WHERE i = 2; ERROR: deadlock detected DETAIL: Process 29419 waits FOR ShareLock ON TRANSACTION 866; blocked BY process 29555. Process 29555 waits FOR ShareLock ON TRANSACTION 865; blocked BY process 29419. HINT: See server log FOR query details. psql1> ROLLBACK; ROLLBACK psql1> SELECT datname, deadlocks FROM pg_stat_database; datname │ deadlocks ───────────┼─────────── template0 │ 0 depesz │ 1 jab │ 0 pgdba │ 0 postgres │ 0 template1 │ 0 (6 ROWS)
Counter is incremented. Thanks to this it will be simpler to see if deadlocks happen (for details you still can look in logs).
It's really good to see monitoring of Pg state simplified.
TBH, I’m not too excited by this. It makes an annoying task simpler, yes, but it doesn’t really give you enough information to solve the problem. For that you’ll still have to go trolling through the log files to dig out the relevant information, and if you are going to do that, you can pretty easily put the counts into a trending solution.
@Robert: It might not help you fix the problem, but it does help with event correlation. For example, if you have been graphing the deadlock count, you might overlay when you’ve done deployments, and determine which set of changes may have introduced deadlock problems.
It is a great feature for monitoring systems. Thanks.