Skip to content

Commit 61cb8c5

Browse files
mhagandermhagander
committed
Add deadlock counter to pg_stat_database
Adds a counter that tracks number of deadlocks that occurred in each database to pg_stat_database. Magnus Hagander, reviewed by Jaime Casanova
1 parent 0e54969 commit 61cb8c5

File tree

9 files changed

+91
-8
lines changed

9 files changed

+91
-8
lines changed

‎doc/src/sgml/monitoring.sgml

Lines changed: 10 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -283,8 +283,8 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
283283
read requests avoided by finding the block already in buffer cache),
284284
number of rows returned, fetched, inserted, updated and deleted, the
285285
total number of queries canceled due to conflict with recovery (on
286-
standby servers), number and size of temporary files used, and time
287-
of last statistics reset.
286+
standby servers), number and size of temporary files used, total
287+
number of deadlocks detected, and time of last statistics reset.
288288
</entry>
289289
</row>
290290

@@ -909,6 +909,14 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
909909
</entry>
910910
</row>
911911

912+
<row>
913+
<entry><literal><function>pg_stat_get_db_deadlocks</function>(<type>oid</type>)</literal></entry>
914+
<entry><type>bigint</type></entry>
915+
<entry>
916+
Number of deadlocks detected in the database
917+
</entry>
918+
</row>
919+
912920
<row>
913921
<entry><literal><function>pg_stat_get_numscans</function>(<type>oid</type>)</literal></entry>
914922
<entry><type>bigint</type></entry>

‎src/backend/catalog/system_views.sql

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -578,6 +578,7 @@ CREATE VIEW pg_stat_database AS
578578
pg_stat_get_db_conflict_all(D.oid) AS conflicts,
579579
pg_stat_get_db_temp_files(D.oid) AS temp_files,
580580
pg_stat_get_db_temp_bytes(D.oid) AS temp_bytes,
581+
pg_stat_get_db_deadlocks(D.oid) AS deadlocks,
581582
pg_stat_get_db_stat_reset_time(D.oid) AS stats_reset
582583
FROM pg_database D;
583584

‎src/backend/postmaster/pgstat.c

Lines changed: 41 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -286,6 +286,7 @@ static void pgstat_recv_bgwriter(PgStat_MsgBgWriter *msg, int len);
286286
static void pgstat_recv_funcstat(PgStat_MsgFuncstat *msg, int len);
287287
static void pgstat_recv_funcpurge(PgStat_MsgFuncpurge *msg, int len);
288288
static void pgstat_recv_recoveryconflict(PgStat_MsgRecoveryConflict *msg, int len);
289+
static void pgstat_recv_deadlock(PgStat_MsgDeadlock *msg, int len);
289290
static void pgstat_recv_tempfile(PgStat_MsgTempFile *msg, int len);
290291

291292

@@ -1340,6 +1341,24 @@ pgstat_report_recovery_conflict(int reason)
13401341
pgstat_send(&msg, sizeof(msg));
13411342
}
13421343

1344+
/* --------
1345+
* pgstat_report_deadlock() -
1346+
*
1347+
* Tell the collector about a deadlock detected.
1348+
* --------
1349+
*/
1350+
void
1351+
pgstat_report_deadlock(void)
1352+
{
1353+
PgStat_MsgDeadlock msg;
1354+
1355+
if (pgStatSock == PGINVALID_SOCKET || !pgstat_track_counts)
1356+
return;
1357+
1358+
pgstat_setheader(&msg.m_hdr, PGSTAT_MTYPE_DEADLOCK);
1359+
msg.m_databaseid = MyDatabaseId;
1360+
pgstat_send(&msg, sizeof(msg));
1361+
}
13431362

