Transaction start in pg_stat_activity

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;