Subject: Re: Add connection active, idle time to pg_stat_activity

From: Sergey Dudoladov <[email-masked]>
Date: Wed, 1 Feb 2023 21:45:52 +0100

Hello hackers,

I've sketched the first version of a patch to add pg_stat_session.
Please review this early version.

Regards,
Sergey.
From 31f781ecd69fc42aaadd9bcdbebaf8f72449946c Mon Sep 17 00:00:00 2001
From: Sergey Dudoladov <[email-masked]>
Date: Tue, 22 Nov 2022 09:23:32 +0100
Subject: [PATCH] Add pg_stat_session

Author: Sergey Dudoladov

Adds pg_stat_session view to track statistics accumulated during
 lifetime of a session.

Reviewed-by: Aleksander Alekseev, Bertrand Drouvot, and Atsushi
Torikoshi

Discussion:
https://www.postgresql.org/message-id/flat/CA%2BFpmFcJF0vwi-SWW0wYO-c-FbhyawLq4tCpRDCJJ8Bq%3Dja-gA%40mail.gmail.com
---
 doc/src/sgml/monitoring.sgml                | 153 ++++++++++++++++++++
 src/backend/catalog/system_views.sql        |  15 ++
 src/backend/utils/activity/backend_status.c |  58 ++++++--
 src/backend/utils/adt/pgstatfuncs.c         |  70 +++++++++
 src/include/catalog/pg_proc.dat             |   9 ++
 src/include/utils/backend_status.h          |  37 +++++
 src/test/regress/expected/rules.out         |  12 ++
 7 files changed, 345 insertions(+), 9 deletions(-)

diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 1756f1a4b6..38cc29810a 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -414,6 +414,20 @@ postgres   27093  0.0  0.0  30096  2752 ?        Ss   11:34   0:00 postgres: ser
        See <xref linkend='copy-progress-reporting'/>.
       </entry>
      </row>
+
+     <row>
+      <entry>
+       <structname>pg_stat_session</structname>
+       <indexterm><primary>pg_stat_session</primary></indexterm>
+      </entry>
+      <entry>
+       One row per server process, showing information related to
+       the currently accumulated activity of that process, such as time spent in
+       a certain state.
+       See <link linkend="monitoring-pg-stat-session-view">
+       <structname>pg_stat_session</structname></link> for details.
+      </entry>
+     </row>
     </tbody>
    </tgroup>
   </table>
@@ -5315,6 +5329,129 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i
    </tgroup>
   </table>
 
+  <table id="monitoring-pg-stat-session-view" xreflabel="pg_stat_session">
+   <title><structname>pg_stat_session</structname> View</title>
+   <tgroup cols="1">
+    <thead>
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       Column Type
+      </para>
+      <para>
+       Description
+      </para></entry>
+     </row>
+    </thead>
+
+    <tbody>
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>pid</structfield> <type>integer</type>
+      </para>
+      <para>
+       Process ID of this backend.
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>total_running_time</structfield> <type>double precision</type>
+      </para>
+      <para>
+       Time in milliseconds this backend spent in the <literal>running</literal> state.
+      </para></entry>
+     </row>
+
+    <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>total_running_count</structfield> <type>bigint</type>
+      </para>
+      <para>
+       Number of times this backend switched to the <literal>running</literal> state.
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>total_idle_time</structfield> <type>double precision</type>
+      </para>
+      <para>
+       Time in milliseconds this backend spent in the <literal>idle</literal> state.
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>total_idle_count</structfield> <type>bigint</type>
+      </para>
+      <para>
+       Number of times this backend switched to the <literal>idle</literal> state.
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>total_transaction_idle_time</structfield> <type>double precision</type>
+      </para>
+      <para>
+       Time in milliseconds this backend spent in the <literal>idle in transaction</literal>
+       state.
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>total_transaction_idle_count</structfield> <type>bigint</type>
+      </para>
+      <para>
+       Number of times this backend switched to the <literal>idle in transaction</literal>
+       state.
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>total_transaction_idle_aborted_time</structfield> <type>double precision</type>
+      </para>
+      <para>
+       Time in milliseconds this backend spent in the <literal>idle in transaction (aborted)</literal> 
+       state.
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>total_transaction_idle_aborted_count</structfield> <type>bigint</type>
+      </para>
+      <para>
+       Number of times this backend switched to the <literal>idle in transaction (aborted)</literal>
+       state.
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>total_fastpath_time</structfield> <type>double precision</type>
+      </para>
+      <para>
+       Time in milliseconds this backend spent in the <literal>fastpath</literal> state.
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>total_fastpath_count</structfield> <type>bigint</type>
+      </para>
+      <para>
+       Number of times this backend switched to the <literal>fastpath</literal>
+       state.
+      </para></entry>
+     </row>
+
+    </tbody>
+   </tgroup>
+  </table>
+
  </sect2>
 
  <sect2 id="monitoring-stats-functions">
@@ -5382,6 +5519,22 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i
        </para></entry>
       </row>
 
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>pg_stat_get_session</primary>
+        </indexterm>
+        <function>pg_stat_get_session</function> ( <type>integer</type> )
+        <returnvalue>setof record</returnvalue>
+       </para>
+       <para>
+        Returns a record of information about the backend with the specified
+        process ID, or one record for each active backend in the system
+        if <literal>NULL</literal> is specified.  The fields returned are a
+        subset of those in the <structname>pg_stat_session</structname> view.
+       </para></entry>
+      </row>
+
       <row>
        <entry role="func_table_entry"><para role="func_signature">
         <indexterm>
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 8608e3fa5b..8f68a6ea00 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -870,6 +870,21 @@ CREATE VIEW pg_stat_activity AS
         LEFT JOIN pg_database AS D ON (S.datid = D.oid)
         LEFT JOIN pg_authid AS U ON (S.usesysid = U.oid);
 
+CREATE VIEW pg_stat_session AS
+    SELECT
+            s.pid,
+            s.total_running_time,
+            s.total_running_count,
+            s.total_idle_time,
+            s.total_idle_count,
+            s.total_transaction_idle_time,
+            s.total_transaction_idle_count,
+            s.total_transaction_idle_aborted_time,
+            s.total_transaction_idle_aborted_count,
+            s.total_fastpath_time,
+            s.total_fastpath_count
+     FROM pg_stat_get_session(NULL) as s;
+
 CREATE VIEW pg_stat_replication AS
     SELECT
             S.pid,
diff --git a/src/backend/utils/activity/backend_status.c b/src/backend/utils/activity/backend_status.c
index 608d01ea0d..804b91cf9f 100644
--- a/src/backend/utils/activity/backend_status.c
+++ b/src/backend/utils/activity/backend_status.c
@@ -338,6 +338,8 @@ pgstat_bestart(void)
 	lbeentry.st_xact_start_timestamp = 0;
 	lbeentry.st_databaseid = MyDatabaseId;
 
+	MemSet(&lbeentry.st_session, 0, sizeof(lbeentry.st_session));
+
 	/* We have userid for client-backends, wal-sender and bgworker processes */
 	if (lbeentry.st_backendType == B_BACKEND
 		|| lbeentry.st_backendType == B_WAL_SENDER
@@ -525,6 +527,9 @@ pgstat_report_activity(BackendState state, const char *cmd_str)
 	TimestampTz current_timestamp;
 	int			len = 0;
 
+	PgBackendSessionStatus st_session_diff;
+	MemSet(&st_session_diff, 0, sizeof(st_session_diff));
+
 	TRACE_POSTGRESQL_STATEMENT_STATUS(cmd_str);
 
 	if (!beentry)
@@ -550,6 +555,7 @@ pgstat_report_activity(BackendState state, const char *cmd_str)
 			beentry->st_xact_start_timestamp = 0;
 			beentry->st_query_id = UINT64CONST(0);
 			proc->wait_event_info = 0;
+			MemSet(&beentry->st_session, 0, sizeof(beentry->st_session));
 			PGSTAT_END_WRITE_ACTIVITY(beentry);
 		}
 		return;
@@ -572,27 +578,46 @@ 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 the state has changed, update per-database and per-session counters.
 	 */
