agora inbox for pgsql-hackers@postgresql.orghelp / color / mirror / Atom feed
[PATCH v2] Repurpose PROC_COPYABLE_FLAGS as PROC_XMIN_FLAGS 7+ messages / 2 participants [nested] [flat]
* [PATCH v2] Repurpose PROC_COPYABLE_FLAGS as PROC_XMIN_FLAGS @ 2022-05-14 14:51 Alvaro Herrera <alvherre@alvh.no-ip.org> 0 siblings, 0 replies; 7+ messages in thread From: Alvaro Herrera @ 2022-05-14 14:51 UTC (permalink / raw) This is a slight, convenient semantics change from what commit 0f0cfb494004 introduced that lets us simplify the coding in the one place where it is used. --- src/backend/storage/ipc/procarray.c | 17 +++++++---------- src/include/storage/proc.h | 7 +++---- 2 files changed, 10 insertions(+), 14 deletions(-) diff --git a/src/backend/storage/ipc/procarray.c b/src/backend/storage/ipc/procarray.c index ca22336e35..cd58c5faf0 100644 --- a/src/backend/storage/ipc/procarray.c +++ b/src/backend/storage/ipc/procarray.c @@ -2685,17 +2685,14 @@ ProcArrayInstallRestoredXmin(TransactionId xmin, PGPROC *proc) TransactionIdIsNormal(xid) && TransactionIdPrecedesOrEquals(xid, xmin)) { - /* Install xmin */ + /* + * Install xmin and propagate the statusFlags that affect how the + * value is interpreted by vacuum. + */ MyProc->xmin = TransactionXmin = xmin; - - /* walsender cheats by passing proc == MyProc, don't check its flags */ - if (proc != MyProc) - { - /* Flags being copied must be valid copy-able flags. */ - Assert((proc->statusFlags & (~PROC_COPYABLE_FLAGS)) == 0); - MyProc->statusFlags = proc->statusFlags; - ProcGlobal->statusFlags[MyProc->pgxactoff] = MyProc->statusFlags; - } + MyProc->statusFlags = (MyProc->statusFlags & ~PROC_XMIN_FLAGS) | + (proc->statusFlags & PROC_XMIN_FLAGS); + ProcGlobal->statusFlags[MyProc->pgxactoff] = MyProc->statusFlags; result = true; } diff --git a/src/include/storage/proc.h b/src/include/storage/proc.h index 15be21c00a..2579e619eb 100644 --- a/src/include/storage/proc.h +++ b/src/include/storage/proc.h @@ -69,11 +69,10 @@ struct XidCache (PROC_IN_VACUUM | PROC_IN_SAFE_IC | PROC_VACUUM_FOR_WRAPAROUND) /* - * Flags that are valid to copy from another proc, the parallel leader - * process in practice. Currently, flags that are set during parallel - * vacuum and parallel index creation are allowed. + * Xmin-related flags. Make sure any flags that affect how the process' Xmin + * value is interpreted by VACUUM are included here. */ -#define PROC_COPYABLE_FLAGS (PROC_IN_VACUUM | PROC_IN_SAFE_IC) +#define PROC_XMIN_FLAGS (PROC_IN_VACUUM | PROC_IN_SAFE_IC) /* * We allow a small number of "weak" relation locks (AccessShareLock, -- 2.30.2 --zgl76stv2tlmzxb2-- ^ permalink raw reply [nested|flat] 7+ messages in thread
* [PATCH] Repurpose PROC_COPYABLE_FLAGS as PROC_XMIN_FLAGS @ 2022-05-14 14:51 Alvaro Herrera <alvherre@alvh.no-ip.org> 0 siblings, 0 replies; 7+ messages in thread From: Alvaro Herrera @ 2022-05-14 14:51 UTC (permalink / raw) This is a slight, convenient semantics change from what commit 0f0cfb494004 introduced that lets us simplify the coding in the one place where it is used. --- src/backend/storage/ipc/procarray.c | 11 ++++++----- src/include/storage/proc.h | 7 +++---- 2 files changed, 9 insertions(+), 9 deletions(-) diff --git a/src/backend/storage/ipc/procarray.c b/src/backend/storage/ipc/procarray.c index 6ff8d8699b..447b6e3de7 100644 --- a/src/backend/storage/ipc/procarray.c +++ b/src/backend/storage/ipc/procarray.c @@ -1916,12 +1916,13 @@ ProcArrayInstallRestoredXmin(TransactionId xmin, PGPROC *proc) TransactionIdIsNormal(xid) && TransactionIdPrecedesOrEquals(xid, xmin)) { - /* Install xmin */ + /* + * Install xmin and propagate the vacuumFlags that affect how the + * value is interpreted by vacuum. + */ MyPgXact->xmin = TransactionXmin = xmin; - - /* Flags being copied must be valid copy-able flags. */ - Assert((pgxact->vacuumFlags & (~PROC_COPYABLE_FLAGS)) == 0); - MyPgXact->vacuumFlags = pgxact->vacuumFlags; + MyPgXact->vacuumFlags = (MyPgXact->vacuumFlags & ~PROC_XMIN_FLAGS) | + (pgxact->vacuumFlags & PROC_XMIN_FLAGS); result = true; } diff --git a/src/include/storage/proc.h b/src/include/storage/proc.h index 9cf9684b41..7c85b5645b 100644 --- a/src/include/storage/proc.h +++ b/src/include/storage/proc.h @@ -63,11 +63,10 @@ struct XidCache (PROC_IN_VACUUM | PROC_IN_ANALYZE | PROC_VACUUM_FOR_WRAPAROUND) /* - * Flags that are valid to copy from another proc, the parallel leader - * process in practice. Currently, a flag that is set during parallel - * vacuum is allowed. + * Xmin-related flags. Make sure any flags that affect how the process' Xmin + * value is interpreted by VACUUM are included here. */ -#define PROC_COPYABLE_FLAGS (PROC_IN_VACUUM) +#define PROC_XMIN_FLAGS (PROC_IN_VACUUM) /* * We allow a small number of "weak" relation locks (AccessShareLock, -- 2.30.2 --gseizntho2k3boor Content-Type: text/x-diff; charset=utf-8 Content-Disposition: attachment; filename="v14-0001-Repurpose-PROC_COPYABLE_FLAGS-as-PROC_XMIN_FLAGS.patch" ^ permalink raw reply [nested|flat] 7+ messages in thread
* [PATCH] Repurpose PROC_COPYABLE_FLAGS as PROC_XMIN_FLAGS @ 2022-05-14 14:51 Alvaro Herrera <alvherre@alvh.no-ip.org> 0 siblings, 0 replies; 7+ messages in thread From: Alvaro Herrera @ 2022-05-14 14:51 UTC (permalink / raw) This is a slight, convenient semantics change from what commit 0f0cfb494004 introduced that lets us simplify the coding in the one place where it is used. --- src/backend/storage/ipc/procarray.c | 11 ++++++----- src/include/storage/proc.h | 7 +++---- 2 files changed, 9 insertions(+), 9 deletions(-) diff --git a/src/backend/storage/ipc/procarray.c b/src/backend/storage/ipc/procarray.c index 127be9c017..08053a7e8f 100644 --- a/src/backend/storage/ipc/procarray.c +++ b/src/backend/storage/ipc/procarray.c @@ -2686,12 +2686,13 @@ ProcArrayInstallRestoredXmin(TransactionId xmin, PGPROC *proc) TransactionIdIsNormal(xid) && TransactionIdPrecedesOrEquals(xid, xmin)) { - /* Install xmin */ + /* + * Install xmin and propagate the statusFlags that affect how the + * value is interpreted by vacuum. + */ MyProc->xmin = TransactionXmin = xmin; - - /* Flags being copied must be valid copy-able flags. */ - Assert((proc->statusFlags & (~PROC_COPYABLE_FLAGS)) == 0); - MyProc->statusFlags = proc->statusFlags; + MyProc->statusFlags = (MyProc->statusFlags & ~PROC_XMIN_FLAGS) | + (proc->statusFlags & PROC_XMIN_FLAGS); ProcGlobal->statusFlags[MyProc->pgxactoff] = MyProc->statusFlags; result = true; diff --git a/src/include/storage/proc.h b/src/include/storage/proc.h index 11b514c9ae..1464fad9b9 100644 --- a/src/include/storage/proc.h +++ b/src/include/storage/proc.h @@ -66,11 +66,10 @@ struct XidCache (PROC_IN_VACUUM | PROC_IN_SAFE_IC | PROC_VACUUM_FOR_WRAPAROUND) /* - * Flags that are valid to copy from another proc, the parallel leader - * process in practice. Currently, flags that are set during parallel - * vacuum and parallel index creation are allowed. + * Xmin-related flags. Make sure any flags that affect how the process' Xmin + * value is interpreted by VACUUM are included here. */ -#define PROC_COPYABLE_FLAGS (PROC_IN_VACUUM | PROC_IN_SAFE_IC) +#define PROC_XMIN_FLAGS (PROC_IN_VACUUM | PROC_IN_SAFE_IC) /* * We allow a small number of "weak" relation locks (AccessShareLock, -- 2.30.2 --gseizntho2k3boor-- ^ permalink raw reply [nested|flat] 7+ messages in thread
* [PATCH v2] Repurpose PROC_COPYABLE_FLAGS as PROC_XMIN_FLAGS @ 2022-05-14 14:51 Alvaro Herrera <alvherre@alvh.no-ip.org> 0 siblings, 0 replies; 7+ messages in thread From: Alvaro Herrera @ 2022-05-14 14:51 UTC (permalink / raw) This is a slight, convenient semantics change from what commit 0f0cfb494004 introduced that lets us simplify the coding in the one place where it is used. --- src/backend/storage/ipc/procarray.c | 17 +++++++---------- src/include/storage/proc.h | 7 +++---- 2 files changed, 10 insertions(+), 14 deletions(-) diff --git a/src/backend/storage/ipc/procarray.c b/src/backend/storage/ipc/procarray.c index ca22336e35..cd58c5faf0 100644 --- a/src/backend/storage/ipc/procarray.c +++ b/src/backend/storage/ipc/procarray.c @@ -2685,17 +2685,14 @@ ProcArrayInstallRestoredXmin(TransactionId xmin, PGPROC *proc) TransactionIdIsNormal(xid) && TransactionIdPrecedesOrEquals(xid, xmin)) { - /* Install xmin */ + /* + * Install xmin and propagate the statusFlags that affect how the + * value is interpreted by vacuum. + */ MyProc->xmin = TransactionXmin = xmin; - - /* walsender cheats by passing proc == MyProc, don't check its flags */ - if (proc != MyProc) - { - /* Flags being copied must be valid copy-able flags. */ - Assert((proc->statusFlags & (~PROC_COPYABLE_FLAGS)) == 0); - MyProc->statusFlags = proc->statusFlags; - ProcGlobal->statusFlags[MyProc->pgxactoff] = MyProc->statusFlags; - } + MyProc->statusFlags = (MyProc->statusFlags & ~PROC_XMIN_FLAGS) | + (proc->statusFlags & PROC_XMIN_FLAGS); + ProcGlobal->statusFlags[MyProc->pgxactoff] = MyProc->statusFlags; result = true; } diff --git a/src/include/storage/proc.h b/src/include/storage/proc.h index 15be21c00a..2579e619eb 100644 --- a/src/include/storage/proc.h +++ b/src/include/storage/proc.h @@ -69,11 +69,10 @@ struct XidCache (PROC_IN_VACUUM | PROC_IN_SAFE_IC | PROC_VACUUM_FOR_WRAPAROUND) /* - * Flags that are valid to copy from another proc, the parallel leader - * process in practice. Currently, flags that are set during parallel - * vacuum and parallel index creation are allowed. + * Xmin-related flags. Make sure any flags that affect how the process' Xmin + * value is interpreted by VACUUM are included here. */ -#define PROC_COPYABLE_FLAGS (PROC_IN_VACUUM | PROC_IN_SAFE_IC) +#define PROC_XMIN_FLAGS (PROC_IN_VACUUM | PROC_IN_SAFE_IC) /* * We allow a small number of "weak" relation locks (AccessShareLock, -- 2.30.2 --zgl76stv2tlmzxb2-- ^ permalink raw reply [nested|flat] 7+ messages in thread
* [PATCH] Repurpose PROC_COPYABLE_FLAGS as PROC_XMIN_FLAGS @ 2022-05-14 14:51 Alvaro Herrera <alvherre@alvh.no-ip.org> 0 siblings, 0 replies; 7+ messages in thread From: Alvaro Herrera @ 2022-05-14 14:51 UTC (permalink / raw) This is a slight, convenient semantics change from what commit 0f0cfb494004 introduced that lets us simplify the coding in the one place where it is used. --- src/backend/storage/ipc/procarray.c | 11 ++++++----- src/include/storage/proc.h | 7 +++---- 2 files changed, 9 insertions(+), 9 deletions(-) diff --git a/src/backend/storage/ipc/procarray.c b/src/backend/storage/ipc/procarray.c index 6ff8d8699b..447b6e3de7 100644 --- a/src/backend/storage/ipc/procarray.c +++ b/src/backend/storage/ipc/procarray.c @@ -1916,12 +1916,13 @@ ProcArrayInstallRestoredXmin(TransactionId xmin, PGPROC *proc) TransactionIdIsNormal(xid) && TransactionIdPrecedesOrEquals(xid, xmin)) { - /* Install xmin */ + /* + * Install xmin and propagate the vacuumFlags that affect how the + * value is interpreted by vacuum. + */ MyPgXact->xmin = TransactionXmin = xmin; - - /* Flags being copied must be valid copy-able flags. */ - Assert((pgxact->vacuumFlags & (~PROC_COPYABLE_FLAGS)) == 0); - MyPgXact->vacuumFlags = pgxact->vacuumFlags; + MyPgXact->vacuumFlags = (MyPgXact->vacuumFlags & ~PROC_XMIN_FLAGS) | + (pgxact->vacuumFlags & PROC_XMIN_FLAGS); result = true; } diff --git a/src/include/storage/proc.h b/src/include/storage/proc.h index 9cf9684b41..7c85b5645b 100644 --- a/src/include/storage/proc.h +++ b/src/include/storage/proc.h @@ -63,11 +63,10 @@ struct XidCache (PROC_IN_VACUUM | PROC_IN_ANALYZE | PROC_VACUUM_FOR_WRAPAROUND) /* - * Flags that are valid to copy from another proc, the parallel leader - * process in practice. Currently, a flag that is set during parallel - * vacuum is allowed. + * Xmin-related flags. Make sure any flags that affect how the process' Xmin + * value is interpreted by VACUUM are included here. */ -#define PROC_COPYABLE_FLAGS (PROC_IN_VACUUM) +#define PROC_XMIN_FLAGS (PROC_IN_VACUUM) /* * We allow a small number of "weak" relation locks (AccessShareLock, -- 2.30.2 --gseizntho2k3boor Content-Type: text/x-diff; charset=utf-8 Content-Disposition: attachment; filename="v14-0001-Repurpose-PROC_COPYABLE_FLAGS-as-PROC_XMIN_FLAGS.patch" ^ permalink raw reply [nested|flat] 7+ messages in thread
* [PATCH] Repurpose PROC_COPYABLE_FLAGS as PROC_XMIN_FLAGS @ 2022-05-14 14:51 Alvaro Herrera <alvherre@alvh.no-ip.org> 0 siblings, 0 replies; 7+ messages in thread From: Alvaro Herrera @ 2022-05-14 14:51 UTC (permalink / raw) This is a slight, convenient semantics change from what commit 0f0cfb494004 introduced that lets us simplify the coding in the one place where it is used. --- src/backend/storage/ipc/procarray.c | 11 ++++++----- src/include/storage/proc.h | 7 +++---- 2 files changed, 9 insertions(+), 9 deletions(-) diff --git a/src/backend/storage/ipc/procarray.c b/src/backend/storage/ipc/procarray.c index 127be9c017..08053a7e8f 100644 --- a/src/backend/storage/ipc/procarray.c +++ b/src/backend/storage/ipc/procarray.c @@ -2686,12 +2686,13 @@ ProcArrayInstallRestoredXmin(TransactionId xmin, PGPROC *proc) TransactionIdIsNormal(xid) && TransactionIdPrecedesOrEquals(xid, xmin)) { - /* Install xmin */ + /* + * Install xmin and propagate the statusFlags that affect how the + * value is interpreted by vacuum. + */ MyProc->xmin = TransactionXmin = xmin; - - /* Flags being copied must be valid copy-able flags. */ - Assert((proc->statusFlags & (~PROC_COPYABLE_FLAGS)) == 0); - MyProc->statusFlags = proc->statusFlags; + MyProc->statusFlags = (MyProc->statusFlags & ~PROC_XMIN_FLAGS) | + (proc->statusFlags & PROC_XMIN_FLAGS); ProcGlobal->statusFlags[MyProc->pgxactoff] = MyProc->statusFlags; result = true; diff --git a/src/include/storage/proc.h b/src/include/storage/proc.h index 11b514c9ae..1464fad9b9 100644 --- a/src/include/storage/proc.h +++ b/src/include/storage/proc.h @@ -66,11 +66,10 @@ struct XidCache (PROC_IN_VACUUM | PROC_IN_SAFE_IC | PROC_VACUUM_FOR_WRAPAROUND) /* - * Flags that are valid to copy from another proc, the parallel leader - * process in practice. Currently, flags that are set during parallel - * vacuum and parallel index creation are allowed. + * Xmin-related flags. Make sure any flags that affect how the process' Xmin + * value is interpreted by VACUUM are included here. */ -#define PROC_COPYABLE_FLAGS (PROC_IN_VACUUM | PROC_IN_SAFE_IC) +#define PROC_XMIN_FLAGS (PROC_IN_VACUUM | PROC_IN_SAFE_IC) /* * We allow a small number of "weak" relation locks (AccessShareLock, -- 2.30.2 --gseizntho2k3boor-- ^ permalink raw reply [nested|flat] 7+ messages in thread
* [PATCH v5 3/3] Adding the pg_stat_backend_transaction view @ 2025-08-09 14:22 Bertrand Drouvot <bertranddrouvot.pg@gmail.com> 0 siblings, 0 replies; 7+ messages in thread From: Bertrand Drouvot @ 2025-08-09 14:22 UTC (permalink / raw) This view displays one row per server process, showing transaction statistics related to the current activity of that process. It currently displays the pid, the number of XIDs generated, the number of commits, the number of rollbacks and the time at which these statistics were last reset. It's built on top of a new function (pg_stat_get_backend_transactions()). The idea is the same as pg_stat_activity and pg_stat_get_activity(). Adding documentation and tests. XXX: Bump catversion --- doc/src/sgml/monitoring.sgml | 115 +++++++++++++++++++++++++++ src/backend/catalog/system_views.sql | 9 +++ src/backend/utils/adt/pgstatfuncs.c | 65 +++++++++++++++ src/include/catalog/pg_proc.dat | 9 +++ src/test/regress/expected/rules.out | 6 ++ src/test/regress/expected/stats.out | 17 ++++ src/test/regress/sql/stats.sql | 10 +++ 7 files changed, 231 insertions(+) 49.8% doc/src/sgml/ 3.1% src/backend/catalog/ 25.8% src/backend/utils/adt/ 6.8% src/include/catalog/ 9.2% src/test/regress/expected/ 4.9% src/test/regress/sql/ diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index bb75ed1069b..86200553293 100644 --- a/doc/src/sgml/monitoring.sgml +++ b/doc/src/sgml/monitoring.sgml @@ -320,6 +320,20 @@ postgres 27093 0.0 0.0 30096 2752 ? Ss 11:34 0:00 postgres: ser </entry> </row> + <row> + <entry> + <structname>pg_stat_backend_transaction</structname> + <indexterm><primary>pg_stat_backend_transaction</primary></indexterm> + </entry> + <entry> + One row per server process, showing statistics related to + the current activity of that process, such as number of commits and + rollbacks. + See <link linkend="monitoring-pg-stat-backend-transaction-view"> + <structname>pg_stat_backend_transaction</structname></link> for details. + </entry> + </row> + <row> <entry><structname>pg_stat_replication</structname><indexterm><primary>pg_stat_replication</primary></indexterm></entry> <entry>One row per WAL sender process, showing statistics about @@ -1195,6 +1209,91 @@ description | Waiting for a newly initialized WAL file to reach durable storage </note> </sect2> + <sect2 id="monitoring-pg-stat-backend-transaction-view"> + <title><structname>pg_stat_backend_transaction</structname></title> + + <indexterm> + <primary>pg_stat_backend_transaction</primary> + </indexterm> + + <para> + The <structname>pg_stat_backend_transaction</structname> view will have one row + per server process, showing statistics related to + the current activity of that process. + </para> + + <table id="pg-stat-backend-transaction-view" xreflabel="pg_stat_backend_transaction"> + <title><structname>pg_stat_backend_transaction</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>xid_count</structfield> <type>bigint</type> + </para> + <para> + The number of XID that have been generated by the backend. It does not take + into account virtual transaction ID on purpose. + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>xact_commit</structfield> <type>bigint</type> + </para> + <para> + The number of transactions that have been committed. + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>xact_rollback</structfield> <type>bigint</type> + </para> + <para> + The number of transactions that have been rolled back. + </para></entry> + </row> + + <row> + <entry role="catalog_table_entry"><para role="column_definition"> + <structfield>stats_reset</structfield> <type>timestamp with time zone</type> + </para> + <para> + Time at which these statistics were last reset + </para></entry> + </row> + </tbody> + </tgroup> + </table> + + <note> + <para> + The view does not return statistics for the checkpointer, + the background writer, the startup process and the autovacuum launcher. + </para> + </note> + </sect2> + <sect2 id="monitoring-pg-stat-replication-view"> <title><structname>pg_stat_replication</structname></title> @@ -5342,6 +5441,22 @@ description | Waiting for a newly initialized WAL file to reach durable storage </para></entry> </row> + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm> + <primary>pg_stat_get_backend_transactions</primary> + </indexterm> + <function>pg_stat_get_backend_transactions</function> ( <type>integer</type> ) + <returnvalue>setof record</returnvalue> + </para> + <para> + Returns a record of transaction statistics 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_backend_transaction</structname> view. + </para></entry> + </row> + <row> <entry id="pg-stat-get-backend-wal" 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 e54018004db..e005eaeb579 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -946,6 +946,15 @@ 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_backend_transaction AS + SELECT + S.pid, + S.xid_count, + S.xact_commit, + S.xact_rollback, + S.stats_reset + FROM pg_stat_get_backend_transactions(NULL) AS S; + CREATE VIEW pg_stat_replication AS SELECT S.pid, diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c index 9185a8e6b83..77ee9222ccf 100644 --- a/src/backend/utils/adt/pgstatfuncs.c +++ b/src/backend/utils/adt/pgstatfuncs.c @@ -708,6 +708,71 @@ pg_stat_get_activity(PG_FUNCTION_ARGS) return (Datum) 0; } +/* + * Returns transactions statistics of PG backends. + */ +Datum +pg_stat_get_backend_transactions(PG_FUNCTION_ARGS) +{ +#define PG_STAT_GET_BACKEND_STATS_COLS 5 + 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_BACKEND_STATS_COLS] = {0}; + bool nulls[PG_STAT_GET_BACKEND_STATS_COLS] = {0}; + LocalPgBackendStatus *local_beentry; + PgBackendStatus *beentry; + PgStat_Backend *backend_stats; + + /* Get the next one in the list */ + local_beentry = pgstat_get_local_beentry_by_index(curr_backend); + beentry = &local_beentry->backendStatus; + + /* If looking for specific PID, ignore all the others */ + if (pid != -1 && beentry->st_procpid != pid) + continue; + + /* check if the backend type tracks statistics */ + if (!pgstat_tracks_backend_bktype(beentry->st_backendType)) + continue; + + /* + * Don't use pgstat_fetch_stat_backend_by_pid() to avoid holding the + * ProcArrayLock during each iteration. + */ + backend_stats = pgstat_fetch_stat_backend(local_beentry->proc_number); + + values[0] = Int32GetDatum(beentry->st_procpid); + + if (!backend_stats) + continue; + + values[1] = Int64GetDatum(backend_stats->xid_count); + values[2] = Int64GetDatum(backend_stats->xact_commit); + values[3] = Int64GetDatum(backend_stats->xact_rollback); + + if (backend_stats->stat_reset_timestamp != 0) + values[4] = TimestampTzGetDatum(backend_stats->stat_reset_timestamp); + else + nulls[4] = 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; +} Datum pg_backend_pid(PG_FUNCTION_ARGS) diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 3579cec5744..6a686e93fdd 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -5680,6 +5680,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 => '9555', + descr => 'statistics: statistics about currently active backends', + proname => 'pg_stat_get_backend_transactions', prorows => '100', proisstrict => 'f', + proretset => 't', provolatile => 's', proparallel => 'r', + prorettype => 'record', proargtypes => 'int4', + proallargtypes => '{int4,int4,int8,int8,int8,timestamptz}', + proargmodes => '{i,o,o,o,o,o}', + proargnames => '{pid,pid,xid_count,xact_commit,xact_rollback,stats_reset}', + prosrc => 'pg_stat_get_backend_transactions' }, { oid => '6318', descr => 'describe wait events', proname => 'pg_get_wait_events', procost => '10', prorows => '250', proretset => 't', provolatile => 'v', prorettype => 'record', diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index 2b3cf6d8569..2b4ea519677 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -1860,6 +1860,12 @@ pg_stat_archiver| SELECT archived_count, last_failed_time, stats_reset FROM pg_stat_get_archiver() s(archived_count, last_archived_wal, last_archived_time, failed_count, last_failed_wal, last_failed_time, stats_reset); +pg_stat_backend_transaction| SELECT pid, + xid_count, + xact_commit, + xact_rollback, + stats_reset + FROM pg_stat_get_backend_transactions(NULL::integer) s(pid, xid_count, xact_commit, xact_rollback, stats_reset); pg_stat_bgwriter| SELECT pg_stat_get_bgwriter_buf_written_clean() AS buffers_clean, pg_stat_get_bgwriter_maxwritten_clean() AS maxwritten_clean, pg_stat_get_buf_alloc() AS buffers_alloc, diff --git a/src/test/regress/expected/stats.out b/src/test/regress/expected/stats.out index ea7f7846895..e3f55a4c0ea 100644 --- a/src/test/regress/expected/stats.out +++ b/src/test/regress/expected/stats.out @@ -135,11 +135,28 @@ INSERT INTO trunc_stats_test1 DEFAULT VALUES; INSERT INTO trunc_stats_test1 DEFAULT VALUES; UPDATE trunc_stats_test1 SET id = id + 10 WHERE id IN (1, 2); DELETE FROM trunc_stats_test1 WHERE id = 3; +-- in passing, check that backend's commit is incrementing +SELECT xact_commit AS xact_commit_before + FROM pg_stat_backend_transaction WHERE pid = pg_backend_pid() \gset BEGIN; UPDATE trunc_stats_test1 SET id = id + 100; TRUNCATE trunc_stats_test1; INSERT INTO trunc_stats_test1 DEFAULT VALUES; COMMIT; +SELECT pg_stat_force_next_flush(); + pg_stat_force_next_flush +-------------------------- + +(1 row) + +SELECT xact_commit AS xact_commit_after + FROM pg_stat_backend_transaction WHERE pid = pg_backend_pid() \gset +SELECT :xact_commit_after > :xact_commit_before; + ?column? +---------- + t +(1 row) + -- use a savepoint: 1 insert, 1 live BEGIN; INSERT INTO trunc_stats_test2 DEFAULT VALUES; diff --git a/src/test/regress/sql/stats.sql b/src/test/regress/sql/stats.sql index 65d8968c83e..e6b35593a95 100644 --- a/src/test/regress/sql/stats.sql +++ b/src/test/regress/sql/stats.sql @@ -58,12 +58,22 @@ INSERT INTO trunc_stats_test1 DEFAULT VALUES; UPDATE trunc_stats_test1 SET id = id + 10 WHERE id IN (1, 2); DELETE FROM trunc_stats_test1 WHERE id = 3; +-- in passing, check that backend's commit is incrementing +SELECT xact_commit AS xact_commit_before + FROM pg_stat_backend_transaction WHERE pid = pg_backend_pid() \gset + BEGIN; UPDATE trunc_stats_test1 SET id = id + 100; TRUNCATE trunc_stats_test1; INSERT INTO trunc_stats_test1 DEFAULT VALUES; COMMIT; +SELECT pg_stat_force_next_flush(); +SELECT xact_commit AS xact_commit_after + FROM pg_stat_backend_transaction WHERE pid = pg_backend_pid() \gset + +SELECT :xact_commit_after > :xact_commit_before; + -- use a savepoint: 1 insert, 1 live BEGIN; INSERT INTO trunc_stats_test2 DEFAULT VALUES; -- 2.34.1 --uN5gGi2rtCNSVbpC-- ^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2025-08-09 14:22 UTC | newest] Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2022-05-14 14:51 [PATCH v2] Repurpose PROC_COPYABLE_FLAGS as PROC_XMIN_FLAGS Alvaro Herrera <alvherre@alvh.no-ip.org> 2022-05-14 14:51 [PATCH] Repurpose PROC_COPYABLE_FLAGS as PROC_XMIN_FLAGS Alvaro Herrera <alvherre@alvh.no-ip.org> 2022-05-14 14:51 [PATCH] Repurpose PROC_COPYABLE_FLAGS as PROC_XMIN_FLAGS Alvaro Herrera <alvherre@alvh.no-ip.org> 2022-05-14 14:51 [PATCH v2] Repurpose PROC_COPYABLE_FLAGS as PROC_XMIN_FLAGS Alvaro Herrera <alvherre@alvh.no-ip.org> 2022-05-14 14:51 [PATCH] Repurpose PROC_COPYABLE_FLAGS as PROC_XMIN_FLAGS Alvaro Herrera <alvherre@alvh.no-ip.org> 2022-05-14 14:51 [PATCH] Repurpose PROC_COPYABLE_FLAGS as PROC_XMIN_FLAGS Alvaro Herrera <alvherre@alvh.no-ip.org> 2025-08-09 14:22 [PATCH v5 3/3] Adding the pg_stat_backend_transaction view Bertrand Drouvot <bertranddrouvot.pg@gmail.com>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox