Additional current timestamp values - Mailing list pgsql-patches

I hBrendan Jurd wrote:
> On 8/7/05, Brendan Jurd <direvus@gmail.com> wrote:
> > Hi all,
> >
> > I propose to add an internal function gettime() that transparently
> > returns the current system time, as a timestamptz with maximum
> > precision.

Rather than applying the above patch, I have implemented this TODO with
the attached patch:

    * Add transaction_timestamp(), statement_timestamp(), clock_timestamp()
      functionality

      Current CURRENT_TIMESTAMP returns the start time of the current
      transaction, and gettimeofday() returns the wallclock time. This will
      make time reporting more consistent and will allow reporting of
      the statement start time.

I questioned whether we need transaction_timestamp() because it is the
same as CURRENT_TIMESTAMP and now(), but added this to the docs:

    <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 overhead of this patch is an additional gettimeofday() call for each
statement in a multi-statement transaction.  We already do a
gettimeofday() for each transaction, even single-statement transactions.
I see no way to avoid the additional function call.

One trick is that these should be the same:

    test=> SELECT statement_timestamp(), transaction_timestamp();
          statement_timestamp      |     transaction_timestamp
    -------------------------------+-------------------------------
     2006-03-20 16:59:33.790335-05 | 2006-03-20 16:59:33.790335-05
    (1 row)

and these should be different:

    test=> BEGIN;
    BEGIN
    test=> select statement_timestamp(), transaction_timestamp();
          statement_timestamp      |     transaction_timestamp
    -------------------------------+-------------------------------
     2006-03-20 16:59:55.347467-05 | 2006-03-20 16:59:54.520446-05
    (1 row)

And these should be the same:

    $ psql -c '
    INSERT INTO t VALUES (statement_timestamp());
    INSERT INTO t VALUES (statement_timestamp());' test
    INSERT 0 1
    $ psql test
    Welcome to psql 8.2devel, the PostgreSQL interactive terminal.

    Type:  \copyright for distribution terms
           \h for help with SQL commands
           \? for help with psql commands
           \g or terminate with semicolon to execute query
           \q to quit

    test=> SELECT * FROM t;
                   x
    -------------------------------
     2006-03-20 17:06:02.057077-05
     2006-03-20 17:06:02.057077-05
    (2 rows)

And they all work.  Is there a cleaner method than the one I have used?

I have also improved the documentation so it is clearer what value is
returned by each current data/time function.

--
  Bruce Momjian   http://candle.pha.pa.us
  SRA OSS, Inc.   http://www.sraoss.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    17 Mar 2006 20:04:27 -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
--- 6177,6182 ----
***************
*** 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>
--- 6184,6202 ----
      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>
Index: src/backend/access/transam/xact.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/access/transam/xact.c,v
retrieving revision 1.217
diff -c -c -r1.217 xact.c
*** src/backend/access/transam/xact.c    5 Mar 2006 15:58:22 -0000    1.217
--- src/backend/access/transam/xact.c    17 Mar 2006 20:04:29 -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,460 ----
  }

  /*
+  *    GetCurrentStatementStartTimestamp
+  */
+ TimestampTz
+ GetCurrentStatementStartTimestamp(void)
+ {
+     return stmtStartTimestamp;
+ }
+
+ /*
+  *    SetCurrentStatementStartTimestamp
+  */
+ void
+ SetCurrentStatementStartTimestamp(void)
+ {
+     TransactionState s = CurrentTransactionState;
+
+     /*
+      *    If it is a single-statement transaction, transaction_timestamp()
+      *    and statement_timestamp() should be identical, so only set
+      *    stmtStartTimestamp in the multi-statement case.
+      */
+     if (s->blockState == TBLOCK_INPROGRESS)
+         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
--- 1394,1402 ----
      XactLockTableInsert(s->transactionId);

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

      /*
       * initialize current transaction state fields
Index: src/backend/tcop/postgres.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/tcop/postgres.c,v
retrieving revision 1.482
diff -c -c -r1.482 postgres.c
*** src/backend/tcop/postgres.c    14 Mar 2006 22:48:21 -0000    1.482
--- src/backend/tcop/postgres.c    17 Mar 2006 20:04:30 -0000
***************
*** 1997,2002 ****
--- 1997,2004 ----
                  (errmsg_internal("StartTransactionCommand")));
          StartTransactionCommand();

+         SetCurrentStatementStartTimestamp();
+
          /* Set statement timeout running, if any */
          if (StatementTimeout > 0)
              enable_sig_alarm(StatementTimeout, true);
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    17 Mar 2006 20:04:33 -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.80
diff -c -c -r1.80 xact.h
*** src/include/access/xact.h    5 Mar 2006 15:58:54 -0000    1.80
--- src/include/access/xact.h    17 Mar 2006 20:04:34 -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.404
diff -c -c -r1.404 pg_proc.h
*** src/include/catalog/pg_proc.h    10 Mar 2006 20:15:26 -0000    1.404
--- src/include/catalog/pg_proc.h    17 Mar 2006 20:04:42 -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 = 2597 (  transaction_timestamp    PGNSP PGUID 12 f f t f s 0 1184 "" _null_ _null_ _null_    now -
_null_)); 
+ DESCR("current transaction time");
+ DATA(insert OID = 2598 (  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 = 2599 (  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    17 Mar 2006 20:04:43 -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);