-	if ((beentry->st_state == STATE_RUNNING ||
-		 beentry->st_state == STATE_FASTPATH ||
-		 beentry->st_state == STATE_IDLEINTRANSACTION ||
-		 beentry->st_state == STATE_IDLEINTRANSACTION_ABORTED) &&
+	if ((PGSTAT_IS_ACTIVE(beentry) ||
+		 PGSTAT_IS_IDLEINTRANSACTION(beentry) ||
+		 PGSTAT_IS_IDLEINTRANSACTION_ABORTED(beentry) || 
+		 PGSTAT_IS_IDLE(beentry)) &&
 		state != beentry->st_state)
 	{
 		long		secs;
 		int			usecs;
+		int64		usecs_diff;
 
 		TimestampDifference(beentry->st_state_start_timestamp,
 							current_timestamp,
 							&secs, &usecs);
+		usecs_diff = secs * 1000000 + usecs;
 
-		if (beentry->st_state == STATE_RUNNING ||
-			beentry->st_state == STATE_FASTPATH)
+		/* Keep statistics for pg_stat_database intact */
+		if (PGSTAT_IS_ACTIVE(beentry))
 			pgstat_count_conn_active_time((PgStat_Counter) secs * 1000000 + usecs);
-		else
+		else if (PGSTAT_IS_IDLEINTRANSACTION(beentry) ||
+				 PGSTAT_IS_IDLEINTRANSACTION_ABORTED(beentry))
 			pgstat_count_conn_txn_idle_time((PgStat_Counter) secs * 1000000 + usecs);
+
+		if (PGSTAT_IS_RUNNING(beentry)) {
+			st_session_diff.total_running_time = usecs_diff;
+			st_session_diff.total_running_count += 1;
+		} else if (PGSTAT_IS_IDLE(beentry)){
+			st_session_diff.total_idle_time = usecs_diff;
+			st_session_diff.total_idle_count += 1;
+		} else if (PGSTAT_IS_IDLEINTRANSACTION(beentry)){
+			st_session_diff.total_transaction_idle_time = usecs_diff;
+			st_session_diff.total_transaction_idle_count += 1;
+		} else if (PGSTAT_IS_IDLEINTRANSACTION_ABORTED(beentry)){
+			st_session_diff.total_transaction_idle_aborted_time = usecs_diff;
+			st_session_diff.total_transaction_idle_aborted_count += 1;
+		} else if (PGSTAT_IS_FASTPATH(beentry)){
+			st_session_diff.total_fastpath_time = usecs_diff;
+			st_session_diff.total_fastpath_count += 1;
+		}
 	}
 
 	/*
@@ -618,6 +643,21 @@ pgstat_report_activity(BackendState state, const char *cmd_str)
 		beentry->st_activity_start_timestamp = start_timestamp;
 	}
 
+	beentry->st_session.total_running_time += st_session_diff.total_running_time;
+	beentry->st_session.total_running_count += st_session_diff.total_running_count;
+
+	beentry->st_session.total_idle_time += st_session_diff.total_idle_time;
+	beentry->st_session.total_idle_count += st_session_diff.total_idle_count;
+
+	beentry->st_session.total_transaction_idle_time += st_session_diff.total_transaction_idle_time;
+	beentry->st_session.total_transaction_idle_count += st_session_diff.total_transaction_idle_count;
+
+	beentry->st_session.total_transaction_idle_aborted_time += st_session_diff.total_transaction_idle_aborted_time;
+	beentry->st_session.total_transaction_idle_aborted_count += st_session_diff.total_transaction_idle_aborted_count;
+
+	beentry->st_session.total_fastpath_time += st_session_diff.total_fastpath_time;
+	beentry->st_session.total_fastpath_count += st_session_diff.total_fastpath_count;
+
 	PGSTAT_END_WRITE_ACTIVITY(beentry);
 }
 
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 6737493402..3a76a5bf06 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -297,6 +297,76 @@ pg_stat_get_progress_info(PG_FUNCTION_ARGS)
 	return (Datum) 0;
 }
 
+/*
+ * Returns accumulated statistics of current PG backends.
+ */
+Datum
+pg_stat_get_session(PG_FUNCTION_ARGS)
+{
+#define PG_STAT_GET_SESSION_COLS	11
+	int			num_backends = pgstat_fetch_stat_numbackends();
+	int			curr_backend;
+	int			pid = PG_ARGISNULL(0) ? -1 : PG_GETARG_INT32(0);
+	ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
+
+	InitMaterializedSRF(fcinfo, 0);
+
+	/* 1-based index */
+	for (curr_backend = 1; curr_backend <= num_backends; curr_backend++)
+	{
+		/* for each row */
+		Datum		values[PG_STAT_GET_SESSION_COLS] = {0};
+		bool		nulls[PG_STAT_GET_SESSION_COLS] = {0};
+		LocalPgBackendStatus *local_beentry;
+		PgBackendStatus *beentry;
+
+		/* Get the next one in the list */
+		local_beentry = pgstat_fetch_stat_local_beentry(curr_backend);
+		beentry = &local_beentry->backendStatus;
+
+		/* If looking for specific PID, ignore all the others */
+		if (pid != -1 && beentry->st_procpid != pid)
+			continue;
+
+		/* Values available to all callers */
+		values[0] = Int32GetDatum(beentry->st_procpid);
+
+		/* Values only available to role member or pg_read_all_stats */
+		if (HAS_PGSTAT_PERMISSIONS(beentry->st_userid)){
+			/* convert to msec */
+			values[1] = Float8GetDatum(beentry->st_session.total_running_time / 1000.0);
+			values[2] = Int64GetDatum(beentry->st_session.total_running_count);
+			values[3] = Float8GetDatum(beentry->st_session.total_idle_time / 1000.0);
+			values[4] = Int64GetDatum(beentry->st_session.total_idle_count);
+			values[5] = Float8GetDatum(beentry->st_session.total_transaction_idle_time / 1000.0);
+			values[6] = Int64GetDatum(beentry->st_session.total_transaction_idle_count);
+			values[7] = Float8GetDatum(beentry->st_session.total_transaction_idle_aborted_time / 1000.0);
+			values[8] = Int64GetDatum(beentry->st_session.total_transaction_idle_aborted_count);
+			values[9] = Float8GetDatum(beentry->st_session.total_fastpath_time / 1000.0);
+			values[10] = Int64GetDatum(beentry->st_session.total_fastpath_count);
+		} else {
+			nulls[1] = true;
+			nulls[2] = true;
+			nulls[3] = true;
+			nulls[4] = true;
+			nulls[5] = true;
+			nulls[6] = true;
+			nulls[7] = true;
+			nulls[8] = true;
+			nulls[9] = true;
+			nulls[10] = true;
+		}
+
+		tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc, values, nulls);
+
+		/* If only a single backend was requested, and we found it, break. */
+		if (pid != -1)
+			break;
+	}
+
+	return (Datum) 0;
+}
+
 /*
  * Returns activity of PG backends.
  */
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index c0f2a8a77c..4c063e2422 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5408,6 +5408,15 @@
   proargmodes => '{i,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o}',
   proargnames => '{pid,datid,pid,usesysid,application_name,state,query,wait_event_type,wait_event,xact_start,query_start,backend_start,state_change,client_addr,client_hostname,client_port,backend_xid,backend_xmin,backend_type,ssl,sslversion,sslcipher,sslbits,ssl_client_dn,ssl_client_serial,ssl_issuer_dn,gss_auth,gss_princ,gss_enc,leader_pid,query_id}',
   prosrc => 'pg_stat_get_activity' },
+{ oid => '2173',
+  descr => 'statistics: cumulative information about currently active backends',
+  proname => 'pg_stat_get_session', prorows => '100', proisstrict => 'f',
+  proretset => 't', provolatile => 's', proparallel => 'r',
+  prorettype => 'record', proargtypes => 'int4',
+  proallargtypes => '{int4,int4,float8,int8,float8,int8,float8,int8,float8,int8,float8,int8}',
+  proargmodes => '{i,o,o,o,o,o,o,o,o,o,o,o}',
+  proargnames => '{pid,pid,total_running_time,total_running_count,total_idle_time,total_idle_count,total_transaction_idle_time,total_transaction_idle_count,total_transaction_idle_aborted_time,total_transaction_idle_aborted_count,total_fastpath_time,total_fastpath_count}',
+  prosrc => 'pg_stat_get_session' },
 { oid => '3318',
   descr => 'statistics: information about progress of backends running maintenance command',
   proname => 'pg_stat_get_progress_info', prorows => '100', proretset => 't',
diff --git a/src/include/utils/backend_status.h b/src/include/utils/backend_status.h
index f7bd83113a..17eeb02a84 100644
--- a/src/include/utils/backend_status.h
+++ b/src/include/utils/backend_status.h
@@ -80,6 +80,26 @@ typedef struct PgBackendGSSStatus
 
 } PgBackendGSSStatus;
 
+/*
+ * PgBackendSessionStatus
+ *
+ * For each session, we keep counters accumulated since the start of the session
+ * in a separate struct. The struct is always filled.
+ *
+ */
+typedef struct PgBackendSessionStatus
+{
+	int64		total_running_time;
+	int64		total_running_count;
+	int64		total_idle_time;
+	int64		total_idle_count;
+	int64		total_transaction_idle_time;
+	int64		total_transaction_idle_count;
+	int64		total_transaction_idle_aborted_time;
+	int64		total_transaction_idle_aborted_count;
+	int64		total_fastpath_time;
+	int64		total_fastpath_count;
+} PgBackendSessionStatus;
 
 /* ----------
  * PgBackendStatus
@@ -169,6 +189,9 @@ typedef struct PgBackendStatus
 
 	/* query identifier, optionally computed using post_parse_analyze_hook */
 	uint64		st_query_id;
+
+	/* Counters accumulated since the start of the session */
+	PgBackendSessionStatus st_session;
 } PgBackendStatus;

@@ -233,6 +256,20 @@ typedef struct PgBackendStatus
 	 ((before_changecount) & 1) == 0)

