Add views and functions to monitor hot standby query conflicts

Add the view pg_stat_database_conflicts and a column to pg_stat_database,
and the underlying functions to provide the information.

4 files changed, 190 insertions, 1 deletions

diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index ded046d9d06..262b3724902 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -506,7 +506,19 @@ CREATE VIEW pg_stat_database AS
             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_tuples_deleted(D.oid) AS tup_deleted,
+            pg_stat_get_db_conflict_all(D.oid) AS conflicts
+    FROM pg_database D;
+
+CREATE VIEW pg_stat_database_conflicts AS
+    SELECT
+            D.oid AS datid,
+            D.datname AS 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;
 
 CREATE VIEW pg_stat_user_functions AS
diff --git a/src/backend/postmaster/pgstat.c b/src/backend/postmaster/pgstat.c
index 850a543d7e7..301568f6df3 100644
--- a/src/backend/postmaster/pgstat.c
+++ b/src/backend/postmaster/pgstat.c
@@ -57,6 +57,7 @@
 #include "storage/ipc.h"
 #include "storage/pg_shmem.h"
 #include "storage/pmsignal.h"
+#include "storage/procsignal.h"
 #include "utils/guc.h"
 #include "utils/memutils.h"
 #include "utils/ps_status.h"
@@ -278,6 +279,7 @@ static void pgstat_recv_analyze(PgStat_MsgAnalyze *msg, int len);
 static void pgstat_recv_bgwriter(PgStat_MsgBgWriter *msg, int len);
 static void pgstat_recv_funcstat(PgStat_MsgFuncstat *msg, int len);
 static void pgstat_recv_funcpurge(PgStat_MsgFuncpurge *msg, int len);
+static void pgstat_recv_recoveryconflict(PgStat_MsgRecoveryConflict *msg, int len);
 
 
 /* ------------------------------------------------------------
@@ -1314,6 +1316,25 @@ pgstat_report_analyze(Relation rel, bool adopt_counts,
 	pgstat_send(&msg, sizeof(msg));
 }
 
+/* --------
+ * pgstat_report_recovery_conflict() -
+ *
+ *  Tell the collector about a Hot Standby recovery conflict.
+ * --------
+ */
+void
+pgstat_report_recovery_conflict(int reason)
+{
+	PgStat_MsgRecoveryConflict msg;
+
+	if (pgStatSock == PGINVALID_SOCKET || !pgstat_track_counts)
+		return;
+
+	pgstat_setheader(&msg.m_hdr, PGSTAT_MTYPE_RECOVERYCONFLICT);
+	msg.m_databaseid = MyDatabaseId;
+	msg.m_reason = reason;
+	pgstat_send(&msg, sizeof(msg));
+}
 
 /* ----------
  * pgstat_ping() -
@@ -3053,6 +3074,10 @@ PgstatCollectorMain(int argc, char *argv[])
 					pgstat_recv_funcpurge((PgStat_MsgFuncpurge *) &msg, len);
 					break;
 
+				case PGSTAT_MTYPE_RECOVERYCONFLICT:
+					pgstat_recv_recoveryconflict((PgStat_MsgRecoveryConflict *) &msg, len);
+					break;
+
 				default:
 					break;
 			}
@@ -3129,6 +3154,11 @@ pgstat_get_db_entry(Oid databaseid, bool create)
 		result->n_tuples_updated = 0;
 		result->n_tuples_deleted = 0;
 		result->last_autovac_time = 0;
+		result->n_conflict_tablespace = 0;
+		result->n_conflict_lock = 0;
+		result->n_conflict_snapshot = 0;
+		result->n_conflict_bufferpin = 0;
+		result->n_conflict_startup_deadlock = 0;
 
 		memset(&hash_ctl, 0, sizeof(hash_ctl));
 		hash_ctl.keysize = sizeof(Oid);
@@ -4204,6 +4234,45 @@ pgstat_recv_bgwriter(PgStat_MsgBgWriter *msg, int len)
 }
 
 /* ----------
+ * pgstat_recv_recoveryconflict() -
+ *
+ *  Process as RECOVERYCONFLICT message.
+ * ----------
+ */
+static void
+pgstat_recv_recoveryconflict(PgStat_MsgRecoveryConflict *msg, int len)
+{
+	PgStat_StatDBEntry *dbentry;
+	dbentry = pgstat_get_db_entry(msg->m_databaseid, true);
+
+	switch (msg->m_reason)
+	{
+		case PROCSIG_RECOVERY_CONFLICT_DATABASE:
+			/*
+			 * Since we drop the information about the database as soon
+			 * as it replicates, there is no point in counting these
+			 * conflicts.
+			 */
+			break;
+		case PROCSIG_RECOVERY_CONFLICT_TABLESPACE:
+			dbentry->n_conflict_tablespace++;
+			break;
+		case PROCSIG_RECOVERY_CONFLICT_LOCK:
+			dbentry->n_conflict_lock++;
+			break;
+		case PROCSIG_RECOVERY_CONFLICT_SNAPSHOT:
+			dbentry->n_conflict_snapshot++;
+			break;
+		case PROCSIG_RECOVERY_CONFLICT_BUFFERPIN:
+			dbentry->n_conflict_bufferpin++;
+			break;
+		case PROCSIG_RECOVERY_CONFLICT_STARTUP_DEADLOCK:
+			dbentry->n_conflict_startup_deadlock++;
+			break;
+	}
+}
+
+/* ----------
  * pgstat_recv_funcstat() -
  *
  *	Count what the backend has done.
diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c
index 4bfc674f8fa..b227e6ce4f8 100644
--- a/src/backend/tcop/postgres.c
+++ b/src/backend/tcop/postgres.c
@@ -2903,15 +2903,21 @@ ProcessInterrupts(void)
 					(errcode(ERRCODE_ADMIN_SHUTDOWN),
 					 errmsg("terminating autovacuum process due to administrator command")));
 		else if (RecoveryConflictPending && RecoveryConflictRetryable)
+		{
+			pgstat_report_recovery_conflict(RecoveryConflictReason);
 			ereport(FATAL,
 					(errcode(ERRCODE_T_R_SERIALIZATION_FAILURE),
 			  errmsg("terminating connection due to conflict with recovery"),
 					 errdetail_recovery_conflict()));
+		}
 		else if (RecoveryConflictPending)
+		{
+			pgstat_report_recovery_conflict(RecoveryConflictReason);
 			ereport(FATAL,
 					(errcode(ERRCODE_ADMIN_SHUTDOWN),
 			  errmsg("terminating connection due to conflict with recovery"),
 					 errdetail_recovery_conflict()));
+		}
 		else
 			ereport(FATAL,
 					(errcode(ERRCODE_ADMIN_SHUTDOWN),
@@ -2956,6 +2962,7 @@ ProcessInterrupts(void)
 			RecoveryConflictPending = false;
 			DisableNotifyInterrupt();
 			DisableCatchupInterrupt();
+			pgstat_report_recovery_conflict(RecoveryConflictReason);
 			if (DoingCommandRead)
 				ereport(FATAL,
 						(errcode(ERRCODE_T_R_SERIALIZATION_FAILURE),
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index d357b5c8b5d..a95ba8b3eb3 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -71,6 +71,12 @@ extern Datum pg_stat_get_db_tuples_fetched(PG_FUNCTION_ARGS);
 extern Datum pg_stat_get_db_tuples_inserted(PG_FUNCTION_ARGS);
 extern Datum pg_stat_get_db_tuples_updated(PG_FUNCTION_ARGS);
 extern Datum pg_stat_get_db_tuples_deleted(PG_FUNCTION_ARGS);
+extern Datum pg_stat_get_db_conflict_tablespace(PG_FUNCTION_ARGS);
+extern Datum pg_stat_get_db_conflict_lock(PG_FUNCTION_ARGS);
+extern Datum pg_stat_get_db_conflict_snapshot(PG_FUNCTION_ARGS);
+extern Datum pg_stat_get_db_conflict_bufferpin(PG_FUNCTION_ARGS);
+extern Datum pg_stat_get_db_conflict_startup_deadlock(PG_FUNCTION_ARGS);
+extern Datum pg_stat_get_db_conflict_all(PG_FUNCTION_ARGS);
 
 extern Datum pg_stat_get_bgwriter_timed_checkpoints(PG_FUNCTION_ARGS);
 extern Datum pg_stat_get_bgwriter_requested_checkpoints(PG_FUNCTION_ARGS);
@@ -1130,6 +1136,101 @@ pg_stat_get_db_tuples_deleted(PG_FUNCTION_ARGS)
 }
 
 Datum
+pg_stat_get_db_conflict_tablespace(PG_FUNCTION_ARGS)
+{
+	Oid			dbid = PG_GETARG_OID(0);
+	int64		result;
+	PgStat_StatDBEntry *dbentry;
+
+	if ((dbentry = pgstat_fetch_stat_dbentry(dbid)) == NULL)
+		result = 0;
+	else
+		result = (int64) (dbentry->n_conflict_tablespace);
+
+	PG_RETURN_INT64(result);
+}
+
+Datum
+pg_stat_get_db_conflict_lock(PG_FUNCTION_ARGS)
+{
+	Oid			dbid = PG_GETARG_OID(0);
+	int64		result;
+	PgStat_StatDBEntry *dbentry;
+
+	if ((dbentry = pgstat_fetch_stat_dbentry(dbid)) == NULL)
+		result = 0;
+	else
+		result = (int64) (dbentry->n_conflict_lock);
+
+	PG_RETURN_INT64(result);
+}
+
+Datum
+pg_stat_get_db_conflict_snapshot(PG_FUNCTION_ARGS)
+{
+	Oid			dbid = PG_GETARG_OID(0);
+	int64		result;
+	PgStat_StatDBEntry *dbentry;
+
+	if ((dbentry = pgstat_fetch_stat_dbentry(dbid)) == NULL)
+		result = 0;
+	else
+		result = (int64) (dbentry->n_conflict_snapshot);
+
+	PG_RETURN_INT64(result);
+}
+
+Datum
+pg_stat_get_db_conflict_bufferpin(PG_FUNCTION_ARGS)
+{
+	Oid			dbid = PG_GETARG_OID(0);
+	int64		result;
+	PgStat_StatDBEntry *dbentry;
+
+	if ((dbentry = pgstat_fetch_stat_dbentry(dbid)) == NULL)
+		result = 0;
+	else
+		result = (int64) (dbentry->n_conflict_bufferpin);
+
+	PG_RETURN_INT64(result);
+}
+
+Datum
+pg_stat_get_db_conflict_startup_deadlock(PG_FUNCTION_ARGS)
+{
+	Oid			dbid = PG_GETARG_OID(0);
+	int64		result;
+	PgStat_StatDBEntry *dbentry;
+
+	if ((dbentry = pgstat_fetch_stat_dbentry(dbid)) == NULL)
+		result = 0;
+	else
+		result = (int64) (dbentry->n_conflict_startup_deadlock);
+
+	PG_RETURN_INT64(result);
+}
+
+Datum
+pg_stat_get_db_conflict_all(PG_FUNCTION_ARGS)
+{
+	Oid			dbid = PG_GETARG_OID(0);
+	int64		result;
+	PgStat_StatDBEntry *dbentry;
+
+	if ((dbentry = pgstat_fetch_stat_dbentry(dbid)) == NULL)
+		result = 0;
+	else
+		result = (int64) (
+			dbentry->n_conflict_tablespace +
+			dbentry->n_conflict_lock +
+			dbentry->n_conflict_snapshot +
+			dbentry->n_conflict_bufferpin +
+			dbentry->n_conflict_startup_deadlock);
+
+	PG_RETURN_INT64(result);
+}
+
+Datum
 pg_stat_get_bgwriter_timed_checkpoints(PG_FUNCTION_ARGS)
 {
 	PG_RETURN_INT64(pgstat_fetch_global()->timed_checkpoints);