Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit886a02d

Browse files
author
Neil Conway
committed
Add a txn_start column to pg_stat_activity. This makes it easier to
identify long-running transactions. Since we already need to recordthe transaction-start time (e.g. for now()), we don't need anyadditional system calls to report this information.Catversion bumped, initdb required.
1 parentdd740e1 commit886a02d

File tree

9 files changed

+77
-21
lines changed

9 files changed

+77
-21
lines changed

‎doc/src/sgml/monitoring.sgml

Lines changed: 11 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/monitoring.sgml,v 1.41 2006/12/02 09:29:51 petere Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/monitoring.sgml,v 1.42 2006/12/06 18:06:46 neilc Exp $ -->
22

33
<chapter id="monitoring">
44
<title>Monitoring Database Activity</title>
@@ -244,16 +244,16 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
244244
<tbody>
245245
<row>
246246
<entry><structname>pg_stat_activity</></entry>
247-
<entry>One row per server process, showing database OID, database name,
248-
process <acronym>ID</>, user OID, user name, current query, query's
249-
waiting status, time at
250-
which thecurrent query began execution, time at which the process
251-
wasstarted, and client's address and port number. The columns
252-
thatreport data on the current query are available unless the
253-
parameter<varname>stats_command_string</varname> has been
254-
turned off.Furthermore, these columns are only visible if the
255-
user examiningthe view is a superuser or the same as the user
256-
owning the processbeing reported on.
247+
<entry>One row per server process, showing database OID, database
248+
name,process <acronym>ID</>, user OID, user name, current query,
249+
query'swaiting status, time at which the current transaction and
250+
current query began execution, time at which the process was
251+
started, and client's address and port number. The columns that
252+
report data on the current query are available unless the parameter
253+
<varname>stats_command_string</varname> has been turned off.
254+
Furthermore, these columns are only visible if the user examining
255+
the view is a superuser or the same as the user owning the process
256+
being reported on.
257257
</entry>
258258
</row>
259259

‎src/backend/access/transam/xact.c

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -10,7 +10,7 @@
1010
*
1111
*
1212
* IDENTIFICATION
13-
* $PostgreSQL: pgsql/src/backend/access/transam/xact.c,v 1.229 2006/11/23 01:14:59 tgl Exp $
13+
* $PostgreSQL: pgsql/src/backend/access/transam/xact.c,v 1.230 2006/12/06 18:06:47 neilc Exp $
1414
*
1515
*-------------------------------------------------------------------------
1616
*/
@@ -1416,6 +1416,7 @@ StartTransaction(void)
14161416
* GetCurrentTimestamp() call (which'd be expensive anyway).
14171417
*/
14181418
xactStartTimestamp=stmtStartTimestamp;
1419+
pgstat_report_txn_timestamp(xactStartTimestamp);
14191420

14201421
/*
14211422
* initialize current transaction state fields
@@ -1628,6 +1629,7 @@ CommitTransaction(void)
16281629
/* smgrcommit already done */
16291630
AtEOXact_Files();
16301631
pgstat_count_xact_commit();
1632+
pgstat_report_txn_timestamp(0);
16311633

16321634
CurrentResourceOwner=NULL;
16331635
ResourceOwnerDelete(TopTransactionResourceOwner);
@@ -1994,6 +1996,7 @@ AbortTransaction(void)
19941996
smgrabort();
19951997
AtEOXact_Files();
19961998
pgstat_count_xact_rollback();
1999+
pgstat_report_txn_timestamp(0);
19972000

19982001
/*
19992002
* State remains TRANS_ABORT until CleanupTransaction().

‎src/backend/catalog/system_views.sql

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -3,7 +3,7 @@
33
*
44
* Copyright (c) 1996-2006, PostgreSQL Global Development Group
55
*
6-
* $PostgreSQL: pgsql/src/backend/catalog/system_views.sql,v 1.32 2006/11/24 21:18:42 tgl Exp $
6+
* $PostgreSQL: pgsql/src/backend/catalog/system_views.sql,v 1.33 2006/12/0618:06:47 neilc Exp $
77
*/
88

99
CREATEVIEWpg_rolesAS
@@ -335,6 +335,7 @@ CREATE VIEW pg_stat_activity AS
335335
U.rolnameAS usename,
336336
pg_stat_get_backend_activity(S.backendid)AS current_query,
337337
pg_stat_get_backend_waiting(S.backendid)AS waiting,
338+
pg_stat_get_backend_txn_start(S.backendid)AS txn_start,
338339
pg_stat_get_backend_activity_start(S.backendid)AS query_start,
339340
pg_stat_get_backend_start(S.backendid)AS backend_start,
340341
pg_stat_get_backend_client_addr(S.backendid)AS client_addr,

‎src/backend/postmaster/pgstat.c

Lines changed: 25 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -13,7 +13,7 @@
1313
*
1414
*Copyright (c) 2001-2006, PostgreSQL Global Development Group
1515
*
16-
*$PostgreSQL: pgsql/src/backend/postmaster/pgstat.c,v 1.140 2006/11/21 20:59:52 tgl Exp $
16+
*$PostgreSQL: pgsql/src/backend/postmaster/pgstat.c,v 1.141 2006/12/06 18:06:47 neilc Exp $
1717
* ----------
1818
*/
1919
#include"postgres.h"
@@ -1355,6 +1355,7 @@ pgstat_bestart(void)
13551355
beentry->st_procpid=MyProcPid;
13561356
beentry->st_proc_start_timestamp=proc_start_timestamp;
13571357
beentry->st_activity_start_timestamp=0;
1358+
beentry->st_txn_start_timestamp=0;
13581359
beentry->st_databaseid=MyDatabaseId;
13591360
beentry->st_userid=userid;
13601361
beentry->st_clientaddr=clientaddr;
@@ -1443,6 +1444,29 @@ pgstat_report_activity(const char *cmd_str)
14431444
Assert((beentry->st_changecount&1)==0);
14441445
}
14451446