13441363
/* --------
13451364
* pgstat_report_tempfile() -
@@ -1361,7 +1380,6 @@ pgstat_report_tempfile(size_t filesize)
13611380
pgstat_send(&msg, sizeof(msg));
13621381
}
13631382

1364-
;
13651383

13661384
/* ----------
13671385
* pgstat_ping() -
@@ -3242,6 +3260,10 @@ PgstatCollectorMain(int argc, char *argv[])
32423260
pgstat_recv_recoveryconflict((PgStat_MsgRecoveryConflict *) &msg, len);
32433261
break;
32443262

3263+
case PGSTAT_MTYPE_DEADLOCK:
3264+
pgstat_recv_deadlock((PgStat_MsgDeadlock *) &msg, len);
3265+
break;
3266+
32453267
case PGSTAT_MTYPE_TEMPFILE:
32463268
pgstat_recv_tempfile((PgStat_MsgTempFile *) &msg, len);
32473269
break;
@@ -3329,6 +3351,7 @@ pgstat_get_db_entry(Oid databaseid, bool create)
33293351
result->n_conflict_startup_deadlock = 0;
33303352
result->n_temp_files = 0;
33313353
result->n_temp_bytes = 0;
3354+
result->n_deadlocks = 0;
33323355

33333356
result->stat_reset_timestamp = GetCurrentTimestamp();
33343357

@@ -4242,6 +4265,7 @@ pgstat_recv_resetcounter(PgStat_MsgResetcounter *msg, int len)
42424265
dbentry->last_autovac_time = 0;
42434266
dbentry->n_temp_bytes = 0;
42444267
dbentry->n_temp_files = 0;
4268+
dbentry->n_deadlocks = 0;
42454269

42464270
dbentry->stat_reset_timestamp = GetCurrentTimestamp();
42474271

@@ -4467,6 +4491,22 @@ pgstat_recv_recoveryconflict(PgStat_MsgRecoveryConflict *msg, int len)
44674491
}
44684492
}
44694493

4494+
/* ----------
4495+
* pgstat_recv_deadlock() -
4496+
*
4497+
* Process as DEADLOCK message.
4498+
* ----------
4499+
*/
4500+
static void
4501+
pgstat_recv_deadlock(PgStat_MsgDeadlock *msg, int len)
4502+
{
4503+
PgStat_StatDBEntry *dbentry;
4504+
4505+
dbentry = pgstat_get_db_entry(msg->m_databaseid, true);
4506+
4507+
dbentry->n_deadlocks++;
4508+
}
4509+
44704510
/* ----------
44714511
* pgstat_recv_tempfile() -
44724512
*
@@ -4482,7 +4522,6 @@ pgstat_recv_tempfile(PgStat_MsgTempFile *msg, int len)
44824522

44834523
dbentry->n_temp_bytes += msg->m_filesize;
44844524
dbentry->n_temp_files += 1;
4485-
44864525
}
44874526

44884527
/* ----------

‎src/backend/storage/lmgr/deadlock.c

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -938,6 +938,8 @@ DeadLockReport(void)
938938
pgstat_get_backend_current_activity(info->pid, false));
939939
}
940940

941+
pgstat_report_deadlock();
942+
941943
ereport(ERROR,
942944
(errcode(ERRCODE_T_R_DEADLOCK_DETECTED),
943945
errmsg("deadlock detected"),

‎src/backend/utils/adt/pgstatfuncs.c

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -78,6 +78,7 @@ extern Datum pg_stat_get_db_conflict_snapshot(PG_FUNCTION_ARGS);
7878
extern Datum pg_stat_get_db_conflict_bufferpin(PG_FUNCTION_ARGS);
7979
extern Datum pg_stat_get_db_conflict_startup_deadlock(PG_FUNCTION_ARGS);
8080
extern Datum pg_stat_get_db_conflict_all(PG_FUNCTION_ARGS);
81+
extern Datum pg_stat_get_db_deadlocks(PG_FUNCTION_ARGS);
8182
extern Datum pg_stat_get_db_stat_reset_time(PG_FUNCTION_ARGS);
8283
extern Datum pg_stat_get_db_temp_files(PG_FUNCTION_ARGS);
8384
extern Datum pg_stat_get_db_temp_bytes(PG_FUNCTION_ARGS);
@@ -1341,6 +1342,21 @@ pg_stat_get_db_conflict_all(PG_FUNCTION_ARGS)
13411342
PG_RETURN_INT64(result);
13421343
}
13431344

1345+
Datum
1346+
pg_stat_get_db_deadlocks(PG_FUNCTION_ARGS)
1347+
{
1348+
Oid dbid = PG_GETARG_OID(0);
1349+
int64 result;
1350+
PgStat_StatDBEntry *dbentry;
1351+
1352+
if ((dbentry = pgstat_fetch_stat_dbentry(dbid)) == NULL)
1353+
result = 0;
1354+
else
1355+
result = (int64) (dbentry->n_deadlocks);
1356+
1357+
PG_RETURN_INT64(result);
1358+
}
1359+
13441360
Datum
13451361
pg_stat_get_bgwriter_timed_checkpoints(PG_FUNCTION_ARGS)
13461362
{

‎src/include/catalog/catversion.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/* yyyymmddN */
56-
#define CATALOG_VERSION_NO 201201261
56+
#define CATALOG_VERSION_NO 201201262
5757