+/* Macros to identify the states for time accounting */
+#define PGSTAT_IS_RUNNING(s) \
+	((s)->st_state == STATE_RUNNING)
+#define PGSTAT_IS_FASTPATH(s) \
+	((s)->st_state == STATE_FASTPATH)
+#define PGSTAT_IS_IDLE(s) \
+	((s)->st_state == STATE_IDLE)
+#define PGSTAT_IS_IDLEINTRANSACTION(s) \
+	((s)->st_state == STATE_IDLEINTRANSACTION)
+#define PGSTAT_IS_IDLEINTRANSACTION_ABORTED(s) \
+	((s)->st_state == STATE_IDLEINTRANSACTION_ABORTED)
+#define PGSTAT_IS_ACTIVE(s) \
+	((s)->st_state == STATE_RUNNING || (s)->st_state == STATE_FASTPATH)
+
 /* ----------
  * LocalPgBackendStatus
  *
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index e7a2f5856a..73f10c8477 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -2071,6 +2071,18 @@ pg_stat_replication_slots| SELECT s.slot_name,
    FROM pg_replication_slots r,
     LATERAL pg_stat_get_replication_slot((r.slot_name)::text) s(slot_name, spill_txns, spill_count, spill_bytes, stream_txns, stream_count, stream_bytes, total_txns, total_bytes, stats_reset)
   WHERE (r.datoid IS NOT NULL);
+pg_stat_session| SELECT pid,
+    total_running_time,
+    total_running_count,
+    total_idle_time,
+    total_idle_count,
+    total_transaction_idle_time,
+    total_transaction_idle_count,
+    total_transaction_idle_aborted_time,
+    total_transaction_idle_aborted_count,
+    total_fastpath_time,
+    total_fastpath_count
+   FROM pg_stat_get_session(NULL::integer) s(pid, total_running_time, total_running_count, total_idle_time, total_idle_count, total_transaction_idle_time, total_transaction_idle_count, total_transaction_idle_aborted_time, total_transaction_idle_aborted_count, total_fastpath_time, total_fastpath_count);
 pg_stat_slru| SELECT name,
     blks_zeroed,
     blks_hit,
-- 
2.34.1

---

From: Andrey Borodin <[email-masked]>
Date: Thu, 16 Feb 2023 13:37:41 -0800

> Please review this early version.

Hi Sergey!

I've taken a look into the patch and got some notes.
1. It is hard to understand what fastpath backend state is. What do
fastpath metrics mean for a user?
2. Anyway, the path "if (PGSTAT_IS_FASTPATH(beentry))" seems
unreachable to me. I'm a bit surprised that compilers do not produce
warnings about it. Maybe I'm just wrong.
3. Tests do not check any incrementation logic. I think we can have
some test that verifies delta for select some_counter from
pg_stat_session where pid = pg_backend_pid();
4. Macroses like PGSTAT_IS_RUNNING do not look like net win in code
readability and PGSTAT prefix have no semantic load.

That's all I've found so far. Thank you!

Best regards, Andrey Borodin.

PS. We were doing on-air review session [0], I hope Nik will chime-in
with "usability part of a review".

[0] https://youtu.be/vTV8XhWf3mo?t=2404

---

From: Sergey Dudoladov <[email-masked]>
Date: Wed, 14 Jun 2023 07:50:35 +0200

Hello hackers,

Andrey and Nik, thank you for selecting this patch for review in
Postgres Hacking 101: I've modified the patch based both on your email
and the video.

1. Session statistics is now collected only for client backends. PG
internal processes like wal sender seem to stop sending statistics
after they have entered their respective main loops.
2. Fastpath state now counts towards the running state. I think this
special case does not justify tracking two extra numbers for every
client backend.
3. I've added a small test for pg_stat_session similar to other tests
in src/test/regress/sql/sysviews.sql
4.  Here are the pb_bench results requested in the video review:

Conditions: no assertions, number of transactions = 1000
The query: SELECT generate_series(1, 10000000) OFFSET 10000000;
With pg_stat_session:
    latency average = 324.480 ms
    tps = 3.081857 (without initial connection time)

Without pg_stat_session:
    latency average = 327.370 ms
    tps = 3.054651 (without initial connection time)

Regards,
Sergey
From 329db9a594b96499135bcbfdfad674f6af7ae1dc Mon Sep 17 00:00:00 2001
From: Sergey Dudoladov <[email-masked]>
Date: Tue, 22 Nov 2022 09:23:32 +0100
Subject: [PATCH] Add pg_stat_session

Author: Sergey Dudoladov

Adds pg_stat_session view to track statistics accumulated during
 lifetime of a session (client backend).

catversion bump is necessary due to a new view / function

Discussion:
https://www.postgresql.org/message-id/flat/CA%2BFpmFcJF0vwi-SWW0wYO-c-FbhyawLq4tCpRDCJJ8Bq%3Dja-gA%40mail.gmail.com
---
 doc/src/sgml/monitoring.sgml                | 134 ++++++++++++++++++++
 src/backend/catalog/system_views.sql        |  13 ++
 src/backend/utils/activity/backend_status.c |  57 +++++++--
 src/backend/utils/adt/pgstatfuncs.c         |  70 ++++++++++
 src/include/catalog/pg_proc.dat             |   9 ++
 src/include/utils/backend_status.h          |  32 +++++
 src/test/regress/expected/rules.out         |  10 ++
 src/test/regress/expected/sysviews.out      |   7 +
 src/test/regress/sql/sysviews.sql           |   3 +
 9 files changed, 325 insertions(+), 10 deletions(-)

diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 5cfdc70c03..aa759e36c5 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -414,6 +414,20 @@ postgres   27093  0.0  0.0  30096  2752 ?        Ss   11:34   0:00 postgres: ser
        See <xref linkend="copy-progress-reporting"/>.
       </entry>
      </row>
+
+     <row>
+      <entry>
+       <structname>pg_stat_session</structname>
+       <indexterm><primary>pg_stat_session</primary></indexterm>
+      </entry>
+      <entry>
+       One row per client backend, showing information related to
+       the currently accumulated activity of that process, such as time spent in
+       a certain state.
+       See <link linkend="monitoring-pg-stat-session-view">
+       <structname>pg_stat_session</structname></link> for details.
+      </entry>
+     </row>
     </tbody>
    </tgroup>
   </table>
@@ -5755,6 +5769,110 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i
    </tgroup>
   </table>
 
+       Process ID of this client backend.
+       Time in milliseconds this backend spent in the <literal>running</literal> or <literal>fastpath</literal> state.
+       Number of times this backend switched to the <literal>running</literal> or <literal>fastpath</literal> state.
 
  <sect2 id="monitoring-stats-functions">
@@ -5822,6 +5940,22 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i
        </para></entry>
       </row>
 
+      <row>
+        Returns a record of information about the client backend with the specified
index c18fea8362..8f073b442c 100644
@@ -872,6 +872,19 @@ CREATE VIEW pg_stat_activity AS
 
+            s.total_transaction_idle_aborted_count
index 38f91a495b..b20ef8c51e 100644
 
@@ -526,6 +528,9 @@ pgstat_report_activity(BackendState state, const char *cmd_str)

 	if (!beentry)
@@ -551,6 +556,7 @@ pgstat_report_activity(BackendState state, const char *cmd_str)
 			beentry->st_xact_start_timestamp = 0;
 			beentry->st_query_id = UINT64CONST(0);
 			proc->wait_event_info = 0;
+			MemSet(&beentry->st_session, 0, sizeof(beentry->st_session));
 			PGSTAT_END_WRITE_ACTIVITY(beentry);
 		}
 		return;
@@ -573,27 +579,44 @@ 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 a client backend has changed state, update per-database and per-session counters.
 	 */
-	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)
+	if ((PGSTAT_IS_ACTIVE(beentry) ||
+		 PGSTAT_IS_IDLEINTRANSACTION(beentry) ||
+		 PGSTAT_IS_IDLEINTRANSACTION_ABORTED(beentry) || 
+		 PGSTAT_IS_IDLE(beentry)) &&
+		state != beentry->st_state &&
+		beentry->st_backendType == B_BACKEND)
 	{
 		long		secs;
 		int			usecs;
+		int64		usecs_diff;

+		if (PGSTAT_IS_ACTIVE(beentry)) {
 
 	/*
@@ -619,6 +642,20 @@ pgstat_report_activity(BackendState state, const char *cmd_str)
 		beentry->st_activity_start_timestamp = start_timestamp;
 	}
 
+	if (beentry->st_backendType == B_BACKEND) {
 
index 49adc319fc..6eb2384618 100644
 
+/*
+ * Returns accumulated statistics of current PG backends.
+ */
+Datum
+pg_stat_get_session(PG_FUNCTION_ARGS)
+{
+#define PG_STAT_GET_SESSION_COLS	9
+	int			num_backends = pgstat_fetch_stat_numbackends();
+	int			curr_backend;
+	int			pid = PG_ARGISNULL(0) ? -1 : PG_GETARG_INT32(0);
+	ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
+
+	InitMaterializedSRF(fcinfo, 0);
+
+	/* 1-based index */
+	for (curr_backend = 1; curr_backend <= num_backends; curr_backend++)
+	{
+		/* for each row */
+		Datum		values[PG_STAT_GET_SESSION_COLS] = {0};
+		bool		nulls[PG_STAT_GET_SESSION_COLS] = {0};
+		LocalPgBackendStatus *local_beentry;
+		PgBackendStatus *beentry;
+
+		/* Get the next one in the list */
+		local_beentry = pgstat_fetch_stat_local_beentry(curr_backend);
+		beentry = &local_beentry->backendStatus;
+
+		/* Report statistics only for client backends */
+		if (beentry->st_backendType != B_BACKEND)
+			continue;
+
+		/* If looking for specific PID, ignore all the others */
+		if (pid != -1 && beentry->st_procpid != pid)
+			continue;
+
+		/* Values available to all callers */
+		values[0] = Int32GetDatum(beentry->st_procpid);
+
+		/* Values only available to role member or pg_read_all_stats */
+		if (HAS_PGSTAT_PERMISSIONS(beentry->st_userid)){
+			/* convert to msec */
+			values[1] = Float8GetDatum(beentry->st_session.total_running_time / 1000.0);
+			values[2] = Int64GetDatum(beentry->st_session.total_running_count);
+			values[3] = Float8GetDatum(beentry->st_session.total_idle_time / 1000.0);
+			values[4] = Int64GetDatum(beentry->st_session.total_idle_count);
+			values[5] = Float8GetDatum(beentry->st_session.total_transaction_idle_time / 1000.0);
+			values[6] = Int64GetDatum(beentry->st_session.total_transaction_idle_count);
+			values[7] = Float8GetDatum(beentry->st_session.total_transaction_idle_aborted_time / 1000.0);
+			values[8] = Int64GetDatum(beentry->st_session.total_transaction_idle_aborted_count);
+		} else {
+			nulls[1] = true;
+			nulls[2] = true;
+			nulls[3] = true;
+			nulls[4] = true;
+			nulls[5] = true;
+			nulls[6] = true;
+			nulls[7] = true;
+			nulls[8] = true;
+		}
+
+		tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc, values, nulls);
+
+		/* If only a single backend was requested, and we found it, break. */
+		if (pid != -1)
+			break;
+	}
+
+	return (Datum) 0;
+}
+
 /*
  * Returns activity of PG backends.
  */
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 6996073989..60ac6ea3de 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5417,6 +5417,15 @@
   proargmodes => '{i,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o}',
   proargnames => '{pid,datid,pid,usesysid,application_name,state,query,wait_event_type,wait_event,xact_start,query_start,backend_start,state_change,client_addr,client_hostname,client_port,backend_xid,backend_xmin,backend_type,ssl,sslversion,sslcipher,sslbits,ssl_client_dn,ssl_client_serial,ssl_issuer_dn,gss_auth,gss_princ,gss_enc,gss_delegation,leader_pid,query_id}',
   prosrc => 'pg_stat_get_activity' },