1447+
/*
1448+
* Set the current transaction start timestamp to the specified
1449+
* value. If there is no current active transaction, this is signified
1450+
* by 0.
1451+
*/
1452+
void
1453+
pgstat_report_txn_timestamp(TimestampTztstamp)
1454+
{
1455+
volatilePgBackendStatus*beentry=MyBEEntry;
1456+
1457+
if (!pgstat_collect_querystring|| !beentry)
1458+
return;
1459+
1460+
/*
1461+
* Update my status entry, following the protocol of bumping
1462+
* st_changecount before and after. We use a volatile pointer
1463+
* here to ensure the compiler doesn't try to get cute.
1464+
*/
1465+
beentry->st_changecount++;
1466+
beentry->st_txn_start_timestamp=tstamp;
1467+
beentry->st_changecount++;
1468+
Assert((beentry->st_changecount&1)==0);
1469+
}
14461470

14471471
/* ----------
14481472
* pgstat_report_waiting() -

‎src/backend/utils/adt/pgstatfuncs.c

Lines changed: 25 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/utils/adt/pgstatfuncs.c,v 1.34 2006/10/04 00:29:59 momjian Exp $
11+
* $PostgreSQL: pgsql/src/backend/utils/adt/pgstatfuncs.c,v 1.35 2006/12/06 18:06:47 neilc Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -44,6 +44,7 @@ extern Datum pg_stat_get_backend_userid(PG_FUNCTION_ARGS);
4444
externDatumpg_stat_get_backend_activity(PG_FUNCTION_ARGS);
4545
externDatumpg_stat_get_backend_waiting(PG_FUNCTION_ARGS);
4646
externDatumpg_stat_get_backend_activity_start(PG_FUNCTION_ARGS);
47+
externDatumpg_stat_get_backend_txn_start(PG_FUNCTION_ARGS);
4748
externDatumpg_stat_get_backend_start(PG_FUNCTION_ARGS);
4849
externDatumpg_stat_get_backend_client_addr(PG_FUNCTION_ARGS);
4950
externDatumpg_stat_get_backend_client_port(PG_FUNCTION_ARGS);
@@ -422,6 +423,29 @@ pg_stat_get_backend_activity_start(PG_FUNCTION_ARGS)
422423
PG_RETURN_TIMESTAMPTZ(result);
423424
}
424425

426+
427+
Datum
428+
pg_stat_get_backend_txn_start(PG_FUNCTION_ARGS)
429+
{
430+
int32beid=PG_GETARG_INT32(0);
431+
TimestampTzresult;
432+
PgBackendStatus*beentry;
433+
434+
if ((beentry=pgstat_fetch_stat_beentry(beid))==NULL)
435+
PG_RETURN_NULL();
436+
437+
if (!superuser()&&beentry->st_userid!=GetUserId())
438+
PG_RETURN_NULL();
439+
440+
result=beentry->st_txn_start_timestamp;
441+
442+
if (result==0)/* not in a transaction */
443+
PG_RETURN_NULL();
444+
445+
PG_RETURN_TIMESTAMPTZ(result);
446+
}
447+
448+
425449
Datum
426450
pg_stat_get_backend_start(PG_FUNCTION_ARGS)
427451
{

‎src/include/catalog/catversion.h

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -37,7 +37,7 @@
3737
* Portions Copyright (c) 1996-2006, PostgreSQL Global Development Group
3838
* Portions Copyright (c) 1994, Regents of the University of California
3939
*
40-
* $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.360 2006/11/24 21:18:42 tgl Exp $
40+
* $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.361 2006/12/0618:06:47 neilc Exp $
4141
*
4242
*-------------------------------------------------------------------------
4343
*/
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/*yyyymmddN */
56-
#defineCATALOG_VERSION_NO200611241
56+
#defineCATALOG_VERSION_NO200612061
5757

5858
#endif

‎src/include/catalog/pg_proc.h

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -7,7 +7,7 @@
77
* Portions Copyright (c) 1996-2006, PostgreSQL Global Development Group
88
* Portions Copyright (c) 1994, Regents of the University of California
99
*
10-
* $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.429 2006/11/28 19:18:44 tgl Exp $
10+
* $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.430 2006/12/0618:06:47 neilc Exp $
1111
*
1212
* NOTES
1313
* The script catalog/genbki.sh reads this file and generates .bki
@@ -2902,6 +2902,8 @@ DATA(insert OID = 2853 ( pg_stat_get_backend_waitingPGNSP PGUID 12 f f t f s 1
29022902
DESCR("Statistics: Is backend currently waiting for a lock");
29032903
DATA(insertOID=2094 (pg_stat_get_backend_activity_startPGNSPPGUID12fftfs11184"23"_null__null__null_pg_stat_get_backend_activity_start-_null_));
29042904
DESCR("Statistics: Start time for current query of backend");
2905+
DATA(insertOID=2857 (pg_stat_get_backend_txn_startPGNSPPGUID12fftfs11184"23"_null__null__null_pg_stat_get_backend_txn_start-_null_));
2906+
DESCR("Statistics: Start time for backend's current transaction");
29052907
DATA(insertOID=1391 (pg_stat_get_backend_startPGNSPPGUID12fftfs11184"23"_null__null__null_pg_stat_get_backend_start-_null_));
29062908
DESCR("Statistics: Start time for current backend session");
29072909
DATA(insertOID=1392 (pg_stat_get_backend_client_addrPGNSPPGUID12fftfs1869"23"_null__null__null_pg_stat_get_backend_client_addr-_null_));

‎src/include/pgstat.h

Lines changed: 4 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -5,7 +5,7 @@
55
*
66
*Copyright (c) 2001-2006, PostgreSQL Global Development Group
77
*
8-
*$PostgreSQL: pgsql/src/include/pgstat.h,v 1.50 2006/10/04 00:30:06 momjian Exp $
8+
*$PostgreSQL: pgsql/src/include/pgstat.h,v 1.51 2006/12/06 18:06:47 neilc Exp $
99
* ----------
1010
*/
1111
#ifndefPGSTAT_H
@@ -325,8 +325,9 @@ typedef struct PgBackendStatus
325325
/* The entry is valid iff st_procpid > 0, unused if st_procpid == 0 */
326326
intst_procpid;
327327

328-
/* Timesofbackend process startandcurrentactivitystart */
328+
/* Timeswhen currentbackend, transaction,and activitystarted */
329329
TimestampTzst_proc_start_timestamp;
330+
TimestampTzst_txn_start_timestamp;
330331
TimestampTzst_activity_start_timestamp;
331332

332333
/* Database OID, owning user's OID, connection client address */
@@ -390,6 +391,7 @@ extern void pgstat_report_analyze(Oid tableoid, bool shared,
390391

391392
externvoidpgstat_bestart(void);
392393
externvoidpgstat_report_activity(constchar*what);
394+
externvoidpgstat_report_txn_timestamp(TimestampTztstamp);
393395
externvoidpgstat_report_waiting(boolwaiting);
394396

395397
externvoidpgstat_initstats(PgStat_Info*stats,Relationrel);

‎src/test/regress/expected/rules.out

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1287,7 +1287,7 @@ SELECT viewname, definition FROM pg_views WHERE schemaname <> 'information_schem
12871287
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);
12881288
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);
12891289
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;
1290-
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));
1290+
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));
12911291
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"]));
12921292
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;
12931293
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;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp