Add pg_stat_database counters for sessions and session time

This add counters for number of sessions, the different kind of session
termination types, and timers for how much time is spent in active vs
idle in a database to pg_stat_database.

Internally this also renames the parameter "force" to disconnect. This
was the only use-case for the parameter before, so repurposing it to
this mroe narrow usecase makes things cleaner than inventing something
new.

Author: Laurenz Albe
Reviewed-By: Magnus Hagander, Soumyadeep Chakraborty, Masahiro Ikeda
Discussion: https://postgr.es/m/b07e1f9953701b90c66ed368656f2aef40cac4fb.camel@cybertec.at

1 files changed, 130 insertions, 4 deletions

diff --git a/src/backend/postmaster/pgstat.c b/src/backend/postmaster/pgstat.c
index 3f24a33ef1d..f75b52719dd 100644
--- a/src/backend/postmaster/pgstat.c
+++ b/src/backend/postmaster/pgstat.c
@@ -258,6 +258,9 @@ static int	pgStatXactCommit = 0;
 static int	pgStatXactRollback = 0;
 PgStat_Counter pgStatBlockReadTime = 0;
 PgStat_Counter pgStatBlockWriteTime = 0;
+static PgStat_Counter pgStatActiveTime = 0;
+static PgStat_Counter pgStatTransactionIdleTime = 0;
+SessionEndType pgStatSessionEndCause = DISCONNECT_NORMAL;
 
 /* Record that's written to 2PC state file when pgstat state is persisted */
 typedef struct TwoPhasePgStatRecord
@@ -343,6 +346,7 @@ static void pgstat_send_tabstat(PgStat_MsgTabstat *tsmsg);
 static void pgstat_send_funcstats(void);
 static void pgstat_send_slru(void);
 static HTAB *pgstat_collect_oids(Oid catalogid, AttrNumber anum_oid);
+static void pgstat_send_connstats(bool disconnect, TimestampTz last_report);
 
 static PgStat_TableStatus *get_tabstat_entry(Oid rel_id, bool isshared);
 
@@ -378,6 +382,7 @@ static void pgstat_recv_funcpurge(PgStat_MsgFuncpurge *msg, int len);
 static void pgstat_recv_recoveryconflict(PgStat_MsgRecoveryConflict *msg, int len);
 static void pgstat_recv_deadlock(PgStat_MsgDeadlock *msg, int len);
 static void pgstat_recv_checksum_failure(PgStat_MsgChecksumFailure *msg, int len);
+static void pgstat_recv_connstat(PgStat_MsgConn *msg, int len);
 static void pgstat_recv_replslot(PgStat_MsgReplSlot *msg, int len);
 static void pgstat_recv_tempfile(PgStat_MsgTempFile *msg, int len);
 
@@ -855,10 +860,14 @@ allow_immediate_pgstat_restart(void)
  *	per-table and function usage statistics to the collector.  Note that this
  *	is called only when not within a transaction, so it is fair to use
  *	transaction stop time as an approximation of current time.
+ *
+ *	"disconnect" is "true" only for the last call before the backend
+ *	exits.  This makes sure that no data is lost and that interrupted
+ *	sessions are reported correctly.
  * ----------
  */
 void
-pgstat_report_stat(bool force)
+pgstat_report_stat(bool disconnect)
 {
 	/* we assume this inits to all zeroes: */
 	static const PgStat_TableCounts all_zeroes;
@@ -873,17 +882,22 @@ pgstat_report_stat(bool force)
 	/* Don't expend a clock check if nothing to do */
 	if ((pgStatTabList == NULL || pgStatTabList->tsa_used == 0) &&
 		pgStatXactCommit == 0 && pgStatXactRollback == 0 &&
-		!have_function_stats)
+		!have_function_stats && !disconnect)
 		return;
 
 	/*
 	 * Don't send a message unless it's been at least PGSTAT_STAT_INTERVAL
-	 * msec since we last sent one, or the caller wants to force stats out.
+	 * msec since we last sent one, or the backend is about to exit.
 	 */
 	now = GetCurrentTransactionStopTimestamp();
