Re: Additional current timestamp values - Mailing list pgsql-patches
From | Bruce Momjian |
---|---|
Subject | Re: Additional current timestamp values |
Date | |
Msg-id | 200604231830.k3NIUpE22328@candle.pha.pa.us Whole thread Raw |
In response to | Re: Additional current timestamp values (Bruce Momjian <pgman@candle.pha.pa.us>) |
Responses |
Re: Additional current timestamp values
|
List | pgsql-patches |
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@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); > > > ---------------------------(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. +
pgsql-patches by date: