Add start time to pg_stat_activity

Neil Conway

3 files changed, 78 insertions, 31 deletions

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 91cc2c70af4..256b0fa3fae 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -1,5 +1,5 @@
 <!--
-$Header: /cvsroot/pgsql/doc/src/sgml/func.sgml,v 1.143 2003/03/13 01:30:28 petere Exp $
+$Header: /cvsroot/pgsql/doc/src/sgml/func.sgml,v 1.144 2003/03/20 03:34:55 momjian Exp $
 PostgreSQL documentation
 -->
 
@@ -5315,7 +5315,7 @@ SELECT LOCALTIMESTAMP;
 
    <para>
     There is also the function <function>timeofday()</function>, which for historical
-    reasons returns a text string rather than a <type>timestamp</type> value:
+    reasons returns a <type>text</type> string rather than a <type>timestamp</type> value:
 <screen>
 SELECT timeofday();
 <lineannotation>Result: </lineannotation><computeroutput>Sat Feb 17 19:07:32.000126 2001 EST</computeroutput>
@@ -5326,7 +5326,11 @@ SELECT timeofday();
     It is important to know that
     <function>CURRENT_TIMESTAMP</function> and related functions return
     the start time of the current transaction; their values do not
-    change during the transaction. <function>timeofday()</function>
+    change during the transaction. This is considered a feature:
+    the intent is to allow a single transaction to have a consistent
+    notion of the <quote>current</quote> time, so that multiple
+    modifications within the same transaction bear the same
+    timestamp. <function>timeofday()</function>
     returns the wall-clock time and does advance during transactions.
    </para>
 
diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml
index 78c1bdbec3f..002134c9acd 100644
--- a/doc/src/sgml/monitoring.sgml
+++ b/doc/src/sgml/monitoring.sgml
@@ -1,5 +1,5 @@
 <!--
-$Header: /cvsroot/pgsql/doc/src/sgml/monitoring.sgml,v 1.15 2002/11/11 20:14:03 petere Exp $
+$Header: /cvsroot/pgsql/doc/src/sgml/monitoring.sgml,v 1.16 2003/03/20 03:34:55 momjian Exp $
 -->
 
 <chapter id="monitoring">
@@ -146,12 +146,13 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
    <important>
     <para>
      Since the variables <varname>STATS_COMMAND_STRING</varname>,
-     <varname>STATS_BLOCK_LEVEL</varname>,
-     and <varname>STATS_ROW_LEVEL</varname>
-     default to <literal>false</>, no statistics are actually collected
-     in the default configuration.  You must turn one or more of them on
-     before you will get useful results from the statistical display
-     functions.
+     <varname>STATS_BLOCK_LEVEL</varname>, and
+     <varname>STATS_ROW_LEVEL</varname> default to <literal>false</>,
+     very few statistics are collected in the default
+     configuration. Enabling one or more of these configuration
+     variables will significantly enhance the amount of useful data
+     produced by the statistics collector, at the expense of
+     additional run-time overhead.
     </para>
    </important>
 
@@ -205,11 +206,15 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
     <tbody>
      <row>
       <entry><structname>pg_stat_activity</></entry>
-      <entry>One row per server process, showing process <acronym>ID</>, database,
-      user, and current query.  The current query column is only available
-      to superusers; for others it reads as null.  (Note that because of
-      the collector's reporting delay, current query will only be up-to-date
-      for long-running queries.)</entry>
+      <entry>One row per server process, showing process
+      <acronym>ID</>, database, user, current query, and the time at
+      which the current query began execution. The columns that report
+      data on the current query are only available if the
+      <varname>STATS_COMMAND_STRING</varname> configuration option has
+      been enabled. Furthermore, these columns can only be accessed by
+      superusers; to other users, they always appear NULL. (Note that
+      because of the collector's reporting delay, current query will
+      only be up-to-date for long-running queries.)</entry>
      </row>
 
      <row>
@@ -333,10 +338,20 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
   </para>
 
   <para>
-   The <structname>pg_statio_</> views are primarily useful to determine
-   the effectiveness of the buffer cache.  When the number of actual disk
-   reads is much smaller than the number of buffer hits, then the cache
-   is satisfying most read requests without invoking a kernel call.
+   The <structname>pg_statio_</> views are primarily useful to
+   determine the effectiveness of the buffer cache.  When the number
+   of actual disk reads is much smaller than the number of buffer
+   hits, then the cache is satisfying most read requests without
+   invoking a kernel call. However, these statistics do not give the
+   entire story: due to the way in which <productname>PostgreSQL</>
+   handles disk I/O, data that is not in the
+   <productname>PostgreSQL</> buffer cache may still reside in the
+   kernel's I/O cache, and may therefore still be fetched without
+   requiring a physical read. Users interested in obtaining more
+   detailed information on <productname>PostgreSQL</> I/O behavior are
+   advised to use the <productname>PostgreSQL</> statistics collector
+   in combination with operating system utilities that allow insight
+   into the kernel's handling of I/O.
   </para>
 
   <para>
@@ -401,7 +416,7 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
       <entry><function>pg_stat_get_db_blocks_hit</function>(<type>oid</type>)</entry>
       <entry><type>bigint</type></entry>
       <entry>
-       Number of disk block requests found in cache for database
+       Number of disk block fetch requests found in cache for database
       </entry>
      </row>
 
@@ -478,7 +493,7 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
       <entry><type>set of integer</type></entry>
       <entry>
        Set of currently active backend IDs (from 1 to N where N is the
-       number of active backends).  See usage example below.
+       number of active backends).  See usage example below
       </entry>
      </row>
 
@@ -518,15 +533,27 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
       <entry><function>pg_stat_get_backend_activity</function>(<type>integer</type>)</entry>
       <entry><type>text</type></entry>
       <entry>
-       Current query of backend process (NULL if caller is not superuser)
+       Current query of backend process (NULL if caller is not
+       superuser, or <varname>STATS_COMMAND_STRING</varname> is not enabled)
       </entry>
      </row>
 
      <row>
+      <entry><function>pg_stat_get_backend_activity_start</function>(<type>integer</type>)</entry>
+      <entry><type>text</type></entry>
+      <entry>
+       The time at which the specified backend's currently executing query was
+       initiated (NULL if caller is not superuser, or
+       <varname>STATS_COMMAND_STRING</varname> is not enabled)
+      </entry>
+     </row>
+
+
+     <row>
       <entry><function>pg_stat_reset</function>()</entry>
       <entry><type>boolean</type></entry>
       <entry>
-       Reset all currently collected statistics.
+       Reset all currently collected statistics
       </entry>
      </row>
     </tbody>
@@ -535,7 +562,8 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
 
    <note>
     <para>
-     Blocks_fetched minus blocks_hit gives the number of kernel
+     <literal>blocks_fetched</literal> minus
+     <literal>blocks_hit</literal> gives the number of kernel
      <function>read()</> calls issued for the table, index, or
      database; but the actual number of physical reads is usually
      lower due to kernel-level buffering.
diff --git a/doc/src/sgml/runtime.sgml b/doc/src/sgml/runtime.sgml
index 86d5ddc2852..408c81e2ee5 100644
--- a/doc/src/sgml/runtime.sgml
+++ b/doc/src/sgml/runtime.sgml
@@ -1,5 +1,5 @@
 <!--
-$Header: /cvsroot/pgsql/doc/src/sgml/runtime.sgml,v 1.170 2003/03/04 21:51:19 tgl Exp $
+$Header: /cvsroot/pgsql/doc/src/sgml/runtime.sgml,v 1.171 2003/03/20 03:34:55 momjian Exp $
 -->
 
 <Chapter Id="runtime">
@@ -1182,16 +1182,31 @@ env PGOPTIONS='-c geqo=off' psql
 
      <varlistentry>
       <term><varname>STATS_COMMAND_STRING</varname> (<type>boolean</type>)</term>
+      <listitem>
+       <para>
+        Enables the collection of statistics on the currently
+        executing command of each backend, along with the time at
+        which that query began execution. This option is off by
+        default. Note that even when enabled, this information is only
+        visible to the superuser, so it should not represent a
+        security risk. This data can be accessed via the
+        <structname>pg_stat_activity</structname> system view; refer
+        to the &cite-admin; for more information.
+       </para>
+      </listitem>
+     </varlistentry>
+
+     <varlistentry>
       <term><varname>STATS_BLOCK_LEVEL</varname> (<type>boolean</type>)</term>
       <term><varname>STATS_ROW_LEVEL</varname> (<type>boolean</type>)</term>
       <listitem>
        <para>
-        Determines what information backends send to the statistics
-        collector process: current commands, block-level activity
-        statistics, or row-level activity statistics.  All default to
-        off.  Enabling statistics collection costs a small amount of
-        time per query, but is invaluable for debugging and
-        performance tuning.
+        Enables the collection of block-level and row-level statistics
+        on database activity, respectively. These options are off by
+        default. This data can be accessed via the
+        <structname>pg_stat</structname> and
+        <structname>pg_statio</structname> family of system views;
+        refer to the &cite-admin; for more information.
        </para>
       </listitem>
      </varlistentry>