-	if (!force &&
+	if (!disconnect &&
 		!TimestampDifferenceExceeds(last_report, now, PGSTAT_STAT_INTERVAL))
 		return;
+
+	/* for backends, send connection statistics */
+	if (MyBackendType == B_BACKEND)
+		pgstat_send_connstats(disconnect, last_report);
+
 	last_report = now;
 
 	/*
@@ -1352,6 +1366,48 @@ pgstat_drop_relation(Oid relid)
 
 
 /* ----------
+ * pgstat_send_connstats() -
+ *
+ *	Tell the collector about session statistics.
+ *	The parameter "disconnect" will be true when the backend exits.
+ *	"last_report" is the last time we were called (0 if never).
+ * ----------
+ */
+static void
+pgstat_send_connstats(bool disconnect, TimestampTz last_report)
+{
+	PgStat_MsgConn msg;
+	long		secs;
+	int			usecs;
+
+	if (pgStatSock == PGINVALID_SOCKET || !pgstat_track_counts)
+		return;
+
+	pgstat_setheader(&msg.m_hdr, PGSTAT_MTYPE_CONNECTION);
+	msg.m_databaseid = MyDatabaseId;
+
+	/* session time since the last report */
+	TimestampDifference(((last_report == 0) ? MyStartTimestamp : last_report),
+						GetCurrentTimestamp(),
+						&secs, &usecs);
+	msg.m_session_time = secs * 1000000 + usecs;
+
+	msg.m_disconnect = disconnect ? pgStatSessionEndCause : DISCONNECT_NOT_YET;
+
+	msg.m_active_time = pgStatActiveTime;
+	pgStatActiveTime = 0;
+
+	msg.m_idle_in_xact_time = pgStatTransactionIdleTime;
+	pgStatTransactionIdleTime = 0;
+
+	/* report a new session only the first time */
+	msg.m_count = (last_report == 0) ? 1 : 0;
+
+	pgstat_send(&msg, sizeof(PgStat_MsgConn));
+}
+
+
+/* ----------
  * pgstat_reset_counters() -
  *
  *	Tell the statistics collector to reset counters for our database.
@@ -3349,6 +3405,30 @@ pgstat_report_activity(BackendState state, const char *cmd_str)
 	current_timestamp = GetCurrentTimestamp();
 
 	/*
+	 * If the state has changed from "active" or "idle in transaction",
+	 * calculate the duration.
+	 */
+	if ((beentry->st_state == STATE_RUNNING ||
+		 beentry->st_state == STATE_FASTPATH ||
+		 beentry->st_state == STATE_IDLEINTRANSACTION ||
+		 beentry->st_state == STATE_IDLEINTRANSACTION_ABORTED) &&
+		state != beentry->st_state)
+	{
+		long		secs;
+		int			usecs;
+
+		TimestampDifference(beentry->st_state_start_timestamp,
+							current_timestamp,
+							&secs, &usecs);
+
+		if (beentry->st_state == STATE_RUNNING ||
+			beentry->st_state == STATE_FASTPATH)
+			pgStatActiveTime += secs * 1000000 + usecs;
+		else
+			pgStatTransactionIdleTime += secs * 1000000 + usecs;
+	}
+
+	/*
 	 * Now update the status entry
 	 */
 	PGSTAT_BEGIN_WRITE_ACTIVITY(beentry);
@@ -4919,6 +4999,10 @@ PgstatCollectorMain(int argc, char *argv[])
 					pgstat_recv_replslot(&msg.msg_replslot, len);
 					break;
 
+				case PGSTAT_MTYPE_CONNECTION:
+					pgstat_recv_connstat(&msg.msg_conn, len);
+					break;
+
 				default:
 					break;
 			}
@@ -4993,6 +5077,13 @@ reset_dbentry_counters(PgStat_StatDBEntry *dbentry)
 	dbentry->last_checksum_failure = 0;
 	dbentry->n_block_read_time = 0;
 	dbentry->n_block_write_time = 0;
+	dbentry->n_sessions = 0;
+	dbentry->total_session_time = 0;
+	dbentry->total_active_time = 0;
+	dbentry->total_idle_in_xact_time = 0;
+	dbentry->n_sessions_abandoned = 0;
+	dbentry->n_sessions_fatal = 0;
+	dbentry->n_sessions_killed = 0;
 
 	dbentry->stat_reset_timestamp = GetCurrentTimestamp();
 	dbentry->stats_timestamp = 0;
@@ -6945,6 +7036,41 @@ pgstat_recv_replslot(PgStat_MsgReplSlot *msg, int len)
 }
 
 /* ----------
+ * pgstat_recv_connstat() -
+ *
+ *  Process connection information.
+ * ----------
+ */
+static void
+pgstat_recv_connstat(PgStat_MsgConn *msg, int len)
+{
+	PgStat_StatDBEntry *dbentry;
+
+	dbentry = pgstat_get_db_entry(msg->m_databaseid, true);
+
+	dbentry->n_sessions += msg->m_count;
+	dbentry->total_session_time += msg->m_session_time;
+	dbentry->total_active_time += msg->m_active_time;
+	dbentry->total_idle_in_xact_time += msg->m_idle_in_xact_time;
+	switch (msg->m_disconnect)
+	{
+		case DISCONNECT_NOT_YET:
+		case DISCONNECT_NORMAL:
+			/* we don't collect these */
+			break;
+		case DISCONNECT_CLIENT_EOF:
+			dbentry->n_sessions_abandoned++;
+			break;
+		case DISCONNECT_FATAL:
+			dbentry->n_sessions_fatal++;
+			break;
+		case DISCONNECT_KILLED:
+			dbentry->n_sessions_killed++;
+			break;
+	}
+}
+
+/* ----------
  * pgstat_recv_tempfile() -
  *
  *	Process a TEMPFILE message.