+{ oid => '2173',
+  descr => 'statistics: cumulative information about currently active client backends',
+  proname => 'pg_stat_get_session', prorows => '100', proisstrict => 'f',
+  proretset => 't', provolatile => 's', proparallel => 'r',
+  prorettype => 'record', proargtypes => 'int4',
+  proallargtypes => '{int4,int4,float8,int8,float8,int8,float8,int8,float8,int8}',
+  proargmodes => '{i,o,o,o,o,o,o,o,o,o}',
+  proargnames => '{pid,pid,total_running_time,total_running_count,total_idle_time,total_idle_count,total_transaction_idle_time,total_transaction_idle_count,total_transaction_idle_aborted_time,total_transaction_idle_aborted_count}',
+  prosrc => 'pg_stat_get_session' },
 { oid => '3318',
   descr => 'statistics: information about progress of backends running maintenance command',
   proname => 'pg_stat_get_progress_info', prorows => '100', proretset => 't',
diff --git a/src/include/utils/backend_status.h b/src/include/utils/backend_status.h
index 16500d53b2..b0099d307e 100644
--- a/src/include/utils/backend_status.h
+++ b/src/include/utils/backend_status.h
@@ -81,6 +81,24 @@ typedef struct PgBackendGSSStatus
 
 } PgBackendGSSStatus;
 
+/*
 
 /* ----------
  * PgBackendStatus
@@ -170,6 +188,9 @@ typedef struct PgBackendStatus

@@ -234,6 +255,17 @@ typedef struct PgBackendStatus
 	 ((before_changecount) & 1) == 0)

+/* Macros to identify the states for time accounting */
+#define PGSTAT_IS_ACTIVE(s) \
+	((s)->st_state == STATE_RUNNING || (s)->st_state == STATE_FASTPATH)
+#define PGSTAT_IS_IDLE(s) \
+	((s)->st_state == STATE_IDLE)
+#define PGSTAT_IS_IDLEINTRANSACTION(s) \
+	((s)->st_state == STATE_IDLEINTRANSACTION)
+#define PGSTAT_IS_IDLEINTRANSACTION_ABORTED(s) \
+	((s)->st_state == STATE_IDLEINTRANSACTION_ABORTED)
+
+
 /* ----------
  * LocalPgBackendStatus
  *
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 7fd81e6a7d..9eb67d4763 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -2094,6 +2094,16 @@ pg_stat_replication_slots| SELECT s.slot_name,
    FROM pg_replication_slots r,
     LATERAL pg_stat_get_replication_slot((r.slot_name)::text) s(slot_name, spill_txns, spill_count, spill_bytes, stream_txns, stream_count, stream_bytes, total_txns, total_bytes, stats_reset)
   WHERE (r.datoid IS NOT NULL);
+pg_stat_session| SELECT pid,
+    total_running_time,
+    total_running_count,
+    total_idle_time,
+    total_idle_count,
+    total_transaction_idle_time,
+    total_transaction_idle_count,
+    total_transaction_idle_aborted_time,
+    total_transaction_idle_aborted_count
+   FROM pg_stat_get_session(NULL::integer) s(pid, total_running_time, total_running_count, total_idle_time, total_idle_count, total_transaction_idle_time, total_transaction_idle_count, total_transaction_idle_aborted_time, total_transaction_idle_aborted_count);
 pg_stat_slru| SELECT name,
     blks_zeroed,
     blks_hit,
diff --git a/src/test/regress/expected/sysviews.out b/src/test/regress/expected/sysviews.out
index 001c6e7eb9..e66c08cd40 100644
--- a/src/test/regress/expected/sysviews.out
+++ b/src/test/regress/expected/sysviews.out
@@ -167,3 +167,10 @@ select count(distinct utc_offset) >= 24 as ok from pg_timezone_abbrevs;
  t
 (1 row)
 
+select total_idle_count as prev_idle_count from pg_stat_session where pid = pg_backend_pid() \gset
+select (select total_idle_count from pg_stat_session where pid = pg_backend_pid()) - :prev_idle_count = 1 as ok;
+ ok 
+----
+ t
+(1 row)
+
diff --git a/src/test/regress/sql/sysviews.sql b/src/test/regress/sql/sysviews.sql
index 351e469c77..6545e65d21 100644
--- a/src/test/regress/sql/sysviews.sql
+++ b/src/test/regress/sql/sysviews.sql
@@ -68,3 +68,6 @@ set timezone_abbreviations = 'Australia';
 select count(distinct utc_offset) >= 24 as ok from pg_timezone_abbrevs;
 set timezone_abbreviations = 'India';
 select count(distinct utc_offset) >= 24 as ok from pg_timezone_abbrevs;
+
+select total_idle_count as prev_idle_count from pg_stat_session where pid = pg_backend_pid() \gset
+select (select total_idle_count from pg_stat_session where pid = pg_backend_pid()) - :prev_idle_count = 1 as ok;
\ No newline at end of file
-- 
2.34.1

---

From: Andrei Zubkov <[email-masked]>
Date: Wed, 25 Oct 2023 16:12:42 +0300

Hi Sergey,

I've done a review of this patch. I found the patch idea very useful,
thank you for the patch. I've noted something observing this patch:
1. Patch can't be applied on the current master. My review is based on
   application of this patch over ac68323a878
2. Being applied over ac68323a878 patch works as expected.
3. Field names seems quite long to me (and they should be uniformly
   named with the same statistics in other views. For example
   "running" term is called "active" in pg_stat_database)
4. Meaningless spaces at the end of line:
   - backend_status.c:586
   - monitoring.sgml:5857
5. Patch adds

     usecs_diff = secs * 1000000 + usecs;

   at backend_status.c:pgstat_report_activity() to optimize
   calculations. But

     pgstat_count_conn_active_time((PgStat_Counter) secs * 1000000 +
usecs);
   and  
     pgstat_count_conn_txn_idle_time((PgStat_Counter) secs * 1000000 +
usecs);

   are left in place after that.
6. I'm not sure that I can understand the comment
     /* Keep statistics for pg_stat_database intact */
   at backend_status.c:600 correctly. Can you please explain it a
   little?
7. Tests seems incomplete. It looks like we can check increments in
   all fields playing with transactions in tests.

Also, I have a thought about other possible improvements fitting to
this patch.

The view pg_stat_session is really needed in Postgres but I think it
should have much more statistics. I mean all resource statistics
related to sessions. Every backend has instrumentation that tracks
resource consumption. Data of this instrumentation goes to the
cumulative statistics system and is used in monitoring extensions
(like pg_stat_statements). I think pg_stat_session view is able to add
one more dimension of monitoring - a dimension of sessions. In my
opinion this view should provide resource consumption statistics of
current sessions in two cumulative sets of statistics - since backend
start and since transaction start.  Such view will be really useful in
monitoring of long running sessions and transactions providing
resource consumption information besides timing statistics.

regards, Andrei Zubkov
Postgres Professional

---

From: Aleksander Alekseev <[email-masked]>
Date: Wed, 25 Oct 2023 16:17:51 +0300

Hi,

>    application of this patch over ac68323a878

On top of that not sure if I see the patch on the November commitfest
[1]. Please make sure it's there so that cfbot will check the patch.

[1]: https://commitfest.postgresql.org/45/

-- 
Best regards,
Aleksander Alekseev

---

From: Andrei Zubkov <[email-masked]>
Date: Wed, 25 Oct 2023 16:36:32 +0300

Hi Aleksander,

> [1]. Please make sure it's there so that cfbot will check the patch.

Yes, this patch is listed on the November commitfest. cfbot says rebase
needed since 2023-08-21.

regards, Andrei Zubkov

---

From: Aleksander Alekseev <[email-masked]>
Date: Wed, 25 Oct 2023 16:38:19 +0300

Hi,

> Yes, this patch is listed on the November commitfest. cfbot says rebase

You are right, I missed the corresponding entry [1].

[1]: https://commitfest.postgresql.org/45/3405/

-- 
Best regards,
Aleksander Alekseev

---

From: vignesh C <[email-masked]>
Date: Sun, 14 Jan 2024 16:34:44 +0530

> Yes, this patch is listed on the November commitfest. cfbot says rebase

I have changed the status of commitfest entry to "Returned with
Feedback" as Andrei Zubkov's comments have not yet been resolved.
Please feel free to post an updated version of the patch and update
the commitfest entry accordingly.

Regards,
Vignesh

---

From: Sergey Dudoladov <[email-masked]>
Date: Thu, 1 Feb 2024 18:41:55 +0100

Hi all,

@Andrei Zubkov
I've modify the patch to address most of your comments.

>  I have a thought about other possible improvements fitting to this patch.
> I think pg_stat_session view is able to add one more dimension of
monitoring - a dimension of sessions

I would like to remind here about the initial scope of this patch. The main
goal of it was to ease tracking "idle in transactions" connections, a
feature that would really help in my work. The "pg_stat_session" came into
play only because the "pg_stat_activity" was seen as an unsuitable place
for the relevant counters. With that I still would like to maintaint the
focus on committing the "idle in transactions" part of pg_stat_session
first.