5858
#endif

‎src/include/catalog/pg_proc.h

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2634,6 +2634,8 @@ DATA(insert OID = 3069 ( pg_stat_get_db_conflict_startup_deadlock PGNSP PGUID 1
26342634
DESCR("statistics: recovery conflicts in database caused by buffer deadlock");
26352635
DATA(insert OID = 3070 ( pg_stat_get_db_conflict_all PGNSP PGUID 12 1 0 0 0 f f f t f s 1 0 20 "26" _null_ _null_ _null_ _null_ pg_stat_get_db_conflict_all _null_ _null_ _null_ ));
26362636
DESCR("statistics: recovery conflicts in database");
2637+
DATA(insert OID = 3152 ( pg_stat_get_db_deadlocks PGNSP PGUID 12 1 0 0 0 f f f t f s 1 0 20 "26" _null_ _null_ _null_ _null_ pg_stat_get_db_deadlocks _null_ _null_ _null_ ));
2638+
DESCR("statistics: deadlocks detected in database");
26372639
DATA(insert OID = 3074 ( pg_stat_get_db_stat_reset_time PGNSP PGUID 12 1 0 0 0 f f f t f s 1 0 1184 "26" _null_ _null_ _null_ _null_ pg_stat_get_db_stat_reset_time _null_ _null_ _null_ ));
26382640
DESCR("statistics: last reset for a database");
26392641
DATA(insert OID = 3150 ( pg_stat_get_db_temp_files PGNSP PGUID 12 1 0 0 0 f f f t f s 1 0 20 "26" _null_ _null_ _null_ _null_ pg_stat_get_db_temp_files _null_ _null_ _null_ ));

‎src/include/pgstat.h

Lines changed: 17 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -48,7 +48,8 @@ typedef enum StatMsgType
4848
PGSTAT_MTYPE_FUNCSTAT,
4949
PGSTAT_MTYPE_FUNCPURGE,
5050
PGSTAT_MTYPE_RECOVERYCONFLICT,
51-
PGSTAT_MTYPE_TEMPFILE
51+
PGSTAT_MTYPE_TEMPFILE,
52+
PGSTAT_MTYPE_DEADLOCK
5253
} StatMsgType;
5354

5455
/* ----------
@@ -462,6 +463,17 @@ typedef struct PgStat_MsgFuncpurge
462463
Oid m_functionid[PGSTAT_NUM_FUNCPURGE];
463464
} PgStat_MsgFuncpurge;
464465

466+
/* ----------
467+
* PgStat_MsgDeadlock Sent by the backend to tell the collector
468+
* about a deadlock that occurred.
469+
* ----------
470+
*/
471+
typedef struct PgStat_MsgDeadlock
472+
{
473+
PgStat_MsgHdr m_hdr;
474+
Oid m_databaseid;
475+
} PgStat_MsgDeadlock;
476+
465477

466478
/* ----------
467479
* PgStat_Msg Union over all possible messages.
@@ -485,6 +497,7 @@ typedef union PgStat_Msg
485497
PgStat_MsgFuncstat msg_funcstat;
486498
PgStat_MsgFuncpurge msg_funcpurge;
487499
PgStat_MsgRecoveryConflict msg_recoveryconflict;
500+
PgStat_MsgDeadlock msg_deadlock;
488501
} PgStat_Msg;
489502

490503

@@ -496,7 +509,7 @@ typedef union PgStat_Msg
496509
* ------------------------------------------------------------
497510
*/
498511

499-
#define PGSTAT_FILE_FORMAT_ID 0x01A5BC99
512+
#define PGSTAT_FILE_FORMAT_ID 0x01A5BC9A
500513

501514
/* ----------
502515
* PgStat_StatDBEntry The collector's data per database
@@ -522,6 +535,7 @@ typedef struct PgStat_StatDBEntry
522535
PgStat_Counter n_conflict_startup_deadlock;
523536
PgStat_Counter n_temp_files;
524537
PgStat_Counter n_temp_bytes;
538+
PgStat_Counter n_deadlocks;
525539

526540
TimestampTz stat_reset_timestamp;
527541

@@ -746,6 +760,7 @@ extern void pgstat_report_analyze(Relation rel,
746760
PgStat_Counter livetuples, PgStat_Counter deadtuples);
747761

748762
extern void pgstat_report_recovery_conflict(int reason);
763+
extern void pgstat_report_deadlock(void);
749764

750765
extern void pgstat_initialize(void);
751766
extern void pgstat_bestart(void);

‎src/test/regress/expected/rules.out

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1296,7 +1296,7 @@ SELECT viewname, definition FROM pg_views WHERE schemaname <> 'information_schem
12961296
pg_stat_all_indexes | SELECT c.oid AS relid, i.oid AS indexrelid, n.nspname AS schemaname, c.relname, i.relname AS indexrelname, pg_stat_get_numscans(i.oid) AS idx_scan, pg_stat_get_tuples_returned(i.oid) AS idx_tup_read, pg_stat_get_tuples_fetched(i.oid) AS idx_tup_fetch FROM (((pg_class c JOIN pg_index x ON ((c.oid = x.indrelid))) JOIN pg_class i ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char"]));
12971297
pg_stat_all_tables | SELECT c.oid AS relid, n.nspname AS schemaname, c.relname, pg_stat_get_numscans(c.oid) AS seq_scan, pg_stat_get_tuples_returned(c.oid) AS seq_tup_read, (sum(pg_stat_get_numscans(i.indexrelid)))::bigint AS idx_scan, ((sum(pg_stat_get_tuples_fetched(i.indexrelid)))::bigint + pg_stat_get_tuples_fetched(c.oid)) AS idx_tup_fetch, pg_stat_get_tuples_inserted(c.oid) AS n_tup_ins, pg_stat_get_tuples_updated(c.oid) AS n_tup_upd, pg_stat_get_tuples_deleted(c.oid) AS n_tup_del, pg_stat_get_tuples_hot_updated(c.oid) AS n_tup_hot_upd, pg_stat_get_live_tuples(c.oid) AS n_live_tup, pg_stat_get_dead_tuples(c.oid) AS n_dead_tup, pg_stat_get_last_vacuum_time(c.oid) AS last_vacuum, pg_stat_get_last_autovacuum_time(c.oid) AS last_autovacuum, pg_stat_get_last_analyze_time(c.oid) AS last_analyze, pg_stat_get_last_autoanalyze_time(c.oid) AS last_autoanalyze, pg_stat_get_vacuum_count(c.oid) AS vacuum_count, pg_stat_get_autovacuum_count(c.oid) AS autovacuum_count, pg_stat_get_analyze_count(c.oid) AS analyze_count, pg_stat_get_autoanalyze_count(c.oid) AS autoanalyze_count FROM ((pg_class c LEFT JOIN pg_index i ON ((c.oid = i.indrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char"])) GROUP BY c.oid, n.nspname, c.relname;
12981298
pg_stat_bgwriter | SELECT pg_stat_get_bgwriter_timed_checkpoints() AS checkpoints_timed, pg_stat_get_bgwriter_requested_checkpoints() AS checkpoints_req, pg_stat_get_bgwriter_buf_written_checkpoints() AS buffers_checkpoint, pg_stat_get_bgwriter_buf_written_clean() AS buffers_clean, pg_stat_get_bgwriter_maxwritten_clean() AS maxwritten_clean, pg_stat_get_buf_written_backend() AS buffers_backend, pg_stat_get_buf_fsync_backend() AS buffers_backend_fsync, pg_stat_get_buf_alloc() AS buffers_alloc, pg_stat_get_bgwriter_stat_reset_time() AS stats_reset;
1299-
pg_stat_database | SELECT d.oid AS datid, d.datname, pg_stat_get_db_numbackends(d.oid) AS numbackends, pg_stat_get_db_xact_commit(d.oid) AS xact_commit, pg_stat_get_db_xact_rollback(d.oid) AS xact_rollback, (pg_stat_get_db_blocks_fetched(d.oid) - pg_stat_get_db_blocks_hit(d.oid)) AS blks_read, pg_stat_get_db_blocks_hit(d.oid) AS blks_hit, pg_stat_get_db_tuples_returned(d.oid) AS tup_returned, pg_stat_get_db_tuples_fetched(d.oid) AS tup_fetched, pg_stat_get_db_tuples_inserted(d.oid) AS tup_inserted, pg_stat_get_db_tuples_updated(d.oid) AS tup_updated, pg_stat_get_db_tuples_deleted(d.oid) AS tup_deleted, pg_stat_get_db_conflict_all(d.oid) AS conflicts, pg_stat_get_db_temp_files(d.oid) AS temp_files, pg_stat_get_db_temp_bytes(d.oid) AS temp_bytes, pg_stat_get_db_stat_reset_time(d.oid) AS stats_reset FROM pg_database d;
1299+
pg_stat_database | SELECT d.oid AS datid, d.datname, pg_stat_get_db_numbackends(d.oid) AS numbackends, pg_stat_get_db_xact_commit(d.oid) AS xact_commit, pg_stat_get_db_xact_rollback(d.oid) AS xact_rollback, (pg_stat_get_db_blocks_fetched(d.oid) - pg_stat_get_db_blocks_hit(d.oid)) AS blks_read, pg_stat_get_db_blocks_hit(d.oid) AS blks_hit, pg_stat_get_db_tuples_returned(d.oid) AS tup_returned, pg_stat_get_db_tuples_fetched(d.oid) AS tup_fetched, pg_stat_get_db_tuples_inserted(d.oid) AS tup_inserted, pg_stat_get_db_tuples_updated(d.oid) AS tup_updated, pg_stat_get_db_tuples_deleted(d.oid) AS tup_deleted, pg_stat_get_db_conflict_all(d.oid) AS conflicts, pg_stat_get_db_temp_files(d.oid) AS temp_files, pg_stat_get_db_temp_bytes(d.oid) AS temp_bytes, pg_stat_get_db_deadlocks(d.oid) AS deadlocks, pg_stat_get_db_stat_reset_time(d.oid) AS stats_reset FROM pg_database d;
13001300
pg_stat_database_conflicts | SELECT d.oid AS datid, d.datname, pg_stat_get_db_conflict_tablespace(d.oid) AS confl_tablespace, pg_stat_get_db_conflict_lock(d.oid) AS confl_lock, pg_stat_get_db_conflict_snapshot(d.oid) AS confl_snapshot, pg_stat_get_db_conflict_bufferpin(d.oid) AS confl_bufferpin, pg_stat_get_db_conflict_startup_deadlock(d.oid) AS confl_deadlock FROM pg_database d;
13011301
pg_stat_replication | SELECT s.pid, s.usesysid, u.rolname AS usename, s.application_name, s.client_addr, s.client_hostname, s.client_port, s.backend_start, w.state, w.sent_location, w.write_location, w.flush_location, w.replay_location, w.sync_priority, w.sync_state FROM pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, waiting, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port), pg_authid u, pg_stat_get_wal_senders() w(pid, state, sent_location, write_location, flush_location, replay_location, sync_priority, sync_state) WHERE ((s.usesysid = u.oid) AND (s.pid = w.pid));
13021302
pg_stat_sys_indexes | SELECT pg_stat_all_indexes.relid, pg_stat_all_indexes.indexrelid, pg_stat_all_indexes.schemaname, pg_stat_all_indexes.relname, pg_stat_all_indexes.indexrelname, pg_stat_all_indexes.idx_scan, pg_stat_all_indexes.idx_tup_read, pg_stat_all_indexes.idx_tup_fetch FROM pg_stat_all_indexes WHERE ((pg_stat_all_indexes.schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (pg_stat_all_indexes.schemaname ~ '^pg_toast'::text));

0 commit comments

Comments
 (0)