Re: Additional current timestamp values

I am not happy with my patch and am going to try a more comprehensive
restructuring --- will post later.

---------------------------------------------------------------------------

Bruce Momjian wrote:
> Tom Lane wrote:
> > Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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);
>

>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq

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

+ If your life is a hard drive, Christ can be your backup. +