Regards,
Sergey
From 05f5117be52b613bb9d11114833eec38e152c6f9 Mon Sep 17 00:00:00 2001
From: Sergey Dudoladov <[email-masked]>
Date: Thu, 1 Feb 2024 16:11:36 +0100
Subject: [PATCH] Add pg_stat_session

Author: Sergey Dudoladov

Reviewed-by: Aleksander Alekseev, Bertrand Drouvot, Atsushi
Torikoshi, and Andrei Zubkov

Discussion:
 src/backend/utils/activity/backend_status.c |  64 ++++++++--
 9 files changed, 329 insertions(+), 13 deletions(-)

index d9b8b37585..b10423428a 100644
@@ -4589,6 +4603,110 @@ description | Waiting for a newly initialized WAL file to reach durable storage
    </tgroup>
   </table>

+  <table id="monitoring-pg-stat-session-view" xreflabel="pg_stat_session">
+   <title><structname>pg_stat_session</structname> View</title>
+   <tgroup cols="1">
+    <thead>
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       Column Type
+      </para>
+      <para>
+       Description
+      </para></entry>
+     </row>
+    </thead>
+
+    <tbody>
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>pid</structfield> <type>integer</type>
+      </para>
+      <para>
+       Process ID of this client backend.
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>active_time</structfield> <type>double precision</type>
+      </para>
+      <para>
+       Time in milliseconds this backend spent in the <literal>running</literal> or <literal>fastpath</literal> state.
+      </para></entry>
+     </row>
+
+    <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>active_count</structfield> <type>bigint</type>
+      </para>
+      <para>
+       Number of times this backend switched to the <literal>running</literal> or <literal>fastpath</literal> state.
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>idle_time</structfield> <type>double precision</type>
+      </para>
+      <para>
+       Time in milliseconds this backend spent in the <literal>idle</literal> state.
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>idle_count</structfield> <type>bigint</type>
+      </para>
+      <para>
+       Number of times this backend switched to the <literal>idle</literal> state.
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>idle_in_transaction_time</structfield> <type>double precision</type>
+      </para>
+      <para>
+       Time in milliseconds this backend spent in the <literal>idle in transaction</literal>
+       state.
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>idle_in_transaction_count</structfield> <type>bigint</type>
+      </para>
+      <para>
+       Number of times this backend switched to the <literal>idle in transaction</literal>
+       state.
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>idle_in_transaction_aborted_time</structfield> <type>double precision</type>
+      </para>
+      <para>
+       Time in milliseconds this backend spent in the <literal>idle in transaction (aborted)</literal>
+       state.
+      </para></entry>
+     </row>
+
+     <row>
+      <entry role="catalog_table_entry"><para role="column_definition">
+       <structfield>idle_in_transaction_aborted_count</structfield> <type>bigint</type>
+      </para>
+      <para>
+       Number of times this backend switched to the <literal>idle in transaction (aborted)</literal>
+       state.
+      </para></entry>
+     </row>
+
+    </tbody>
+   </tgroup>
+  </table>
+
  </sect2>

  <sect2 id="monitoring-stats-functions">
@@ -5128,6 +5246,22 @@ FROM pg_stat_get_backend_idset() AS backendid;
        </para></entry>
       </row>

+      <row>
index 6791bff9dd..79db3af28b 100644
@@ -893,6 +893,19 @@ CREATE VIEW pg_stat_activity AS

+CREATE VIEW pg_stat_session AS
+    SELECT
+            s.pid,
+            s.active_time,
+            s.active_count,
+            s.idle_time,
+            s.idle_count,
+            s.idle_in_transaction_time,
+            s.idle_in_transaction_count,
+            s.idle_in_transaction_aborted_time,
+            s.idle_in_transaction_aborted_count
+     FROM pg_stat_get_session(NULL) as s;
+
 CREATE VIEW pg_stat_replication AS
     SELECT
             S.pid,
diff --git a/src/backend/utils/activity/backend_status.c b/src/backend/utils/activity/backend_status.c
index 1a1050c8da..31763a1efd 100644
--- a/src/backend/utils/activity/backend_status.c
+++ b/src/backend/utils/activity/backend_status.c
@@ -338,6 +338,8 @@ pgstat_bestart(void)
 	lbeentry.st_xact_start_timestamp = 0;
 	lbeentry.st_databaseid = MyDatabaseId;

 	if (!beentry)
@@ -573,27 +579,45 @@ pgstat_report_activity(BackendState state, const char *cmd_str)

 	/*

 		TimestampDifference(beentry->st_state_start_timestamp,
 							current_timestamp,
 							&secs, &usecs);
-
-		if (beentry->st_state == STATE_RUNNING ||
-			beentry->st_state == STATE_FASTPATH)
-			pgstat_count_conn_active_time((PgStat_Counter) secs * 1000000 + usecs);
-		else
-			pgstat_count_conn_txn_idle_time((PgStat_Counter) secs * 1000000 + usecs);
+		usecs_diff = secs * 1000000 + usecs;
+
+		/* compute values for pg_stat_database */
+		if (PGSTAT_IS_ACTIVE(beentry))
+			pgstat_count_conn_active_time((PgStat_Counter) usecs_diff);
+		else if (PGSTAT_IS_IDLEINTRANSACTION(beentry) ||
+				 PGSTAT_IS_IDLEINTRANSACTION_ABORTED(beentry))
+			pgstat_count_conn_txn_idle_time((PgStat_Counter) usecs_diff);
+
+		/* compute values for pg_stat_session */
+		if (PGSTAT_IS_ACTIVE(beentry)) {
+			st_session_diff.active_time = usecs_diff;
+			st_session_diff.active_count += 1;
+		} else if (PGSTAT_IS_IDLE(beentry)){
+			st_session_diff.idle_time = usecs_diff;
+			st_session_diff.idle_count += 1;
+		} else if (PGSTAT_IS_IDLEINTRANSACTION(beentry)){
+			st_session_diff.idle_in_transaction_time = usecs_diff;
+			st_session_diff.idle_in_transaction_count += 1;
+		} else if (PGSTAT_IS_IDLEINTRANSACTION_ABORTED(beentry)){
+			st_session_diff.idle_in_transaction_aborted_time = usecs_diff;
+			st_session_diff.idle_in_transaction_aborted_count += 1;
+		}
 	}

 	/*
@@ -619,6 +643,20 @@ pgstat_report_activity(BackendState state, const char *cmd_str)
 		beentry->st_activity_start_timestamp = start_timestamp;
 	}

+	if (beentry->st_backendType == B_BACKEND) {
+		beentry->st_session.active_time += st_session_diff.active_time;
+		beentry->st_session.active_count += st_session_diff.active_count;
+
+		beentry->st_session.idle_time += st_session_diff.idle_time;
+		beentry->st_session.idle_count += st_session_diff.idle_count;
+
+		beentry->st_session.idle_in_transaction_time += st_session_diff.idle_in_transaction_time;
+		beentry->st_session.idle_in_transaction_count += st_session_diff.idle_in_transaction_count;
+
+		beentry->st_session.idle_in_transaction_aborted_time += st_session_diff.idle_in_transaction_aborted_time;
+		beentry->st_session.idle_in_transaction_aborted_count += st_session_diff.idle_in_transaction_aborted_count;
+	}
+
 	PGSTAT_END_WRITE_ACTIVITY(beentry);
 }

diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 30a2063505..a4a8c7204c 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -298,6 +298,76 @@ pg_stat_get_progress_info(PG_FUNCTION_ARGS)
 	return (Datum) 0;
 }

+/*
+ * Returns accumulated statistics of current PG backends.
+ */
+Datum
+pg_stat_get_session(PG_FUNCTION_ARGS)
+{
+#define PG_STAT_GET_SESSION_COLS	9
+	int			num_backends = pgstat_fetch_stat_numbackends();
+	int			curr_backend;
+	int			pid = PG_ARGISNULL(0) ? -1 : PG_GETARG_INT32(0);
+	ReturnSetInfo *rsinfo = (ReturnSetInfo *) fcinfo->resultinfo;
+
+	InitMaterializedSRF(fcinfo, 0);
+
+	/* 1-based index */
+	for (curr_backend = 1; curr_backend <= num_backends; curr_backend++)
+	{
+		/* for each row */
+		Datum		values[PG_STAT_GET_SESSION_COLS] = {0};
+		bool		nulls[PG_STAT_GET_SESSION_COLS] = {0};
+		LocalPgBackendStatus *local_beentry;
+		PgBackendStatus *beentry;
+
+		/* Get the next one in the list */
+		local_beentry = pgstat_get_local_beentry_by_index(curr_backend);
+		beentry = &local_beentry->backendStatus;
+
+		/* Report statistics only for client backends */
+		if (beentry->st_backendType != B_BACKEND)
+			continue;
+
+		/* If looking for specific PID, ignore all the others */
+		if (pid != -1 && beentry->st_procpid != pid)
+			continue;
+
+		/* Values available to all callers */
+		values[0] = Int32GetDatum(beentry->st_procpid);
+
+		/* Values only available to role member or pg_read_all_stats */
+		if (HAS_PGSTAT_PERMISSIONS(beentry->st_userid)){
+			/* convert to msec */
+			values[1] = Float8GetDatum(beentry->st_session.active_time / 1000.0);
+			values[2] = Int64GetDatum(beentry->st_session.active_count);
+			values[3] = Float8GetDatum(beentry->st_session.idle_time / 1000.0);
+			values[4] = Int64GetDatum(beentry->st_session.idle_count);
+			values[5] = Float8GetDatum(beentry->st_session.idle_in_transaction_time / 1000.0);
+			values[6] = Int64GetDatum(beentry->st_session.idle_in_transaction_count);
+			values[7] = Float8GetDatum(beentry->st_session.idle_in_transaction_aborted_time / 1000.0);
+			values[8] = Int64GetDatum(beentry->st_session.idle_in_transaction_aborted_count);
+		} else {
+			nulls[1] = true;
+			nulls[2] = true;
+			nulls[3] = true;
+			nulls[4] = true;
+			nulls[5] = true;
+			nulls[6] = true;
+			nulls[7] = true;
+			nulls[8] = true;
+		}
+
+		tuplestore_putvalues(rsinfo->setResult, rsinfo->setDesc, values, nulls);
+
+		/* If only a single backend was requested, and we found it, break. */
+		if (pid != -1)
+			break;
+	}
+
+	return (Datum) 0;
+}
+
 /*
  * Returns activity of PG backends.
  */
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 29af4ce65d..1c81f70f9b 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5450,6 +5450,15 @@
   proargtypes => '', proallargtypes => '{text,text,text}',
   proargmodes => '{o,o,o}', proargnames => '{type,name,description}',
   prosrc => 'pg_get_wait_events' },
+{ oid => '2173',
+  descr => 'statistics: cumulative information about currently active client backends',
+  proname => 'pg_stat_get_session', prorows => '100', proisstrict => 'f',
+  proretset => 't', provolatile => 's', proparallel => 'r',
+  prorettype => 'record', proargtypes => 'int4',
+  proallargtypes => '{int4,int4,float8,int8,float8,int8,float8,int8,float8,int8}',
+  proargmodes => '{i,o,o,o,o,o,o,o,o,o}',
+  proargnames => '{pid,pid,active_time,active_count,idle_time,idle_count,idle_in_transaction_time,idle_in_transaction_count,idle_in_transaction_aborted_time,idle_in_transaction_aborted_count}',
+  prosrc => 'pg_stat_get_session' },
 { oid => '3318',
   descr => 'statistics: information about progress of backends running maintenance command',
   proname => 'pg_stat_get_progress_info', prorows => '100', proretset => 't',
diff --git a/src/include/utils/backend_status.h b/src/include/utils/backend_status.h
index 7b8a34f64f..830df6b9fa 100644
--- a/src/include/utils/backend_status.h
+++ b/src/include/utils/backend_status.h
@@ -81,6 +81,24 @@ typedef struct PgBackendGSSStatus

 } PgBackendGSSStatus;

+/*
+ * PgBackendSessionStatus
+ *
+ * For each session, we keep counters accumulated since the start of the session
+ * in a separate struct. The struct is always filled.
+ *
+ */
+typedef struct PgBackendSessionStatus
+{
+	int64		active_time;
+	int64		active_count;
+	int64		idle_time;
+	int64		idle_count;
+	int64		idle_in_transaction_time;
+	int64		idle_in_transaction_count;
+	int64		idle_in_transaction_aborted_time;
+	int64		idle_in_transaction_aborted_count;
+} PgBackendSessionStatus;

 /* ----------
  * PgBackendStatus

+/* Macros to identify the states for time accounting */
+#define PGSTAT_IS_ACTIVE(s) \
+	((s)->st_state == STATE_RUNNING || (s)->st_state == STATE_FASTPATH)
+#define PGSTAT_IS_IDLE(s) \
+	((s)->st_state == STATE_IDLE)
+#define PGSTAT_IS_IDLEINTRANSACTION(s) \
+	((s)->st_state == STATE_IDLEINTRANSACTION)
+#define PGSTAT_IS_IDLEINTRANSACTION_ABORTED(s) \
+	((s)->st_state == STATE_IDLEINTRANSACTION_ABORTED)
+
+
 /* ----------
  * LocalPgBackendStatus
  *
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index abc944e8b8..108b5efd15 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -2100,6 +2100,16 @@ pg_stat_replication_slots| SELECT s.slot_name,
    FROM pg_replication_slots r,
     LATERAL pg_stat_get_replication_slot((r.slot_name)::text) s(slot_name, spill_txns, spill_count, spill_bytes, stream_txns, stream_count, stream_bytes, total_txns, total_bytes, stats_reset)
   WHERE (r.datoid IS NOT NULL);
+pg_stat_session| SELECT pid,
+    active_time,
+    active_count,
+    idle_time,
+    idle_count,
+    idle_in_transaction_time,
+    idle_in_transaction_count,
+    idle_in_transaction_aborted_time,
+    idle_in_transaction_aborted_count
+   FROM pg_stat_get_session(NULL::integer) s(pid, active_time, active_count, idle_time, idle_count, idle_in_transaction_time, idle_in_transaction_count, idle_in_transaction_aborted_time, idle_in_transaction_aborted_count);
 pg_stat_slru| SELECT name,
     blks_zeroed,
     blks_hit,
diff --git a/src/test/regress/expected/sysviews.out b/src/test/regress/expected/sysviews.out
index 9be7aca2b8..d3fe88b6a5 100644
--- a/src/test/regress/expected/sysviews.out
+++ b/src/test/regress/expected/sysviews.out
@@ -185,3 +185,10 @@ select count(distinct utc_offset) >= 24 as ok from pg_timezone_abbrevs;
  t
 (1 row)

+select idle_count as prev_idle_count from pg_stat_session where pid = pg_backend_pid() \gset
+select (select idle_count from pg_stat_session where pid = pg_backend_pid()) - :prev_idle_count = 1 as ok;
+ ok
+----
+ t
+(1 row)
+
diff --git a/src/test/regress/sql/sysviews.sql b/src/test/regress/sql/sysviews.sql
index 6b4e24601d..910d6a6851 100644
--- a/src/test/regress/sql/sysviews.sql
+++ b/src/test/regress/sql/sysviews.sql
@@ -72,3 +72,6 @@ set timezone_abbreviations = 'Australia';
 select count(distinct utc_offset) >= 24 as ok from pg_timezone_abbrevs;
 set timezone_abbreviations = 'India';
 select count(distinct utc_offset) >= 24 as ok from pg_timezone_abbrevs;
+
+select idle_count as prev_idle_count from pg_stat_session where pid = pg_backend_pid() \gset
+select (select idle_count from pg_stat_session where pid = pg_backend_pid()) - :prev_idle_count = 1 as ok;
\ No newline at end of file
--
2.34.1

---

From: Sergey Dudoladov <[email-masked]>
Date: Thu, 1 Feb 2024 20:50:15 +0100

Hi again,

> It looks like we can check increments in all fields playing with
transactions in tests.

I've added such tests.

Regards,
Sergey
From 66ac1efe5424aa1385744a60047ffd44d42dd244 Mon Sep 17 00:00:00 2001
From: Sergey Dudoladov <[email-masked]>
Date: Thu, 1 Feb 2024 16:11:36 +0100
Subject: [PATCH] Add pg_stat_session

Author: Sergey Dudoladov

Discussion:
https://www.postgresql.org/message-id/flat/CA%2BFpmFcJF0vwi-SWW0wYO-c-FbhyawLq4tCpRDCJJ8Bq%3Dja-gA%40mail.gmail.com
---
 doc/src/sgml/monitoring.sgml                | 134 ++++++++++++++++++++
 src/backend/catalog/system_views.sql        |  13 ++
 src/backend/utils/activity/backend_status.c |  64 ++++++++--
 src/backend/utils/adt/pgstatfuncs.c         |  70 ++++++++++
 src/include/catalog/pg_proc.dat             |   9 ++
 src/include/utils/backend_status.h          |  32 +++++
 src/test/regress/expected/rules.out         |  10 ++
 src/test/regress/expected/sysviews.out      |  37 ++++++
 src/test/regress/sql/sysviews.sql           |  17 +++
 9 files changed, 373 insertions(+), 13 deletions(-)

 
 
+      <row>

 
 
 	if (!beentry)
 
 	/*

 	/*

 
+/*
 
 } PgBackendGSSStatus;
 
+/*
 
 /* ----------
  * PgBackendStatus

index 9be7aca2b8..42b04d198b 100644
@@ -185,3 +185,40 @@ select count(distinct utc_offset) >= 24 as ok from pg_timezone_abbrevs;
  t
 (1 row)
 
+select active_count as prev_active_count from pg_stat_session where pid = pg_backend_pid() \gset
+select (select active_count from pg_stat_session where pid = pg_backend_pid()) - :prev_active_count = 1 as ok;
+ ok 
+----
+ t
+(1 row)
+
+select idle_count as prev_idle_count from pg_stat_session where pid = pg_backend_pid() \gset
+select (select idle_count from pg_stat_session where pid = pg_backend_pid()) - :prev_idle_count = 1 as ok;
+ ok 
+----
+ t
+(1 row)
+
+select idle_in_transaction_count as prev_idle_in_transaction_count from pg_stat_session where pid = pg_backend_pid() \gset
+begin;
+commit;
+select (select idle_in_transaction_count from pg_stat_session where pid = pg_backend_pid()) - :prev_idle_in_transaction_count = 1 as ok;
+ ok 
+----
+ t
+(1 row)
+
+select idle_in_transaction_aborted_count as prev_idle_in_transaction_aborted_count from pg_stat_session where pid = pg_backend_pid() \gset
+begin;
+select non_existent_function();
+ERROR:  function non_existent_function() does not exist
+LINE 1: select non_existent_function();
+               ^
+HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
+rollback;
+select (select idle_in_transaction_aborted_count from pg_stat_session where pid = pg_backend_pid()) - :prev_idle_in_transaction_aborted_count = 1 as ok;
+ ok 
+----
+ t
+(1 row)
+
diff --git a/src/test/regress/sql/sysviews.sql b/src/test/regress/sql/sysviews.sql
index 6b4e24601d..593a3176ca 100644
--- a/src/test/regress/sql/sysviews.sql
+++ b/src/test/regress/sql/sysviews.sql
@@ -72,3 +72,20 @@ set timezone_abbreviations = 'Australia';
 select count(distinct utc_offset) >= 24 as ok from pg_timezone_abbrevs;
 set timezone_abbreviations = 'India';
 select count(distinct utc_offset) >= 24 as ok from pg_timezone_abbrevs;
+
+select active_count as prev_active_count from pg_stat_session where pid = pg_backend_pid() \gset
+select (select active_count from pg_stat_session where pid = pg_backend_pid()) - :prev_active_count = 1 as ok;
+
+select idle_count as prev_idle_count from pg_stat_session where pid = pg_backend_pid() \gset
+select (select idle_count from pg_stat_session where pid = pg_backend_pid()) - :prev_idle_count = 1 as ok;
+
+select idle_in_transaction_count as prev_idle_in_transaction_count from pg_stat_session where pid = pg_backend_pid() \gset
+begin;
+commit;
+select (select idle_in_transaction_count from pg_stat_session where pid = pg_backend_pid()) - :prev_idle_in_transaction_count = 1 as ok;
+
+select idle_in_transaction_aborted_count as prev_idle_in_transaction_aborted_count from pg_stat_session where pid = pg_backend_pid() \gset
+begin;
+select non_existent_function();
+rollback;
+select (select idle_in_transaction_aborted_count from pg_stat_session where pid = pg_backend_pid()) - :prev_idle_in_transaction_aborted_count = 1 as ok;
\ No newline at end of file
-- 
2.34.1

---

From: Andrei Zubkov <[email-masked]>
Date: Mon, 12 Feb 2024 15:30:58 +0300

Hi Sergei,

> I still would like to maintaint the focus on committing the "idle in
transactions" part of pg_stat_session first.

Agreed.

I've done a review of version 0004. This version is applied successful
over ce571434ae7, installcheck passed. The behavior of pg_stat_session
view and corresponding function looks correct. I've didn't found any
issues in the code.

Notes about the current state of a patch:

Naming
the view and function names 'pg_stat_session' seems correct for this
particular scope of a patch. However possible future resource
consumption statistics are valid for all backends (vacuum for example).
Right now it is not clear for me if we can get resource statistics from
those backends while those are listed in the pg_stat_activity view but
renaming to something like 'pg_stat_backend' seems reasonable to me.

Docs
1. session states referenced in monitoring.sgml is not uniform with
those of the pg_stat_activity view.
monitoring.sgml:4635
monitoring.sgml:4644
+       Time in milliseconds this backend spent in the
<literal>running</literal> or <literal>fastpath</literal> state.
I think those states should be referenced uniformly with
pg_stat_activity.

2. Description of the 'pg_stat_get_session()' function might be as
follows:

  Returns a row, showing statistics about the client backend with the
  specified process ID, or one row per client backend if
  <literal>NULL</literal> is specified. The fields returned are the
  same as those of <structname>pg_stat_session</structname> view.

The main thought here is to get rid of 'each active backend' because
'active backend' looks like backend in the 'active' state.

Tests
Call to a non-existing function is depend on non-existence of a
function, which can't be guaranteed absolutely. How about to do some
kind of obvious error here? Couple examples follows:

SELECT 0/0;

- or -

DO $$
BEGIN
RAISE 'test error';
END;
$$ LANGUAGE plpgsql;

My personal choice would be the last one.

-- 
regards, Andrei Zubkov
Postgres Professional

---

From: Sergey Dudoladov <[email-masked]>
Date: Wed, 27 Nov 2024 08:28:25 +0100

Hello, hackers.

I've attached the fifth version of the patch that incorporates suggestions
from Andrei.
The version  runs successfully on top of 8fcd80258bcf43dab93d877

> renaming to something like 'pg_stat_backend' seems reasonable to me.

For now I kept the "pg_stat_session" naming because I don't know how
desirable it is to collect the
resource statistics in this view given that a plenty of other pg_stat_*
views exists for other backends.

Regards,
Sergey.
From 54e4d85a6382fbb4baf4575c99310a6a61a738ac Mon Sep 17 00:00:00 2001
From: Sergey Dudoladov <[email-masked]>
Date: Thu, 1 Feb 2024 16:11:36 +0100
Subject: [PATCH] Add pg_stat_session

Author: Sergey Dudoladov

Discussion:
https://www.postgresql.org/message-id/flat/CA%2BFpmFcJF0vwi-SWW0wYO-c-FbhyawLq4tCpRDCJJ8Bq%3Dja-gA%40mail.gmail.com
---
 doc/src/sgml/monitoring.sgml                | 134 ++++++++++++++++++++
 src/backend/catalog/system_views.sql        |  13 ++
 src/backend/utils/activity/backend_status.c |  64 ++++++++--
 src/backend/utils/adt/pgstatfuncs.c         |  70 ++++++++++
 src/include/catalog/pg_proc.dat             |   9 ++
 src/include/utils/backend_status.h          |  32 +++++
 src/test/regress/expected/rules.out         |  10 ++
 src/test/regress/expected/sysviews.out      |  39 ++++++
 src/test/regress/sql/sysviews.sql           |  21 +++
 9 files changed, 379 insertions(+), 13 deletions(-)

index 840d7f8161..33c98425b4 100644
@@ -4739,6 +4753,110 @@ description | Waiting for a newly initialized WAL file to reach durable storage
    </tgroup>
   </table>

  <sect2 id="monitoring-stats-functions">
@@ -5278,6 +5396,22 @@ FROM pg_stat_get_backend_idset() AS backendid;
        </para></entry>
       </row>
 
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>pg_stat_get_session</primary>
+        </indexterm>
+        <function>pg_stat_get_session</function> ( <type>integer</type> )
+        <returnvalue>setof record</returnvalue>
+       </para>
+       <para>
+        Returns a row, showing statistics about the client backend with the
+        specified process ID, or one row per client backend
+        if <literal>NULL</literal> is specified. The fields returned are the
+        same as those of <structname>pg_stat_session</structname> view.
+       </para></entry>
+      </row>
+
       <row>
        <entry role="func_table_entry"><para role="func_signature">
         <indexterm>
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index da9a8fe99f..93a78f7384 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -886,6 +886,19 @@ CREATE VIEW pg_stat_activity AS
         LEFT JOIN pg_database AS D ON (S.datid = D.oid)
         LEFT JOIN pg_authid AS U ON (S.usesysid = U.oid);
 
index bdb3a296ca..1e3fc5ffec 100644
@@ -317,6 +317,8 @@ pgstat_bestart(void)
 
@@ -505,6 +507,9 @@ pgstat_report_activity(BackendState state, const char *cmd_str)

 	if (!beentry)
@@ -530,6 +535,7 @@ pgstat_report_activity(BackendState state, const char *cmd_str)
 			beentry->st_xact_start_timestamp = 0;
 			beentry->st_query_id = UINT64CONST(0);
 			proc->wait_event_info = 0;
+			MemSet(&beentry->st_session, 0, sizeof(beentry->st_session));
 			PGSTAT_END_WRITE_ACTIVITY(beentry);
 		}
 		return;
@@ -552,27 +558,45 @@ pgstat_report_activity(BackendState state, const char *cmd_str)
 	current_timestamp = GetCurrentTimestamp();
 
 	/*

 	/*
@@ -598,6 +622,20 @@ pgstat_report_activity(BackendState state, const char *cmd_str)
 		beentry->st_activity_start_timestamp = start_timestamp;
 	}

diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 60a397dc56..ba9ff1a2d6 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -296,6 +296,76 @@ pg_stat_get_progress_info(PG_FUNCTION_ARGS)
 	return (Datum) 0;
 }
 
+/*
index cbbe8acd38..f017de6779 100644
@@ -5559,6 +5559,15 @@
index 97874300c3..b2583b2824 100644
 
 } PgBackendGSSStatus;
 
+/*
 
 /* ----------
  * PgBackendStatus

index 3014d047fe..f37c57e220 100644
@@ -2106,6 +2106,16 @@ pg_stat_replication_slots| SELECT s.slot_name,
index 91089ac215..308780f119 100644
@@ -223,3 +223,42 @@ select count(distinct utc_offset) >= 24 as ok from pg_timezone_abbrevs;
  t
 (1 row)
 
+do $$
+begin
+raise 'test error';
+end;
+$$ language plpgsql;
+ERROR:  test error
+CONTEXT:  PL/pgSQL function inline_code_block line 3 at RAISE
index b2a7923754..cfa69a8975 100644
@@ -98,3 +98,24 @@ set timezone_abbreviations = 'Australia';
+do $$
+begin
+raise 'test error';
+end;
+$$ language plpgsql;

---

From: Sergey Dudoladov <[email-masked]>
Date: Mon, 3 Mar 2025 13:29:41 +0100

Hello,

I've rebased the patch according to the CI bot's requirements. Please have
a look.
Regards,
Sergey
From 4974c8c9440bbd6d7f9e37e882990602d69b495f Mon Sep 17 00:00:00 2001
From: Sergey Dudoladov <[email-masked]>
Date: Mon, 3 Mar 2025 13:19:56 +0100
Subject: [PATCH] Adds pg_stat_session view to track statistics accumulated
 during  lifetime of a session (client backend).

Discussion:

index 9178f1d34e..2f4b77ca52 100644
@@ -4772,6 +4786,110 @@ description | Waiting for a newly initialized WAL file to reach durable storage
    </tgroup>
   </table>

  <sect2 id="monitoring-stats-functions">
@@ -5277,6 +5395,22 @@ FROM pg_stat_get_backend_idset() AS backendid;
        </para></entry>
       </row>
 
+      <row>
index a4d2cfdcaf..0f191c9b8e 100644
@@ -895,6 +895,19 @@ CREATE VIEW pg_stat_activity AS
 
index 5f68ef26ad..d0c66f6d93 100644
 
@@ -509,6 +511,9 @@ pgstat_report_activity(BackendState state, const char *cmd_str)

 	if (!beentry)
@@ -534,6 +539,7 @@ pgstat_report_activity(BackendState state, const char *cmd_str)
 			beentry->st_xact_start_timestamp = 0;
 			beentry->st_query_id = UINT64CONST(0);
 			proc->wait_event_info = 0;
+			MemSet(&beentry->st_session, 0, sizeof(beentry->st_session));
 			PGSTAT_END_WRITE_ACTIVITY(beentry);
 		}
 		return;
@@ -556,27 +562,45 @@ pgstat_report_activity(BackendState state, const char *cmd_str)
 	current_timestamp = GetCurrentTimestamp();
 
 	/*

 	/*
@@ -602,6 +626,20 @@ pgstat_report_activity(BackendState state, const char *cmd_str)
 		beentry->st_activity_start_timestamp = start_timestamp;
 	}

diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 0212d8d590..8a8d185793 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -324,6 +324,76 @@ pg_stat_get_progress_info(PG_FUNCTION_ARGS)
 	return (Datum) 0;
 }
 
+/*
index cd9422d0ba..f78d9a8c45 100644
@@ -5584,6 +5584,15 @@
index d3d4ff6c5c..a04c3581d6 100644
 
 } PgBackendGSSStatus;
 
+/*
 
 /* ----------
  * PgBackendStatus

index 62f69ac20b..81b6de5935 100644
@@ -2114,6 +2114,16 @@ pg_stat_replication_slots| SELECT s.slot_name,
index 83228cfca2..51fd9bee6d 100644
@@ -232,3 +232,42 @@ select * from pg_timezone_abbrevs where abbrev = 'LMT';
  LMT    | @ 7 hours 52 mins 58 secs ago | f
 (1 row)
 
+select active_count as prev_active_count from pg_stat_session where pid = pg_backend_pid() \gset
+select (select active_count from pg_stat_session where pid = pg_backend_pid()) - :prev_active_count = 1 as ok;
+ ok 
+----
+ t
+(1 row)
+
+select idle_count as prev_idle_count from pg_stat_session where pid = pg_backend_pid() \gset
+select (select idle_count from pg_stat_session where pid = pg_backend_pid()) - :prev_idle_count = 1 as ok;
+ ok 
+----
+ t
+(1 row)
+
+select idle_in_transaction_count as prev_idle_in_transaction_count from pg_stat_session where pid = pg_backend_pid() \gset
+begin;
+commit;
+select (select idle_in_transaction_count from pg_stat_session where pid = pg_backend_pid()) - :prev_idle_in_transaction_count = 1 as ok;
+ ok 
+----
+ t
+(1 row)
+
+select idle_in_transaction_aborted_count as prev_idle_in_transaction_aborted_count from pg_stat_session where pid = pg_backend_pid() \gset
+begin;
+do $$
+begin
+raise 'test error';
+end;
+$$ language plpgsql;
+ERROR:  test error
+CONTEXT:  PL/pgSQL function inline_code_block line 3 at RAISE
+rollback;
+select (select idle_in_transaction_aborted_count from pg_stat_session where pid = pg_backend_pid()) - :prev_idle_in_transaction_aborted_count = 1 as ok;
+ ok 
+----
+ t
+(1 row)
+
diff --git a/src/test/regress/sql/sysviews.sql b/src/test/regress/sql/sysviews.sql
index 66179f026b..b90a911d81 100644
--- a/src/test/regress/sql/sysviews.sql
+++ b/src/test/regress/sql/sysviews.sql
@@ -101,3 +101,24 @@ select count(distinct utc_offset) >= 24 as ok from pg_timezone_abbrevs;
 -- One specific case we can check without much fear of breakage
 -- is the historical local-mean-time value used for America/Los_Angeles.
 select * from pg_timezone_abbrevs where abbrev = 'LMT';
+
+select active_count as prev_active_count from pg_stat_session where pid = pg_backend_pid() \gset
+select (select active_count from pg_stat_session where pid = pg_backend_pid()) - :prev_active_count = 1 as ok;
+
+select idle_count as prev_idle_count from pg_stat_session where pid = pg_backend_pid() \gset
+select (select idle_count from pg_stat_session where pid = pg_backend_pid()) - :prev_idle_count = 1 as ok;
+
+select idle_in_transaction_count as prev_idle_in_transaction_count from pg_stat_session where pid = pg_backend_pid() \gset
+begin;
+commit;
+select (select idle_in_transaction_count from pg_stat_session where pid = pg_backend_pid()) - :prev_idle_in_transaction_count = 1 as ok;
+
+select idle_in_transaction_aborted_count as prev_idle_in_transaction_aborted_count from pg_stat_session where pid = pg_backend_pid() \gset
+begin;
+do $$
+begin
+raise 'test error';
+end;
+$$ language plpgsql;
+rollback;
+select (select idle_in_transaction_aborted_count from pg_stat_session where pid = pg_backend_pid()) - :prev_idle_in_transaction_aborted_count = 1 as ok;
-- 
2.34.1

---

From: Sadeq Dousti <[email-masked]>
Date: Thu, 13 Mar 2025 05:50:58 +0100

> I've rebased the patch according to the CI bot's requirements. Please have

Dear Sergey & Hackers,

+1 to the idea, and hope it becomes available in v18.

Here are some observations from my review:

1. backend_status.c, Line 572 - the following new condition can break the
logic of existing code, please move it inside the IF body, where you
compute values for pg_stat_session.
beentry->st_backendType == B_BACKEND

2. Macros PGSTAT_IS_* do not seem to help a lot in code readability, I
prefer the original code

3. backend_status.h, Line 193 - Can st_session be defined as a pointer to
PgBackendSessionStatus? (Similar to st_sslstatus and st_gssstatus)?
This helps in "shallow" copying PgBackendStatus.

4. backend_status.h, Lines 93-101 - Can these fields be defined as uint64
rather than int64, since they are unsigned in reality?

5. Changes to pg_proc.dat - Andrey Borodin in this video at 46:05 (
https://youtu.be/vTV8XhWf3mo?t=2765) points out that it should be changed
by committers only; otherwise frequent conflicts happen.

6. pgstatfuncs.c, line 333: The following line is defined inside the
function pg_stat_get_session, but I think it's better suited for
pgstatfuncs.h (though existing function - pg_stat_get_progress_info - does
the same)
#define PG_STAT_GET_SESSION_COLS 9

7. Some information is currently scattered between pg_stat_session and
pg_stat_activity, making joins necessary. However, pg_stat_activity
contains two types of columns: Those being immutable during a session
(e.g., client_addr, application_name, usename) and those being volatile
during the session (e.g., query, query_start, state). The following is thus
a "careless" join, combining immutable, volatile, and cumulative info
together:

select * from pg_stat_activity join pg_stat_session using (pid);

I suggest adding all or some of the immutable columns of pg_stat_activity
to pg_stat_session, thus excluding the need to join the two views (for all
practical purposes).

Best Regards,
Sadeq Dousti

---

From: Robert Haas <[email-masked]>
Date: Thu, 10 Apr 2025 14:46:44 -0400

Hi,

For the most part this patch looks like it's in pretty good shape to
me, although I am less sold on the desirability of it than it sounds
like most people are. I do think it can be useful, and it seems to
mostly piggyback on existing time measurements, so it should be pretty
cheap. But I'm just not quite sure about the design.

While the number of times that we enter each state does seem to have
some utility, it's also a little weird. If I'm not mistaken, the total
number of times you've become idle (any flavor) has got to add up to
the number of times that you've become active, or it can be 1 less if
you're still active. So that seems a bit redundant: why count four
things where three of them basically sum up to the fourth? Basically,
I think you end up with the times-active counter counting how many
statements you executed, the times-idle counter counting how many of
those statements were not in explicit transactions, and the other two
idle flavors counting how many statements you ran within explicit
transactions, divided between statements that errored out and
statements that didn't. I certainly don't think that's useless, but
there are other choices that seem equally valid; e.g. most obviously,
we could count how many statements committed and and how many
statements aborted, which seems like it might be more generally useful
than this, even though this also has some use. The thing that concerns
me is that I don't think we ever want both. If we add this, we're more
or less saying we're never going to do that, or at least I think so.

I'm also not sure that I like the idea of breaking this out into a
separate pg_stat_session view, as Andres proposed. It might be the
right idea, but if you want to see everything, you'll now need to join
the two views -- and there's no guarantee that you'll get a consistent
read across both of them, so the join might do weird things unless
you're very careful.

If we do decide to keep it separate, I think we should consider
omitting rows entirely when the HAS_PGSTAT_PERMISSIONS check fails. In
the case of pg_stat_activity, we still display some useful information
even to unprivileged users, but here all you get without privileges is
the pid. Maybe there is some point to that if we think we might add
unprivileged columns later or if we think it's important to be
consistent with pg_stat_activity, but I'm sort of inclined to think
it's just clutter.

Cuddled braces are not project style. You will find that code like "}
else if (PGSTAT_IS_IDLE(beentry)){" gats reindented by pgindent; but
it's best to run pgindent before submitting.

I'd probably write the increments as ++ rather than += 1 but I'm not
sure if everyone would agree.

--
Robert Haas
EDB: http://www.enterprisedb.com

---

From: Richard Guo <[email-masked]>
Date: Fri, 11 Apr 2025 10:41:14 +0900

> sure if everyone would agree.

I'm a fan of ++ as well, so I agree.

Thanks
Richard

---

From: Rafia Sabih <[email-masked]>
Date: Mon, 16 Feb 2026 19:13:57 +0100

On Thu, 10 Apr 2025 at 18:41, Richard Guo <[email-masked]> wrote:

> Looking at this patch and the idea altogether I have other thoughts on
this now.
Thinking about the original problem of having a count of total idle in
transaction time, seems unnecessary with the availability of state_change
timestamp along with state columns in pg_stat_activity.

-- 
Regards,
Rafia Sabih
CYBERTEC PostgreSQL International GmbH