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

3 files changed, 72 insertions, 1 deletions

diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h
index 747135dab46..385d108c292 100644
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -53,6 +53,6 @@
  */
 
 /*							yyyymmddN */
-#define CATALOG_VERSION_NO	202101131
+#define CATALOG_VERSION_NO	202101171
 
 #endif
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index d27336adcd9..dd64c3bd60b 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5440,6 +5440,38 @@
   proname => 'pg_stat_get_db_blk_write_time', provolatile => 's',
   proparallel => 'r', prorettype => 'float8', proargtypes => 'oid',
   prosrc => 'pg_stat_get_db_blk_write_time' },
+{ oid => '9575', descr => 'statistics: session time, in milliseconds',
+  proname => 'pg_stat_get_db_session_time', provolatile => 's',
+  proparallel => 'r', prorettype => 'float8', proargtypes => 'oid',
+  prosrc => 'pg_stat_get_db_session_time' },
+{ oid => '9576', descr => 'statistics: session active time, in milliseconds',
+  proname => 'pg_stat_get_db_active_time', provolatile => 's',
+  proparallel => 'r', prorettype => 'float8', proargtypes => 'oid',
+  prosrc => 'pg_stat_get_db_active_time' },
+{ oid => '9577',
+  descr => 'statistics: session idle in transaction time, in milliseconds',
+  proname => 'pg_stat_get_db_idle_in_transaction_time', provolatile => 's',
+  proparallel => 'r', prorettype => 'float8', proargtypes => 'oid',
+  prosrc => 'pg_stat_get_db_idle_in_transaction_time' },
+{ oid => '9578', descr => 'statistics: total number of sessions',
+  proname => 'pg_stat_get_db_sessions', provolatile => 's', proparallel => 'r',
+  prorettype => 'int8', proargtypes => 'oid',
+  prosrc => 'pg_stat_get_db_sessions' },
+{ oid => '9579',
+  descr => 'statistics: number of sessions disconnected by the client closing the network connection',
+  proname => 'pg_stat_get_db_sessions_abandoned', provolatile => 's',
+  proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
+  prosrc => 'pg_stat_get_db_sessions_abandoned' },
+{ oid => '9580',
+  descr => 'statistics: number of sessions disconnected by fatal errors',
+  proname => 'pg_stat_get_db_sessions_fatal', provolatile => 's',
+  proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
+  prosrc => 'pg_stat_get_db_sessions_fatal' },
+{ oid => '9581',
+  descr => 'statistics: number of sessions killed by administrative action',
+  proname => 'pg_stat_get_db_sessions_killed', provolatile => 's',
+  proparallel => 'r', prorettype => 'int8', proargtypes => 'oid',
+  prosrc => 'pg_stat_get_db_sessions_killed' },
 { oid => '3195', descr => 'statistics: information about WAL archiver',
   proname => 'pg_stat_get_archiver', proisstrict => 'f', provolatile => 's',
   proparallel => 'r', prorettype => 'record', proargtypes => '',
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index c38b6897101..a384f6eb56f 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -41,6 +41,16 @@ typedef enum TrackFunctionsLevel
 	TRACK_FUNC_ALL
 }			TrackFunctionsLevel;
 
+/* Values to track the cause of session termination */
+typedef enum SessionEndType
+{
+	DISCONNECT_NOT_YET,			/* still active */
+	DISCONNECT_NORMAL,
+	DISCONNECT_CLIENT_EOF,
+	DISCONNECT_FATAL,
+	DISCONNECT_KILLED
+} SessionEndType;
+
 /* ----------
  * The types of backend -> collector messages
  * ----------
@@ -71,6 +81,7 @@ typedef enum StatMsgType
 	PGSTAT_MTYPE_DEADLOCK,
 	PGSTAT_MTYPE_CHECKSUMFAILURE,
 	PGSTAT_MTYPE_REPLSLOT,
+	PGSTAT_MTYPE_CONNECTION,
 } StatMsgType;
 
 /* ----------
@@ -622,6 +633,21 @@ typedef struct PgStat_MsgChecksumFailure
 	TimestampTz m_failure_time;
 } PgStat_MsgChecksumFailure;
 
+/* ----------
+ * PgStat_MsgConn			Sent by the backend to update connection statistics.
+ * ----------
+ */
+typedef struct PgStat_MsgConn
+{
+	PgStat_MsgHdr m_hdr;
+	Oid			m_databaseid;
+	PgStat_Counter m_count;
+	PgStat_Counter m_session_time;
+	PgStat_Counter m_active_time;
+	PgStat_Counter m_idle_in_xact_time;
+	SessionEndType m_disconnect;
+} PgStat_MsgConn;
+
 
 /* ----------
  * PgStat_Msg					Union over all possible messages.
@@ -654,6 +680,7 @@ typedef union PgStat_Msg
 	PgStat_MsgTempFile msg_tempfile;
 	PgStat_MsgChecksumFailure msg_checksumfailure;
 	PgStat_MsgReplSlot msg_replslot;
+	PgStat_MsgConn msg_conn;
 } PgStat_Msg;
 
 
@@ -696,6 +723,13 @@ typedef struct PgStat_StatDBEntry
 	TimestampTz last_checksum_failure;
 	PgStat_Counter n_block_read_time;	/* times in microseconds */
 	PgStat_Counter n_block_write_time;
+	PgStat_Counter n_sessions;
+	PgStat_Counter total_session_time;
+	PgStat_Counter total_active_time;
+	PgStat_Counter total_idle_in_xact_time;
+	PgStat_Counter n_sessions_abandoned;
+	PgStat_Counter n_sessions_fatal;
+	PgStat_Counter n_sessions_killed;
 
 	TimestampTz stat_reset_timestamp;
 	TimestampTz stats_timestamp;	/* time of db stats file update */
@@ -1354,6 +1388,11 @@ extern PgStat_MsgWal WalStats;
 extern PgStat_Counter pgStatBlockReadTime;
 extern PgStat_Counter pgStatBlockWriteTime;
 
+/*
+ * Updated by the traffic cop and in errfinish()
+ */
+extern SessionEndType pgStatSessionEndCause;
+
 /* ----------
  * Functions called from postmaster
  * ----------