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 <sergey.dudola...@gmail.com> 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).
catversion bump is necessary due to a new view / function Reviewed-by: Aleksander Alekseev, Bertrand Drouvot, Atsushi Torikoshi, and Andrei Zubkov 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(-) diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index 9178f1d34e..2f4b77ca52 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> @@ -4772,6 +4786,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"> @@ -5277,6 +5395,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 a4d2cfdcaf..0f191c9b8e 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -895,6 +895,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); +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 5f68ef26ad..d0c66f6d93 100644 --- a/src/backend/utils/activity/backend_status.c +++ b/src/backend/utils/activity/backend_status.c @@ -317,6 +317,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 @@ -509,6 +511,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) @@ -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(); /* - * 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; 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; + } } /* @@ -602,6 +626,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 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; } +/* + * 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 cd9422d0ba..f78d9a8c45 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -5584,6 +5584,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 d3d4ff6c5c..a04c3581d6 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 @@ -170,6 +188,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; @@ -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 62f69ac20b..81b6de5935 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -2114,6 +2114,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 83228cfca2..51fd9bee6d 100644 --- a/src/test/regress/expected/sysviews.out +++ b/src/test/regress/expected/sysviews.out @@ -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
