Attached is a first revision of a patch that adds a column to pg_stat_activity containing the time at which the backend's current transaction began, or NULL if the backend is not inside a transaction. This is useful for finding long-running transactions, and AFAIK this information is not otherwise easily available from outside the backend's session.
Naturally, this is for 8.3. -Neil
Index: doc/src/sgml/monitoring.sgml =================================================================== RCS file: /home/neilc/postgres/cvs_root/pgsql/doc/src/sgml/monitoring.sgml,v retrieving revision 1.38 diff -c -p -r1.38 monitoring.sgml *** doc/src/sgml/monitoring.sgml 16 Sep 2006 00:30:14 -0000 1.38 --- doc/src/sgml/monitoring.sgml 19 Nov 2006 00:37:38 -0000 *************** postgres: <replaceable>user</> <replacea *** 243,255 **** <tbody> <row> ! <entry><structname>pg_stat_activity</></entry> ! <entry>One row per server process, showing database OID, database name, ! process <acronym>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 <varname>stats_command_string</varname> 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 --- 243,255 ---- <tbody> <row> ! <entry><structname>pg_stat_activity</></entry> <entry>One row ! per server process, showing database OID, database name, process ! <acronym>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 <varname>stats_command_string</varname> 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 Index: src/backend/access/transam/xact.c =================================================================== RCS file: /home/neilc/postgres/cvs_root/pgsql/src/backend/access/transam/xact.c,v retrieving revision 1.228 diff -c -p -r1.228 xact.c *** src/backend/access/transam/xact.c 5 Nov 2006 22:42:07 -0000 1.228 --- src/backend/access/transam/xact.c 19 Nov 2006 00:41:58 -0000 *************** StartTransaction(void) *** 1418,1423 **** --- 1418,1424 ---- s->state = TRANS_INPROGRESS; ShowTransactionState("StartTransaction"); + pgstat_report_txn_timestamp(xactStartTimestamp); } *************** CommitTransaction(void) *** 1600,1605 **** --- 1601,1607 ---- /* smgrcommit already done */ AtEOXact_Files(); pgstat_count_xact_commit(); + pgstat_report_txn_timestamp(0); CurrentResourceOwner = NULL; ResourceOwnerDelete(TopTransactionResourceOwner); *************** AbortTransaction(void) *** 1966,1971 **** --- 1968,1974 ---- smgrabort(); AtEOXact_Files(); pgstat_count_xact_rollback(); + pgstat_report_txn_timestamp(0); /* * State remains TRANS_ABORT until CleanupTransaction(). Index: src/backend/catalog/system_views.sql =================================================================== RCS file: /home/neilc/postgres/cvs_root/pgsql/src/backend/catalog/system_views.sql,v retrieving revision 1.31 diff -c -p -r1.31 system_views.sql *** src/backend/catalog/system_views.sql 16 Sep 2006 20:14:33 -0000 1.31 --- src/backend/catalog/system_views.sql 18 Nov 2006 22:47:14 -0000 *************** CREATE VIEW pg_stat_activity AS *** 335,340 **** --- 335,341 ---- 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, Index: src/backend/postmaster/pgstat.c =================================================================== RCS file: /home/neilc/postgres/cvs_root/pgsql/src/backend/postmaster/pgstat.c,v retrieving revision 1.139 diff -c -p -r1.139 pgstat.c *** src/backend/postmaster/pgstat.c 4 Oct 2006 00:29:56 -0000 1.139 --- src/backend/postmaster/pgstat.c 19 Nov 2006 01:07:09 -0000 *************** pgstat_bestart(void) *** 1355,1360 **** --- 1355,1361 ---- 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; *************** pgstat_report_activity(const char *cmd_s *** 1443,1448 **** --- 1444,1472 ---- 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() - Index: src/backend/utils/adt/pgstatfuncs.c =================================================================== RCS file: /home/neilc/postgres/cvs_root/pgsql/src/backend/utils/adt/pgstatfuncs.c,v retrieving revision 1.34 diff -c -p -r1.34 pgstatfuncs.c *** src/backend/utils/adt/pgstatfuncs.c 4 Oct 2006 00:29:59 -0000 1.34 --- src/backend/utils/adt/pgstatfuncs.c 18 Nov 2006 22:59:58 -0000 *************** extern Datum pg_stat_get_backend_userid( *** 44,49 **** --- 44,50 ---- 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); *************** pg_stat_get_backend_activity_start(PG_FU *** 422,427 **** --- 423,451 ---- 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) { Index: src/include/pgstat.h =================================================================== RCS file: /home/neilc/postgres/cvs_root/pgsql/src/include/pgstat.h,v retrieving revision 1.50 diff -c -p -r1.50 pgstat.h *** src/include/pgstat.h 4 Oct 2006 00:30:06 -0000 1.50 --- src/include/pgstat.h 19 Nov 2006 00:17:42 -0000 *************** typedef struct PgBackendStatus *** 325,332 **** /* 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 */ TimestampTz st_proc_start_timestamp; TimestampTz st_activity_start_timestamp; /* Database OID, owning user's OID, connection client address */ --- 325,333 ---- /* The entry is valid iff st_procpid > 0, unused if st_procpid == 0 */ int st_procpid; ! /* 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 */ *************** extern void pgstat_report_analyze(Oid ta *** 390,395 **** --- 391,397 ---- 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); Index: src/include/catalog/pg_proc.h =================================================================== RCS file: /home/neilc/postgres/cvs_root/pgsql/src/include/catalog/pg_proc.h,v retrieving revision 1.427 diff -c -p -r1.427 pg_proc.h *** src/include/catalog/pg_proc.h 4 Oct 2006 00:30:07 -0000 1.427 --- src/include/catalog/pg_proc.h 18 Nov 2006 23:04:37 -0000 *************** DATA(insert OID = 2853 ( pg_stat_get_ba *** 2902,2907 **** --- 2902,2909 ---- 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_)); Index: src/test/regress/expected/rules.out =================================================================== RCS file: /home/neilc/postgres/cvs_root/pgsql/src/test/regress/expected/rules.out,v retrieving revision 1.120 diff -c -p -r1.120 rules.out *** src/test/regress/expected/rules.out 11 Oct 2006 20:21:04 -0000 1.120 --- src/test/regress/expected/rules.out 19 Nov 2006 02:18:44 -0000 *************** SELECT viewname, definition FROM pg_view *** 1287,1293 **** 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_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_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, 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 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; --- 1287,1293 ---- 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_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_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, 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 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;
---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq