Re: Additional current timestamp values - Mailing list pgsql-patches

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Tom Lane wrote:
> >> The patch as given strikes me as pretty broken --- it does not advance
> >> statement_timestamp when I would expect (AFAICS it only sets it during
> >> transaction start).
>
> > Uh, it does advance:
>
> But not once per statement --- in reality, you get a fairly arbitrary
> behavior that will advance in some cases and not others when dealing
> with a multi-statement querystring.  Your example showing that it fails
> to advance in a psql -c string shows this ... don't you think most
> people would call that a bug?
>
> If it's "statement" timestamp then I think it ought to advance once per
> SQL statement, which this isn't doing.  (As I already said, though, that
> isn't the behavior I really want.  My point is just that the code's
> behavior is an extremely strange, nonintuitive definition of the word
> "statement".)
>
> > I have always been confused if
> > statement_timeout times queries inside server-side functions, for
> > example.  I don't think it should.
>
> That's exactly my point; I agree that we don't want it doing that,
> but that being the case, "statement" isn't a great name for the units
> that we are actually processing.  We're really wanting to do these
> things once per client command, or maybe per client query would be a
> better name.

I have updated my patch based on community comments.  One cleanup is
that I now set statement_timestamp(), and then base
transaction_timestamp() (aka now()) on the statement_timestamp of BEGIN,
which is a much cleaner API.

As far as how often statement_timestamp() is called, when a "Q" query
arrives, it calls exec_simple_query(), which calls start_xact_command()
before it parses anything, setting the transaction start.  It is called
inside the per-command loop, but it does nothing unless
finish_xact_command() was called to finish a transaction.

(Is there some double-processing here for BEGIN because it will re-run
the initialization stuff?)

I also documented how statement_timestamp behaves when multiple
statements are in the same query string, and when called from functions.

One side-affect of tracking transaction_timestamp based on
statement_timestamp() is if multiple statements are sent in a single
query string, and multiple transactions are used, statement_timestamp
will be advanced so transaction_timestamp() can vary.  Again, not ideal,
but probably the cleanest we are going to be able to do.  If we decided
to just have statement_timestamp be the arrival of the string always, we
are going to incur additional gettimeofday() calls and the code is going
to be more complex.

FYI, this is exactly how statement_timeout behaves, and no one has
complained about it.

The only other approach would be to put the statement_timestamp()
setting call in exec_simple_query(), and in all the protocol-level
functions, and fastpath.  You then also need to do a separate call for
transaction_timestamp() because you want that to advance if multiple
transactions are in the same query string.

If we want to take that approach, should statement_timeout code also be
moved around?

See my other post about the use of the term "statement".  I don't think
most people think about sending multiple statements, so if we document
its behavior, that is good enough.

--
  Bruce Momjian   http://candle.pha.pa.us
  EnterpriseDB    http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +
Index: doc/src/sgml/func.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/func.sgml,v
retrieving revision 1.313
diff -c -c -r1.313 func.sgml
*** doc/src/sgml/func.sgml    10 Mar 2006 20:15:25 -0000    1.313
--- doc/src/sgml/func.sgml    23 Apr 2006 02:26:19 -0000
***************
*** 5303,5308 ****
--- 5303,5317 ----
      <primary>now</primary>
     </indexterm>
     <indexterm>
+     <primary>transaction_timestamp</primary>
+    </indexterm>
+    <indexterm>
+     <primary>statement_timestamp</primary>
+    </indexterm>
+    <indexterm>
+     <primary>clock_timestamp</primary>
+    </indexterm>
+    <indexterm>
      <primary>timeofday</primary>
     </indexterm>

***************
*** 5358,5364 ****
         <row>
          <entry><literal><function>current_timestamp</function></literal></entry>
          <entry><type>timestamp with time zone</type></entry>
!         <entry>Date and time; see <xref linkend="functions-datetime-current">
          </entry>
          <entry></entry>
          <entry></entry>
--- 5367,5373 ----
         <row>
          <entry><literal><function>current_timestamp</function></literal></entry>
          <entry><type>timestamp with time zone</type></entry>
!         <entry>Date and time of start of current transaction; see <xref linkend="functions-datetime-current">
          </entry>
          <entry></entry>
          <entry></entry>
***************
*** 5474,5481 ****
         <row>
          <entry><literal><function>now</function>()</literal></entry>
          <entry><type>timestamp with time zone</type></entry>
!         <entry>Current date and time (equivalent to
!          <function>current_timestamp</function>); see <xref linkend="functions-datetime-current">
          </entry>
          <entry></entry>
          <entry></entry>
--- 5483,5518 ----
         <row>
          <entry><literal><function>now</function>()</literal></entry>
          <entry><type>timestamp with time zone</type></entry>
!         <entry>Date and time of start of current transaction (equivalent to
!          <function>CURRENT_TIMESTAMP</function>); see <xref linkend="functions-datetime-current">
!         </entry>
!         <entry></entry>
!         <entry></entry>
!        </row>
!
!        <row>
!         <entry><literal><function>transaction_timestamp</function>()</literal></entry>
!         <entry><type>timestamp with time zone</type></entry>
!         <entry>Date and time of start of current transaction (equivalent to
!          <function>CURRENT_TIMESTAMP</function>); see <xref linkend="functions-datetime-current">
!         </entry>
!         <entry></entry>
!         <entry></entry>
!        </row>
!
!        <row>
!         <entry><literal><function>statement_timestamp</function>()</literal></entry>
!         <entry><type>timestamp with time zone</type></entry>
!         <entry>Date and time of start of current statement; see <xref linkend="functions-datetime-current">
!         </entry>
!         <entry></entry>
!         <entry></entry>
!        </row>
!
!        <row>
!         <entry><literal><function>clock_timestamp</function>()</literal></entry>
!         <entry><type>timestamp with time zone</type></entry>
!         <entry>Current date and time (changes during statement execution); see <xref
linkend="functions-datetime-current">
          </entry>
          <entry></entry>
          <entry></entry>
***************
*** 5484,5490 ****
         <row>
          <entry><literal><function>timeofday</function>()</literal></entry>
          <entry><type>text</type></entry>
!         <entry>Current date and time; see <xref linkend="functions-datetime-current">
          </entry>
          <entry></entry>
          <entry></entry>
--- 5521,5528 ----
         <row>
          <entry><literal><function>timeofday</function>()</literal></entry>
          <entry><type>text</type></entry>
!         <entry>Current date and time (like <function>clock_timestamp</>), but as a Unix-style <type>text</> value;
!         see <xref linkend="functions-datetime-current">
          </entry>
          <entry></entry>
          <entry></entry>
***************
*** 6072,6078 ****
    </sect2>

    <sect2 id="functions-datetime-current">
!    <title>Current Date/Time</title>

     <indexterm>
      <primary>date</primary>
--- 6110,6116 ----
    </sect2>

    <sect2 id="functions-datetime-current">
!    <title>Date/Time of Transaction Start</title>

     <indexterm>
      <primary>date</primary>
***************
*** 6085,6092 ****
     </indexterm>

     <para>
!     The following functions are available to obtain the current date and/or
!     time:
  <synopsis>
  CURRENT_DATE
  CURRENT_TIME
--- 6123,6130 ----
     </indexterm>

     <para>
!     The following functions are available to obtain the date and/or
!     time of the start of the current transaction:
  <synopsis>
  CURRENT_DATE
  CURRENT_TIME
***************
*** 6147,6158 ****
     </para>

     <para>
-     The function <function>now()</function> is the traditional
-     <productname>PostgreSQL</productname> equivalent to
-     <function>CURRENT_TIMESTAMP</function>.
-    </para>
-
-    <para>
      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
--- 6185,6190 ----
***************
*** 6160,6185 ****
      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
!     time stamp.
     </para>

!    <note>
!     <para>
!      Other database systems may advance these values more
!      frequently.
!     </para>
!    </note>

     <para>
!     There is also the function <function>timeofday()</function> which
!     returns the wall-clock time and advances during transactions.  For
!     historical reasons <function>timeofday()</function> 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>
! </screen>
     </para>

     <para>
--- 6192,6222 ----
      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
!     time stamp.  Consider using <function>statement_timestamp</> or
!     <function>clock_timestamp</> if you need something that changes
!     more frequently.
     </para>

!    <para>
!     <function>CURRENT_TIMESTAMP</> might not be the
!     transaction start time on other database systems.
!     For this reason, and for completeness,
!     <function>transaction_timestamp</> is provided.
!     The function <function>now()</function> is the traditional
!     <productname>PostgreSQL</productname> equivalent to
!     the SQL-standard <function>CURRENT_TIMESTAMP</function>.
!    </para>

     <para>
!     <function>STATEMENT_TIMESTAMP</> is the time the statement
!     arrived at the server from the client.  It is not the time
!     the command started execution.  If multiple commands were
!     sent as a single query string to the server, each command
!     has the same <function>STATEMENT_TIMESTAMP</> because they
!     all arrived at the same time.  Also, commands executed
!     by server-side functions have a <function>STATEMENT_TIMESTAMP</>
!     based on the time the client sent the query that triggered
!     the function, not the time the function was executed.
     </para>

     <para>
Index: src/backend/access/transam/xact.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/access/transam/xact.c,v
retrieving revision 1.219
diff -c -c -r1.219 xact.c
*** src/backend/access/transam/xact.c    29 Mar 2006 21:17:37 -0000    1.219
--- src/backend/access/transam/xact.c    23 Apr 2006 02:26:21 -0000
***************
*** 172,177 ****
--- 172,178 ----
   * keep it inside the TransactionState stack.
   */
  static TimestampTz xactStartTimestamp;
+ static TimestampTz stmtStartTimestamp;

  /*
   * GID to be used for preparing the current transaction.  This is also
***************
*** 428,433 ****
--- 429,452 ----
  }

  /*
+  *    GetCurrentStatementStartTimestamp
+  */
+ TimestampTz
+ GetCurrentStatementStartTimestamp(void)
+ {
+     return stmtStartTimestamp;
+ }
+
+ /*
+  *    SetCurrentStatementStartTimestamp
+  */
+ void
+ SetCurrentStatementStartTimestamp(void)
+ {
+     stmtStartTimestamp = GetCurrentTimestamp();
+ }
+
+ /*
   *    GetCurrentTransactionNestLevel
   *
   * Note: this will return zero when not inside any transaction, one when
***************
*** 1367,1375 ****
      XactLockTableInsert(s->transactionId);

      /*
!      * set now()
       */
!     xactStartTimestamp = GetCurrentTimestamp();

      /*
       * initialize current transaction state fields
--- 1386,1394 ----
      XactLockTableInsert(s->transactionId);

      /*
!      * now() and statement_timestamp() should be the same time
       */
!     xactStartTimestamp = stmtStartTimestamp;

      /*
       * initialize current transaction state fields
Index: src/backend/tcop/postgres.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/tcop/postgres.c,v
retrieving revision 1.485
diff -c -c -r1.485 postgres.c
*** src/backend/tcop/postgres.c    22 Apr 2006 01:26:00 -0000    1.485
--- src/backend/tcop/postgres.c    23 Apr 2006 02:26:24 -0000
***************
*** 2000,2006 ****


  /*
!  * Convenience routines for starting/committing a single command.
   */
  static void
  start_xact_command(void)
--- 2000,2008 ----


  /*
!  *    Check if the newly-arrived query string needs to have an implicit
!  *    transaction started.  Also set statement_timestamp() and optionally
!  *    statement_timeout.
   */
  static void
  start_xact_command(void)
***************
*** 2009,2014 ****
--- 2011,2018 ----
      {
          ereport(DEBUG3,
                  (errmsg_internal("StartTransactionCommand")));
+
+         SetCurrentStatementStartTimestamp();
          StartTransactionCommand();

          /* Set statement timeout running, if any */
Index: src/backend/utils/adt/timestamp.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/adt/timestamp.c,v
retrieving revision 1.162
diff -c -c -r1.162 timestamp.c
*** src/backend/utils/adt/timestamp.c    6 Mar 2006 22:49:16 -0000    1.162
--- src/backend/utils/adt/timestamp.c    23 Apr 2006 02:26:26 -0000
***************
*** 920,925 ****
--- 920,937 ----
  }

  Datum
+ statement_timestamp(PG_FUNCTION_ARGS)
+ {
+     PG_RETURN_TIMESTAMPTZ(GetCurrentStatementStartTimestamp());
+ }
+
+ Datum
+ clock_timestamp(PG_FUNCTION_ARGS)
+ {
+     PG_RETURN_TIMESTAMPTZ(GetCurrentTimestamp());
+ }
+
+ Datum
  pgsql_postmaster_start_time(PG_FUNCTION_ARGS)
  {
      PG_RETURN_TIMESTAMPTZ(PgStartTime);
Index: src/include/access/xact.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/access/xact.h,v
retrieving revision 1.81
diff -c -c -r1.81 xact.h
*** src/include/access/xact.h    24 Mar 2006 04:32:13 -0000    1.81
--- src/include/access/xact.h    23 Apr 2006 02:26:27 -0000
***************
*** 141,146 ****
--- 141,148 ----
  extern SubTransactionId GetCurrentSubTransactionId(void);
  extern CommandId GetCurrentCommandId(void);
  extern TimestampTz GetCurrentTransactionStartTimestamp(void);
+ extern TimestampTz GetCurrentStatementStartTimestamp(void);
+ extern void SetCurrentStatementStartTimestamp(void);
  extern int    GetCurrentTransactionNestLevel(void);
  extern bool TransactionIdIsCurrentTransactionId(TransactionId xid);
  extern void CommandCounterIncrement(void);
Index: src/include/catalog/pg_proc.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/catalog/pg_proc.h,v
retrieving revision 1.405
diff -c -c -r1.405 pg_proc.h
*** src/include/catalog/pg_proc.h    5 Apr 2006 22:11:55 -0000    1.405
--- src/include/catalog/pg_proc.h    23 Apr 2006 02:26:33 -0000
***************
*** 1614,1619 ****
--- 1614,1625 ----
  DESCR("convert time with time zone and date to timestamp with time zone");
  DATA(insert OID = 1299 (  now               PGNSP PGUID 12 f f t f s 0 1184 "" _null_ _null_ _null_    now - _null_
));
  DESCR("current transaction time");
+ DATA(insert OID = 2647 (  transaction_timestamp    PGNSP PGUID 12 f f t f s 0 1184 "" _null_ _null_ _null_    now -
_null_)); 
+ DESCR("current transaction time");
+ DATA(insert OID = 2648 (  statement_timestamp    PGNSP PGUID 12 f f t f s 0 1184 "" _null_ _null_ _null_
statement_timestamp- _null_ )); 
+ DESCR("current statement time");
+ DATA(insert OID = 2649 (  clock_timestamp    PGNSP PGUID 12 f f t f v 0 1184 "" _null_ _null_ _null_
clock_timestamp- _null_ )); 
+ DESCR("current clock time");

  /* OIDS 1300 - 1399 */

Index: src/include/utils/timestamp.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/utils/timestamp.h,v
retrieving revision 1.59
diff -c -c -r1.59 timestamp.h
*** src/include/utils/timestamp.h    6 Mar 2006 22:49:17 -0000    1.59
--- src/include/utils/timestamp.h    23 Apr 2006 02:26:36 -0000
***************
*** 284,289 ****
--- 284,291 ----
  extern Datum timestamptz_part(PG_FUNCTION_ARGS);

  extern Datum now(PG_FUNCTION_ARGS);
+ extern Datum statement_timestamp(PG_FUNCTION_ARGS);
+ extern Datum clock_timestamp(PG_FUNCTION_ARGS);

  extern Datum pgsql_postmaster_start_time(PG_FUNCTION_ARGS);