From 886a02d1cb19fe25859bd3c2d1f6a64b04cdc710 Mon Sep 17 00:00:00 2001 From: Neil Conway Date: Wed, 6 Dec 2006 18:06:48 +0000 Subject: [PATCH] Add a txn_start column to pg_stat_activity. This makes it easier to identify long-running transactions. Since we already need to record the transaction-start time (e.g. for now()), we don't need any additional system calls to report this information. Catversion bumped, initdb required. --- doc/src/sgml/monitoring.sgml | 22 +++++++++++----------- src/backend/access/transam/xact.c | 5 ++++- src/backend/catalog/system_views.sql | 3 ++- src/backend/postmaster/pgstat.c | 26 +++++++++++++++++++++++++- src/backend/utils/adt/pgstatfuncs.c | 26 +++++++++++++++++++++++++- src/include/catalog/catversion.h | 4 ++-- src/include/catalog/pg_proc.h | 4 +++- src/include/pgstat.h | 6 ++++-- src/test/regress/expected/rules.out | 2 +- 9 files changed, 77 insertions(+), 21 deletions(-) diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index 9c9662e793..e3b2678bdc 100644 --- a/doc/src/sgml/monitoring.sgml +++ b/doc/src/sgml/monitoring.sgml @@ -1,4 +1,4 @@ - + Monitoring Database Activity @@ -244,16 +244,16 @@ postgres: user database host pg_stat_activity - One row per server process, showing database OID, database name, - process ID, user OID, user name, current query, query's - waiting status, time at - which the current query began execution, time at which the process - was started, and client's address and port number. The columns - that report data on the current query are available unless the - parameter stats_command_string has been - turned off. Furthermore, these columns are only visible if the - user examining the view is a superuser or the same as the user - owning the process being reported on. + One row per server process, showing database OID, database + name, process ID, user OID, user name, current query, + query's waiting status, time at which the current transaction and + current query began execution, time at which the process was + started, and client's address and port number. The columns that + report data on the current query are available unless the parameter + stats_command_string has been turned off. + Furthermore, these columns are only visible if the user examining + the view is a superuser or the same as the user owning the process + being reported on. diff --git a/src/backend/access/transam/xact.c b/src/backend/access/transam/xact.c index 673a34ad03..b0b8970380 100644 --- a/src/backend/access/transam/xact.c +++ b/src/backend/access/transam/xact.c @@ -10,7 +10,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/access/transam/xact.c,v 1.229 2006/11/23 01:14:59 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/access/transam/xact.c,v 1.230 2006/12/06 18:06:47 neilc Exp $ * *------------------------------------------------------------------------- */ @@ -1416,6 +1416,7 @@ StartTransaction(void) * GetCurrentTimestamp() call (which'd be expensive anyway). */ xactStartTimestamp = stmtStartTimestamp; + pgstat_report_txn_timestamp(xactStartTimestamp); /* * initialize current transaction state fields @@ -1628,6 +1629,7 @@ CommitTransaction(void) /* smgrcommit already done */ AtEOXact_Files(); pgstat_count_xact_commit(); + pgstat_report_txn_timestamp(0); CurrentResourceOwner = NULL; ResourceOwnerDelete(TopTransactionResourceOwner); @@ -1994,6 +1996,7 @@ AbortTransaction(void) smgrabort(); AtEOXact_Files(); pgstat_count_xact_rollback(); + pgstat_report_txn_timestamp(0); /* * State remains TRANS_ABORT until CleanupTransaction(). diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index 9dd19e8c5d..e79b0f00fb 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -3,7 +3,7 @@ * * Copyright (c) 1996-2006, PostgreSQL Global Development Group * - * $PostgreSQL: pgsql/src/backend/catalog/system_views.sql,v 1.32 2006/11/24 21:18:42 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/catalog/system_views.sql,v 1.33 2006/12/06 18:06:47 neilc Exp $ */ CREATE VIEW pg_roles AS @@ -335,6 +335,7 @@ CREATE VIEW pg_stat_activity AS U.rolname AS usename, pg_stat_get_backend_activity(S.backendid) AS current_query, pg_stat_get_backend_waiting(S.backendid) AS waiting, + pg_stat_get_backend_txn_start(S.backendid) AS txn_start, pg_stat_get_backend_activity_start(S.backendid) AS query_start, pg_stat_get_backend_start(S.backendid) AS backend_start, pg_stat_get_backend_client_addr(S.backendid) AS client_addr, diff --git a/src/backend/postmaster/pgstat.c b/src/backend/postmaster/pgstat.c index 19a694caf3..ef9487ade0 100644 --- a/src/backend/postmaster/pgstat.c +++ b/src/backend/postmaster/pgstat.c @@ -13,7 +13,7 @@ * * Copyright (c) 2001-2006, PostgreSQL Global Development Group * - * $PostgreSQL: pgsql/src/backend/postmaster/pgstat.c,v 1.140 2006/11/21 20:59:52 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/postmaster/pgstat.c,v 1.141 2006/12/06 18:06:47 neilc Exp $ * ---------- */ #include "postgres.h" @@ -1355,6 +1355,7 @@ pgstat_bestart(void) beentry->st_procpid = MyProcPid; beentry->st_proc_start_timestamp = proc_start_timestamp; beentry->st_activity_start_timestamp = 0; + beentry->st_txn_start_timestamp = 0; beentry->st_databaseid = MyDatabaseId; beentry->st_userid = userid; beentry->st_clientaddr = clientaddr; @@ -1443,6 +1444,29 @@ pgstat_report_activity(const char *cmd_str) Assert((beentry->st_changecount & 1) == 0); } +/* + * Set the current transaction start timestamp to the specified + * value. If there is no current active transaction, this is signified + * by 0. + */ +void +pgstat_report_txn_timestamp(TimestampTz tstamp) +{ + volatile PgBackendStatus *beentry = MyBEEntry; + + if (!pgstat_collect_querystring || !beentry) + return; + + /* + * Update my status entry, following the protocol of bumping + * st_changecount before and after. We use a volatile pointer + * here to ensure the compiler doesn't try to get cute. + */ + beentry->st_changecount++; + beentry->st_txn_start_timestamp = tstamp; + beentry->st_changecount++; + Assert((beentry->st_changecount & 1) == 0); +} /* ---------- * pgstat_report_waiting() - diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c index e2d302b5a6..18709a981d 100644 --- a/src/backend/utils/adt/pgstatfuncs.c +++ b/src/backend/utils/adt/pgstatfuncs.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/utils/adt/pgstatfuncs.c,v 1.34 2006/10/04 00:29:59 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/utils/adt/pgstatfuncs.c,v 1.35 2006/12/06 18:06:47 neilc Exp $ * *------------------------------------------------------------------------- */ @@ -44,6 +44,7 @@ extern Datum pg_stat_get_backend_userid(PG_FUNCTION_ARGS); extern Datum pg_stat_get_backend_activity(PG_FUNCTION_ARGS); extern Datum pg_stat_get_backend_waiting(PG_FUNCTION_ARGS); extern Datum pg_stat_get_backend_activity_start(PG_FUNCTION_ARGS); +extern Datum pg_stat_get_backend_txn_start(PG_FUNCTION_ARGS); extern Datum pg_stat_get_backend_start(PG_FUNCTION_ARGS); extern Datum pg_stat_get_backend_client_addr(PG_FUNCTION_ARGS); extern Datum pg_stat_get_backend_client_port(PG_FUNCTION_ARGS); @@ -422,6 +423,29 @@ pg_stat_get_backend_activity_start(PG_FUNCTION_ARGS) PG_RETURN_TIMESTAMPTZ(result); } + +Datum +pg_stat_get_backend_txn_start(PG_FUNCTION_ARGS) +{ + int32 beid = PG_GETARG_INT32(0); + TimestampTz result; + PgBackendStatus *beentry; + + if ((beentry = pgstat_fetch_stat_beentry(beid)) == NULL) + PG_RETURN_NULL(); + + if (!superuser() && beentry->st_userid != GetUserId()) + PG_RETURN_NULL(); + + result = beentry->st_txn_start_timestamp; + + if (result == 0) /* not in a transaction */ + PG_RETURN_NULL(); + + PG_RETURN_TIMESTAMPTZ(result); +} + + Datum pg_stat_get_backend_start(PG_FUNCTION_ARGS) { diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h index 4488c607e7..246eef42f4 100644 --- a/src/include/catalog/catversion.h +++ b/src/include/catalog/catversion.h @@ -37,7 +37,7 @@ * Portions Copyright (c) 1996-2006, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.360 2006/11/24 21:18:42 tgl Exp $ + * $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.361 2006/12/06 18:06:47 neilc Exp $ * *------------------------------------------------------------------------- */ @@ -53,6 +53,6 @@ */ /* yyyymmddN */ -#define CATALOG_VERSION_NO 200611241 +#define CATALOG_VERSION_NO 200612061 #endif diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h index 9b2155f862..9dee8f6a3c 100644 --- a/src/include/catalog/pg_proc.h +++ b/src/include/catalog/pg_proc.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2006, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.429 2006/11/28 19:18:44 tgl Exp $ + * $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.430 2006/12/06 18:06:47 neilc Exp $ * * NOTES * The script catalog/genbki.sh reads this file and generates .bki @@ -2902,6 +2902,8 @@ DATA(insert OID = 2853 ( pg_stat_get_backend_waiting PGNSP PGUID 12 f f t f s 1 DESCR("Statistics: Is backend currently waiting for a lock"); DATA(insert OID = 2094 ( pg_stat_get_backend_activity_start PGNSP PGUID 12 f f t f s 1 1184 "23" _null_ _null_ _null_ pg_stat_get_backend_activity_start - _null_)); DESCR("Statistics: Start time for current query of backend"); +DATA(insert OID = 2857 ( pg_stat_get_backend_txn_start PGNSP PGUID 12 f f t f s 1 1184 "23" _null_ _null_ _null_ pg_stat_get_backend_txn_start - _null_)); +DESCR("Statistics: Start time for backend's current transaction"); DATA(insert OID = 1391 ( pg_stat_get_backend_start PGNSP PGUID 12 f f t f s 1 1184 "23" _null_ _null_ _null_ pg_stat_get_backend_start - _null_)); DESCR("Statistics: Start time for current backend session"); DATA(insert OID = 1392 ( pg_stat_get_backend_client_addr PGNSP PGUID 12 f f t f s 1 869 "23" _null_ _null_ _null_ pg_stat_get_backend_client_addr - _null_)); diff --git a/src/include/pgstat.h b/src/include/pgstat.h index 8f5db77079..a702ad8288 100644 --- a/src/include/pgstat.h +++ b/src/include/pgstat.h @@ -5,7 +5,7 @@ * * Copyright (c) 2001-2006, PostgreSQL Global Development Group * - * $PostgreSQL: pgsql/src/include/pgstat.h,v 1.50 2006/10/04 00:30:06 momjian Exp $ + * $PostgreSQL: pgsql/src/include/pgstat.h,v 1.51 2006/12/06 18:06:47 neilc Exp $ * ---------- */ #ifndef PGSTAT_H @@ -325,8 +325,9 @@ typedef struct PgBackendStatus /* The entry is valid iff st_procpid > 0, unused if st_procpid == 0 */ int st_procpid; - /* Times of backend process start and current activity start */ + /* Times when current backend, transaction, and activity started */ TimestampTz st_proc_start_timestamp; + TimestampTz st_txn_start_timestamp; TimestampTz st_activity_start_timestamp; /* Database OID, owning user's OID, connection client address */ @@ -390,6 +391,7 @@ extern void pgstat_report_analyze(Oid tableoid, bool shared, extern void pgstat_bestart(void); extern void pgstat_report_activity(const char *what); +extern void pgstat_report_txn_timestamp(TimestampTz tstamp); extern void pgstat_report_waiting(bool waiting); extern void pgstat_initstats(PgStat_Info *stats, Relation rel); diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index 2a8415d5e1..9c0f055edc 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -1287,7 +1287,7 @@ SELECT viewname, definition FROM pg_views WHERE schemaname <> 'information_schem pg_rules | SELECT n.nspname AS schemaname, c.relname AS tablename, r.rulename, pg_get_ruledef(r.oid) AS definition FROM ((pg_rewrite r JOIN pg_class c ON ((c.oid = r.ev_class))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (r.rulename <> '_RETURN'::name); pg_settings | SELECT a.name, a.setting, a.unit, a.category, a.short_desc, a.extra_desc, a.context, a.vartype, a.source, a.min_val, a.max_val FROM pg_show_all_settings() a(name text, setting text, unit text, category text, short_desc text, extra_desc text, context text, vartype text, source text, min_val text, max_val text); pg_shadow | SELECT pg_authid.rolname AS usename, pg_authid.oid AS usesysid, pg_authid.rolcreatedb AS usecreatedb, pg_authid.rolsuper AS usesuper, pg_authid.rolcatupdate AS usecatupd, pg_authid.rolpassword AS passwd, (pg_authid.rolvaliduntil)::abstime AS valuntil, pg_authid.rolconfig AS useconfig FROM pg_authid WHERE pg_authid.rolcanlogin; - pg_stat_activity | SELECT d.oid AS datid, d.datname, pg_stat_get_backend_pid(s.backendid) AS procpid, pg_stat_get_backend_userid(s.backendid) AS usesysid, u.rolname AS usename, pg_stat_get_backend_activity(s.backendid) AS current_query, pg_stat_get_backend_waiting(s.backendid) AS waiting, pg_stat_get_backend_activity_start(s.backendid) AS query_start, pg_stat_get_backend_start(s.backendid) AS backend_start, pg_stat_get_backend_client_addr(s.backendid) AS client_addr, pg_stat_get_backend_client_port(s.backendid) AS client_port FROM pg_database d, (SELECT pg_stat_get_backend_idset() AS backendid) s, pg_authid u WHERE ((pg_stat_get_backend_dbid(s.backendid) = d.oid) AND (pg_stat_get_backend_userid(s.backendid) = u.oid)); + pg_stat_activity | SELECT d.oid AS datid, d.datname, pg_stat_get_backend_pid(s.backendid) AS procpid, pg_stat_get_backend_userid(s.backendid) AS usesysid, u.rolname AS usename, pg_stat_get_backend_activity(s.backendid) AS current_query, pg_stat_get_backend_waiting(s.backendid) AS waiting, pg_stat_get_backend_txn_start(s.backendid) AS txn_start, pg_stat_get_backend_activity_start(s.backendid) AS query_start, pg_stat_get_backend_start(s.backendid) AS backend_start, pg_stat_get_backend_client_addr(s.backendid) AS client_addr, pg_stat_get_backend_client_port(s.backendid) AS client_port FROM pg_database d, (SELECT pg_stat_get_backend_idset() AS backendid) s, pg_authid u WHERE ((pg_stat_get_backend_dbid(s.backendid) = d.oid) AND (pg_stat_get_backend_userid(s.backendid) = u.oid)); pg_stat_all_indexes | SELECT c.oid AS relid, i.oid AS indexrelid, n.nspname AS schemaname, c.relname, i.relname AS indexrelname, pg_stat_get_numscans(i.oid) AS idx_scan, pg_stat_get_tuples_returned(i.oid) AS idx_tup_read, pg_stat_get_tuples_fetched(i.oid) AS idx_tup_fetch FROM (((pg_class c JOIN pg_index x ON ((c.oid = x.indrelid))) JOIN pg_class i ON ((i.oid = x.indexrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char"])); pg_stat_all_tables | SELECT c.oid AS relid, n.nspname AS schemaname, c.relname, pg_stat_get_numscans(c.oid) AS seq_scan, pg_stat_get_tuples_returned(c.oid) AS seq_tup_read, (sum(pg_stat_get_numscans(i.indexrelid)))::bigint AS idx_scan, ((sum(pg_stat_get_tuples_fetched(i.indexrelid)))::bigint + pg_stat_get_tuples_fetched(c.oid)) AS idx_tup_fetch, pg_stat_get_tuples_inserted(c.oid) AS n_tup_ins, pg_stat_get_tuples_updated(c.oid) AS n_tup_upd, pg_stat_get_tuples_deleted(c.oid) AS n_tup_del, pg_stat_get_last_vacuum_time(c.oid) AS last_vacuum, pg_stat_get_last_autovacuum_time(c.oid) AS last_autovacuum, pg_stat_get_last_analyze_time(c.oid) AS last_analyze, pg_stat_get_last_autoanalyze_time(c.oid) AS last_autoanalyze FROM ((pg_class c LEFT JOIN pg_index i ON ((c.oid = i.indrelid))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE (c.relkind = ANY (ARRAY['r'::"char", 't'::"char"])) GROUP BY c.oid, n.nspname, c.relname; pg_stat_database | SELECT d.oid AS datid, d.datname, pg_stat_get_db_numbackends(d.oid) AS numbackends, pg_stat_get_db_xact_commit(d.oid) AS xact_commit, pg_stat_get_db_xact_rollback(d.oid) AS xact_rollback, (pg_stat_get_db_blocks_fetched(d.oid) - pg_stat_get_db_blocks_hit(d.oid)) AS blks_read, pg_stat_get_db_blocks_hit(d.oid) AS blks_hit FROM pg_database d; -- 2.40.0