Thread: Re: gettime() - a timeofday() alternative
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. > Here's the patch. The changes to pg_proc.h, timestamp.h and timestamp.c are trivial. The changes to func.sgml are more comprehensive; I've split the section on Current Date/Time into two subsections, one that explains the transaction time functions and one for the system time functions. -- BJ
Attachment
This has been saved for the 8.2 release:
http://momjian.postgresql.org/cgi-bin/pgpatches_hold
---------------------------------------------------------------------------
Brendan 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.
> >
>
> Here's the patch.
>
> The changes to pg_proc.h, timestamp.h and timestamp.c are trivial.
> The changes to func.sgml are more comprehensive; I've split the
> section on Current Date/Time into two subsections, one that explains
> the transaction time functions and one for the system time functions.
>
> --
> BJ
[ Attachment, skipping... ]
[ Attachment, skipping... ]
[ Attachment, skipping... ]
[ Attachment, skipping... ]
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
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);
Bruce Momjian wrote: > Rather than applying the above patch, I have implemented this TODO > with the attached patch: > > * Add transaction_timestamp(), statement_timestamp(), > clock_timestamp() functionality The most common complaint that I recall is that current_timestamp returns the transaction timestamp rather than the statement timestamp, which is what many expect. How does your patch address that? Do we really need clock_timestamp? -- Peter Eisentraut http://developer.postgresql.org/~petere/
Peter Eisentraut wrote: > Bruce Momjian wrote: > > Rather than applying the above patch, I have implemented this TODO > > with the attached patch: > > > > * Add transaction_timestamp(), statement_timestamp(), > > clock_timestamp() functionality > > The most common complaint that I recall is that current_timestamp > returns the transaction timestamp rather than the statement timestamp, > which is what many expect. How does your patch address that? No, we believe the standard requires it. > Do we really need clock_timestamp? Yes, because timeofday() returns a unix text string. Some people do want a wallclock current timestamp. -- 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. +
Bruce Momjian wrote: > <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. Well, transaction_timestamp() is even more unlikely to be the transaction start time on other database systems :) If the user wants non-standard syntax for getting the timestamp at which the current transaction began, we already have now(). > One trick is that these should be the same: > > test=> SELECT statement_timestamp(), transaction_timestamp(); Should they be? It seems quite reasonable to me that the DBMS begins a transaction internally (setting transaction_timestamp()), and then a short while later begins executing the statement submitted by the user, at which point statement_timestamp() is set. Perhaps ensuring they are identical for single-statement transactions is the best behavior, I just don't think this is required behavior. > And these should be the same: > > $ psql -c ' > INSERT INTO t VALUES (statement_timestamp()); > INSERT INTO t VALUES (statement_timestamp());' test > INSERT 0 1 Uh, why should these be the same? -Neil
Bruce Momjian wrote:
> Peter Eisentraut wrote:
>> The most common complaint that I recall is that current_timestamp
>> returns the transaction timestamp rather than the statement timestamp,
>> which is what many expect. How does your patch address that?
>
> No, we believe the standard requires it.
My copy of SQL 200n has the following to say:
Annex C, paragraph 16:
"The time of evaluation of the CURRENT_DATE, CURRENT_TIME, and
CURRENT_TIMESTAMP functions during the execution of an
SQL-statement is implementation-dependent."
6.31, <datetime value function>:
(1) The <datetime value function>s CURRENT_DATE, CURRENT_TIME,
and CURRENT_TIMESTAMP respectively return the current date,
current time, and current timestamp; the time and timestamp values
are returned with time zone displacement equal to the current
default time zone displacement of the SQL-session. [...]
(2) Let S be an <SQL procedure statement> that is not generally
contained in a <triggered action>. All <datetime value function>s
that are contained in <value expression>s that are generally
contained, without an intervening <routine invocation> whose subject
routines do not include an SQL function, either in S without an
intervening <SQL procedure statement> or in an <SQL procedure
statement> contained in the <triggered action> of a trigger
activated as a consequence of executing S, are effectively evaluated
simultaneously. The time of evaluation of a <datetime value
function> during the execution of S and its activated triggers is
implementation-dependent.
-Neil
Neil Conway wrote: > Bruce Momjian wrote: > > <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. > > Well, transaction_timestamp() is even more unlikely to be the > transaction start time on other database systems :) If the user wants > non-standard syntax for getting the timestamp at which the current > transaction began, we already have now(). True, which is why I brought it up. I think a good argument can be made that we don't need two non-standard ways of specifying the transaction timestamp, but we need to decide that as a group. > > One trick is that these should be the same: > > > > test=> SELECT statement_timestamp(), transaction_timestamp(); > > Should they be? It seems quite reasonable to me that the DBMS begins a > transaction internally (setting transaction_timestamp()), and then a > short while later begins executing the statement submitted by the user, > at which point statement_timestamp() is set. > > Perhaps ensuring they are identical for single-statement transactions is > the best behavior, I just don't think this is required behavior. Yea, perhaps it isn't required, but it seems like a good idea. It will avoid confusion and seems logical. :-) > > And these should be the same: > > > > $ psql -c ' > > INSERT INTO t VALUES (statement_timestamp()); > > INSERT INTO t VALUES (statement_timestamp());' test > > INSERT 0 1 > > Uh, why should these be the same? This gets into cases where a single statement generates more than one parsenode, e.g. rules. We want all the parse nodes to have the same timestamp. We had a long discussion that the statement time isn't really meaningful/logical, so I went with code that said the statement arrival time is the proper time to return, and be consistent. -- 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. +
Neil Conway wrote: > Bruce Momjian wrote: > > Peter Eisentraut wrote: > >> The most common complaint that I recall is that current_timestamp > >> returns the transaction timestamp rather than the statement timestamp, > >> which is what many expect. How does your patch address that? > > > > No, we believe the standard requires it. > > My copy of SQL 200n has the following to say: > > Annex C, paragraph 16: > > "The time of evaluation of the CURRENT_DATE, CURRENT_TIME, and > CURRENT_TIMESTAMP functions during the execution of an > SQL-statement is implementation-dependent." > > 6.31, <datetime value function>: > > (1) The <datetime value function>s CURRENT_DATE, CURRENT_TIME, > and CURRENT_TIMESTAMP respectively return the current date, > current time, and current timestamp; the time and timestamp values > are returned with time zone displacement equal to the current > default time zone displacement of the SQL-session. [...] > > (2) Let S be an <SQL procedure statement> that is not generally > contained in a <triggered action>. All <datetime value function>s > that are contained in <value expression>s that are generally > contained, without an intervening <routine invocation> whose subject > routines do not include an SQL function, either in S without an > intervening <SQL procedure statement> or in an <SQL procedure > statement> contained in the <triggered action> of a trigger > activated as a consequence of executing S, are effectively evaluated > simultaneously. The time of evaluation of a <datetime value > function> during the execution of S and its activated triggers is > implementation-dependent. OK, so we just decided transaction timestamp is the most logical value for CURRENT_TIMESTAMP. Anyway, this might mean we should have transaction_timestamp for completeness. Not sure. -- 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. +
Neil Conway <neilc@samurai.com> writes:
> Bruce Momjian wrote:
>> One trick is that these should be the same:
>> test=> SELECT statement_timestamp(), transaction_timestamp();
> Should they be?
ISTM that the most useful definition of "statement_timestamp" is really
"time of arrival of the latest interactive command from the client", and
as such it should not be tied to statement start per se at all.
I'd be in favor of doing gettimeofday() upon receiving a client message,
reporting that value directly for statement_timestamp, and copying it
during transaction start to obtain the value to use for
transaction_timestamp. I don't much like the idea of doing a
gettimeofday() per SQL statement, especially not if that's taken to mean
every SQL statement issued by PL functions (and if it doesn't mean that,
"statement_timestamp" seems like the wrong name). One gettimeofday()
per client message doesn't seem too horrible though, since that's
certainly going to require at least a couple of kernel calls anyway.
Possibly we should call it "command_timestamp" not "statement_timestamp"
to help reduce confusion.
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). I don't like it stylistically either: ISTM either
these things are the responsibility of xact.c or they are the
responsibility of postgres.c, it is not sensible to have both modules
assigning to statement_timestamp.
BTW, now that I look at it, the "statement_timeout" GUC variable seems
to have much of the same confusion about whether "statement" is
equivalent to "interactive command" or not.
regards, tom lane
Tom Lane wrote:
> Neil Conway <neilc@samurai.com> writes:
> > Bruce Momjian wrote:
> >> One trick is that these should be the same:
> >> test=> SELECT statement_timestamp(), transaction_timestamp();
>
> > Should they be?
>
> ISTM that the most useful definition of "statement_timestamp" is really
> "time of arrival of the latest interactive command from the client", and
> as such it should not be tied to statement start per se at all.
I see your point.
> I'd be in favor of doing gettimeofday() upon receiving a client message,
> reporting that value directly for statement_timestamp, and copying it
> during transaction start to obtain the value to use for
> transaction_timestamp. I don't much like the idea of doing a
> gettimeofday() per SQL statement, especially not if that's taken to mean
> every SQL statement issued by PL functions (and if it doesn't mean that,
> "statement_timestamp" seems like the wrong name). One gettimeofday()
> per client message doesn't seem too horrible though, since that's
> certainly going to require at least a couple of kernel calls anyway.
>
> Possibly we should call it "command_timestamp" not "statement_timestamp"
> to help reduce confusion.
>
> 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). I don't like it stylistically either: ISTM either
Uh, it does advance:
test=> BEGIN;
BEGIN
test=> SELECT statement_timestamp(), transaction_timestamp();
statement_timestamp | transaction_timestamp
------------------------------+-------------------------------
2006-03-20 18:49:17.88062-05 | 2006-03-20 18:49:11.922934-05
(1 row)
test=> SELECT statement_timestamp(), transaction_timestamp();
statement_timestamp | transaction_timestamp
-------------------------------+-------------------------------
2006-03-20 18:49:19.176823-05 | 2006-03-20 18:49:11.922934-05
(1 row)
start_xact_command() is kind of badly worded. It calls
StartTransactionCommand(), which might or might not start a transaction,
then it does statement_timeout setup. I have always been confused if
statement_timeout times queries inside server-side functions, for
example. I don't think it should.
> these things are the responsibility of xact.c or they are the
> responsibility of postgres.c, it is not sensible to have both modules
> assigning to statement_timestamp.
It was done to minimize code change and limit the number of
gettimeofday() calls.
> BTW, now that I look at it, the "statement_timeout" GUC variable seems
> to have much of the same confusion about whether "statement" is
> equivalent to "interactive command" or not.
True.
--
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. +
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.
regards, tom lane
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? I assume RULES should have the same statement_timestamp and I figured my approach was the only one that would work. > 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 suppose so. > > 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. Right. -- 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. +
>>> On Mon, Mar 20, 2006 at 7:58 pm, in message <200603210158.k2L1wMY01170@candle.pha.pa.us>, Bruce Momjian <pgman@candle.pha.pa.us> wrote: > Tom Lane wrote: >> 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. >> "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. > > Right. What about "query string"? If you want to include the term "client", I would find "client query string" less confusing than "client command" or "client query". If it's not always in the form of a string, maybe "client xxx batch", where xxx could be statement, request, command, or query. -Kevin
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
>>> "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.
>>
>> Right.
> What about "query string"? If you want to include the term "client", I
> would find "client query string" less confusing than "client command" or
> "client query".
"Query string" is a term we've used in the past, and it shows up in the
source code. I could live with that, but I'm not sure if it's got any
good connotations for people who haven't got their hands dirty in the
code ...
regards, tom lane
Tom Lane wrote: > "Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes: > >>> "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. > >> > >> Right. > > > What about "query string"? If you want to include the term "client", I > > would find "client query string" less confusing than "client command" or > > "client query". > > "Query string" is a term we've used in the past, and it shows up in the > source code. I could live with that, but I'm not sure if it's got any > good connotations for people who haven't got their hands dirty in the > code ... Peter hammered us quite a bit in the past that we send "statements", not just queries (aka SELECT). It is hard to argue with that. -- Bruce Momjian http://candle.pha.pa.us + If your life is a hard drive, Christ can be your backup. +
Kevin Grittner wrote: > >>> On Mon, Mar 20, 2006 at 7:58 pm, in message > <200603210158.k2L1wMY01170@candle.pha.pa.us>, Bruce Momjian > <pgman@candle.pha.pa.us> wrote: > > Tom Lane wrote: > >> 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. > > >> "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. > > > > Right. > > What about "query string"? If you want to include the term "client", I > would find "client query string" less confusing than "client command" or > "client query". If it's not always in the form of a string, maybe > "client xxx batch", where xxx could be statement, request, command, or > query. We could use something like query_arrived_timestamp or something like that, but it kind of confuses the distinction between it and transaction_timestamp(), and for 99% of users, they don't even realize you can send multiple statements in a single query. I am thinking we call it statement_timestamp (like statement_timeout) and just document its behavior. No one has problems with statement_timeout(), and that has the exact same behavior as statement_timestamp() will have. -- Bruce Momjian http://candle.pha.pa.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
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);
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. +
Here is an updated patch. I broke out the statement_timestamp and statement_timeout handling into separate functions, initialize_command() and finalize_command(), which call the xact start/stop internally. This clears up the API because now start/stop xact can be called independent of the statement tracking operations. It also makes all commands arriving in a single query string have the same statement_timestamp (even if in different transactions), and share the same statement_timeout timer. I have also documented that statement_timeout is the tracked from the time the command arrives at the server. --------------------------------------------------------------------------- Bruce Momjian wrote: > > 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/config.sgml =================================================================== RCS file: /cvsroot/pgsql/doc/src/sgml/config.sgml,v retrieving revision 1.55 diff -c -c -r1.55 config.sgml *** doc/src/sgml/config.sgml 23 Apr 2006 03:39:48 -0000 1.55 --- doc/src/sgml/config.sgml 24 Apr 2006 02:44:03 -0000 *************** *** 3279,3285 **** <listitem> <para> Abort any statement that takes over the specified number of ! milliseconds. If <varname>log_min_error_statement</> is set to <literal>ERROR</> or lower, the statement that timed out will also be logged. A value of zero (the default) turns off the limitation. --- 3279,3286 ---- <listitem> <para> Abort any statement that takes over the specified number of ! milliseconds, starting from the time the command arrives at the server ! from the client. If <varname>log_min_error_statement</> is set to <literal>ERROR</> or lower, the statement that timed out will also be logged. A value of zero (the default) turns off the limitation. Index: doc/src/sgml/func.sgml =================================================================== RCS file: /cvsroot/pgsql/doc/src/sgml/func.sgml,v retrieving revision 1.314 diff -c -c -r1.314 func.sgml *** doc/src/sgml/func.sgml 23 Apr 2006 03:39:50 -0000 1.314 --- doc/src/sgml/func.sgml 24 Apr 2006 02:44:11 -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 *************** *** 6139,6150 **** </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 ---- *************** *** 6152,6177 **** 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,6214 ---- 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 24 Apr 2006 02:44:13 -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 24 Apr 2006 02:44:16 -0000 *************** *** 119,124 **** --- 119,129 ---- static bool xact_started = false; /* + * Flag to keep track of whether we have done statement initialization. + */ + static bool command_initialized = false; + + /* * Flag to indicate that we are doing the outer loop's read-from-client, * as opposed to any random read from client that might happen within * commands like COPY FROM STDIN. *************** *** 164,169 **** --- 169,176 ---- static bool log_after_parse(List *raw_parsetree_list, const char *query_string, char **prepare_string); static List *pg_rewrite_queries(List *querytree_list); + static void initialize_command(void); + static void finalize_command(void); static void start_xact_command(void); static void finish_xact_command(void); static bool IsTransactionExitStmt(Node *parsetree); *************** *** 858,864 **** * one of those, else bad things will happen in xact.c. (Note that this * will normally change current memory context.) */ ! start_xact_command(); /* * Zap any pre-existing unnamed statement. (While not strictly necessary, --- 865,871 ---- * one of those, else bad things will happen in xact.c. (Note that this * will normally change current memory context.) */ ! initialize_command(); /* * Zap any pre-existing unnamed statement. (While not strictly necessary, *************** *** 1067,1073 **** /* * Close down transaction statement, if one is open. */ ! finish_xact_command(); /* * If there were no parsetrees, return EmptyQueryResponse message. --- 1074,1080 ---- /* * Close down transaction statement, if one is open. */ ! finalize_command(); /* * If there were no parsetrees, return EmptyQueryResponse message. *************** *** 1170,1176 **** * that this will normally change current memory context.) Nothing happens * if we are already in one. */ ! start_xact_command(); /* * Switch to appropriate context for constructing parsetrees. --- 1177,1183 ---- * that this will normally change current memory context.) Nothing happens * if we are already in one. */ ! initialize_command(); /* * Switch to appropriate context for constructing parsetrees. *************** *** 1393,1399 **** * this will normally change current memory context.) Nothing happens if * we are already in one. */ ! start_xact_command(); /* Switch back to message context */ MemoryContextSwitchTo(MessageContext); --- 1400,1406 ---- * this will normally change current memory context.) Nothing happens if * we are already in one. */ ! initialize_command(); /* Switch back to message context */ MemoryContextSwitchTo(MessageContext); *************** *** 1759,1765 **** * Ensure we are in a transaction command (this should normally be the * case already due to prior BIND). */ ! start_xact_command(); /* * If we are in aborted transaction state, the only portals we can --- 1766,1772 ---- * Ensure we are in a transaction command (this should normally be the * case already due to prior BIND). */ ! initialize_command(); /* * If we are in aborted transaction state, the only portals we can *************** *** 1883,1889 **** * Start up a transaction command. (Note that this will normally change * current memory context.) Nothing happens if we are already in one. */ ! start_xact_command(); /* Switch back to message context */ MemoryContextSwitchTo(MessageContext); --- 1890,1896 ---- * Start up a transaction command. (Note that this will normally change * current memory context.) Nothing happens if we are already in one. */ ! initialize_command(); /* Switch back to message context */ MemoryContextSwitchTo(MessageContext); *************** *** 1961,1967 **** * Start up a transaction command. (Note that this will normally change * current memory context.) Nothing happens if we are already in one. */ ! start_xact_command(); /* Switch back to message context */ MemoryContextSwitchTo(MessageContext); --- 1968,1974 ---- * Start up a transaction command. (Note that this will normally change * current memory context.) Nothing happens if we are already in one. */ ! initialize_command(); /* Switch back to message context */ MemoryContextSwitchTo(MessageContext); *************** *** 2000,2015 **** /* ! * Convenience routines for starting/committing a single command. */ static void ! start_xact_command(void) { ! if (!xact_started) { ! ereport(DEBUG3, ! (errmsg_internal("StartTransactionCommand"))); ! StartTransactionCommand(); /* Set statement timeout running, if any */ if (StatementTimeout > 0) --- 2007,2021 ---- /* ! * Start xact if necessary, and set statement_timestamp() and optionally ! * statement_timeout. */ static void ! initialize_command(void) { ! if (!command_initialized) { ! SetCurrentStatementStartTimestamp(); /* Set statement timeout running, if any */ if (StatementTimeout > 0) *************** *** 2017,2035 **** else cancel_from_timeout = false; ! xact_started = true; } } static void ! finish_xact_command(void) { ! if (xact_started) { /* Cancel any active statement timeout before committing */ disable_sig_alarm(true); ! /* Now commit the command */ ereport(DEBUG3, (errmsg_internal("CommitTransactionCommand"))); --- 2023,2070 ---- else cancel_from_timeout = false; ! command_initialized = true; } + start_xact_command(); } static void ! finalize_command(void) { ! if (command_initialized) { /* Cancel any active statement timeout before committing */ disable_sig_alarm(true); ! command_initialized = false; ! } ! finish_xact_command(); ! } ! ! ! /* ! * Check if the newly-arrived query string needs to have an implicit ! * transaction started. ! */ ! static void ! start_xact_command(void) ! { ! if (!xact_started) ! { ! ereport(DEBUG3, ! (errmsg_internal("StartTransactionCommand"))); ! ! StartTransactionCommand(); ! ! xact_started = true; ! } ! } ! ! static void ! finish_xact_command(void) ! { ! if (xact_started) ! { ereport(DEBUG3, (errmsg_internal("CommitTransactionCommand"))); *************** *** 3137,3143 **** /* We don't have a transaction command open anymore */ xact_started = false; ! /* Now we can allow interrupts again */ RESUME_INTERRUPTS(); } --- 3172,3179 ---- /* We don't have a transaction command open anymore */ xact_started = false; ! command_initialized = false; ! /* Now we can allow interrupts again */ RESUME_INTERRUPTS(); } *************** *** 3305,3311 **** pgstat_report_activity("<FASTPATH> function call"); /* start an xact for this function invocation */ ! start_xact_command(); /* switch back to message context */ MemoryContextSwitchTo(MessageContext); --- 3341,3347 ---- pgstat_report_activity("<FASTPATH> function call"); /* start an xact for this function invocation */ ! initialize_command(); /* switch back to message context */ MemoryContextSwitchTo(MessageContext); *************** *** 3328,3334 **** } /* commit the function-invocation transaction */ ! finish_xact_command(); send_ready_for_query = true; break; --- 3364,3370 ---- } /* commit the function-invocation transaction */ ! finalize_command(); send_ready_for_query = true; break; *************** *** 3416,3422 **** case 'S': /* sync */ pq_getmsgend(&input_message); ! finish_xact_command(); send_ready_for_query = true; break; --- 3452,3458 ---- case 'S': /* sync */ pq_getmsgend(&input_message); ! finalize_command(); send_ready_for_query = true; break; 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 24 Apr 2006 02:44:17 -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 24 Apr 2006 02:44:18 -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 24 Apr 2006 02:44:25 -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 24 Apr 2006 02:44:26 -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);
Patch applied, and catalog version updated. --------------------------------------------------------------------------- Bruce Momjian wrote: > > Here is an updated patch. I broke out the statement_timestamp and > statement_timeout handling into separate functions, initialize_command() > and finalize_command(), which call the xact start/stop internally. > > This clears up the API because now start/stop xact can be called > independent of the statement tracking operations. > > It also makes all commands arriving in a single query string have the > same statement_timestamp (even if in different transactions), and share > the same statement_timeout timer. > > I have also documented that statement_timeout is the tracked from the > time the command arrives at the server. > > --------------------------------------------------------------------------- > > Bruce Momjian wrote: > > > > 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/config.sgml > =================================================================== > RCS file: /cvsroot/pgsql/doc/src/sgml/config.sgml,v > retrieving revision 1.55 > diff -c -c -r1.55 config.sgml > *** doc/src/sgml/config.sgml 23 Apr 2006 03:39:48 -0000 1.55 > --- doc/src/sgml/config.sgml 24 Apr 2006 02:44:03 -0000 > *************** > *** 3279,3285 **** > <listitem> > <para> > Abort any statement that takes over the specified number of > ! milliseconds. If <varname>log_min_error_statement</> is set to > <literal>ERROR</> or lower, the statement that timed out will also be > logged. A value of zero (the default) turns off the > limitation. > --- 3279,3286 ---- > <listitem> > <para> > Abort any statement that takes over the specified number of > ! milliseconds, starting from the time the command arrives at the server > ! from the client. If <varname>log_min_error_statement</> is set to > <literal>ERROR</> or lower, the statement that timed out will also be > logged. A value of zero (the default) turns off the > limitation. > Index: doc/src/sgml/func.sgml > =================================================================== > RCS file: /cvsroot/pgsql/doc/src/sgml/func.sgml,v > retrieving revision 1.314 > diff -c -c -r1.314 func.sgml > *** doc/src/sgml/func.sgml 23 Apr 2006 03:39:50 -0000 1.314 > --- doc/src/sgml/func.sgml 24 Apr 2006 02:44:11 -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 > *************** > *** 6139,6150 **** > </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 ---- > *************** > *** 6152,6177 **** > 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,6214 ---- > 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 24 Apr 2006 02:44:13 -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 24 Apr 2006 02:44:16 -0000 > *************** > *** 119,124 **** > --- 119,129 ---- > static bool xact_started = false; > > /* > + * Flag to keep track of whether we have done statement initialization. > + */ > + static bool command_initialized = false; > + > + /* > * Flag to indicate that we are doing the outer loop's read-from-client, > * as opposed to any random read from client that might happen within > * commands like COPY FROM STDIN. > *************** > *** 164,169 **** > --- 169,176 ---- > static bool log_after_parse(List *raw_parsetree_list, > const char *query_string, char **prepare_string); > static List *pg_rewrite_queries(List *querytree_list); > + static void initialize_command(void); > + static void finalize_command(void); > static void start_xact_command(void); > static void finish_xact_command(void); > static bool IsTransactionExitStmt(Node *parsetree); > *************** > *** 858,864 **** > * one of those, else bad things will happen in xact.c. (Note that this > * will normally change current memory context.) > */ > ! start_xact_command(); > > /* > * Zap any pre-existing unnamed statement. (While not strictly necessary, > --- 865,871 ---- > * one of those, else bad things will happen in xact.c. (Note that this > * will normally change current memory context.) > */ > ! initialize_command(); > > /* > * Zap any pre-existing unnamed statement. (While not strictly necessary, > *************** > *** 1067,1073 **** > /* > * Close down transaction statement, if one is open. > */ > ! finish_xact_command(); > > /* > * If there were no parsetrees, return EmptyQueryResponse message. > --- 1074,1080 ---- > /* > * Close down transaction statement, if one is open. > */ > ! finalize_command(); > > /* > * If there were no parsetrees, return EmptyQueryResponse message. > *************** > *** 1170,1176 **** > * that this will normally change current memory context.) Nothing happens > * if we are already in one. > */ > ! start_xact_command(); > > /* > * Switch to appropriate context for constructing parsetrees. > --- 1177,1183 ---- > * that this will normally change current memory context.) Nothing happens > * if we are already in one. > */ > ! initialize_command(); > > /* > * Switch to appropriate context for constructing parsetrees. > *************** > *** 1393,1399 **** > * this will normally change current memory context.) Nothing happens if > * we are already in one. > */ > ! start_xact_command(); > > /* Switch back to message context */ > MemoryContextSwitchTo(MessageContext); > --- 1400,1406 ---- > * this will normally change current memory context.) Nothing happens if > * we are already in one. > */ > ! initialize_command(); > > /* Switch back to message context */ > MemoryContextSwitchTo(MessageContext); > *************** > *** 1759,1765 **** > * Ensure we are in a transaction command (this should normally be the > * case already due to prior BIND). > */ > ! start_xact_command(); > > /* > * If we are in aborted transaction state, the only portals we can > --- 1766,1772 ---- > * Ensure we are in a transaction command (this should normally be the > * case already due to prior BIND). > */ > ! initialize_command(); > > /* > * If we are in aborted transaction state, the only portals we can > *************** > *** 1883,1889 **** > * Start up a transaction command. (Note that this will normally change > * current memory context.) Nothing happens if we are already in one. > */ > ! start_xact_command(); > > /* Switch back to message context */ > MemoryContextSwitchTo(MessageContext); > --- 1890,1896 ---- > * Start up a transaction command. (Note that this will normally change > * current memory context.) Nothing happens if we are already in one. > */ > ! initialize_command(); > > /* Switch back to message context */ > MemoryContextSwitchTo(MessageContext); > *************** > *** 1961,1967 **** > * Start up a transaction command. (Note that this will normally change > * current memory context.) Nothing happens if we are already in one. > */ > ! start_xact_command(); > > /* Switch back to message context */ > MemoryContextSwitchTo(MessageContext); > --- 1968,1974 ---- > * Start up a transaction command. (Note that this will normally change > * current memory context.) Nothing happens if we are already in one. > */ > ! initialize_command(); > > /* Switch back to message context */ > MemoryContextSwitchTo(MessageContext); > *************** > *** 2000,2015 **** > > > /* > ! * Convenience routines for starting/committing a single command. > */ > static void > ! start_xact_command(void) > { > ! if (!xact_started) > { > ! ereport(DEBUG3, > ! (errmsg_internal("StartTransactionCommand"))); > ! StartTransactionCommand(); > > /* Set statement timeout running, if any */ > if (StatementTimeout > 0) > --- 2007,2021 ---- > > > /* > ! * Start xact if necessary, and set statement_timestamp() and optionally > ! * statement_timeout. > */ > static void > ! initialize_command(void) > { > ! if (!command_initialized) > { > ! SetCurrentStatementStartTimestamp(); > > /* Set statement timeout running, if any */ > if (StatementTimeout > 0) > *************** > *** 2017,2035 **** > else > cancel_from_timeout = false; > > ! xact_started = true; > } > } > > static void > ! finish_xact_command(void) > { > ! if (xact_started) > { > /* Cancel any active statement timeout before committing */ > disable_sig_alarm(true); > > ! /* Now commit the command */ > ereport(DEBUG3, > (errmsg_internal("CommitTransactionCommand"))); > > --- 2023,2070 ---- > else > cancel_from_timeout = false; > > ! command_initialized = true; > } > + start_xact_command(); > } > > static void > ! finalize_command(void) > { > ! if (command_initialized) > { > /* Cancel any active statement timeout before committing */ > disable_sig_alarm(true); > > ! command_initialized = false; > ! } > ! finish_xact_command(); > ! } > ! > ! > ! /* > ! * Check if the newly-arrived query string needs to have an implicit > ! * transaction started. > ! */ > ! static void > ! start_xact_command(void) > ! { > ! if (!xact_started) > ! { > ! ereport(DEBUG3, > ! (errmsg_internal("StartTransactionCommand"))); > ! > ! StartTransactionCommand(); > ! > ! xact_started = true; > ! } > ! } > ! > ! static void > ! finish_xact_command(void) > ! { > ! if (xact_started) > ! { > ereport(DEBUG3, > (errmsg_internal("CommitTransactionCommand"))); > > *************** > *** 3137,3143 **** > > /* We don't have a transaction command open anymore */ > xact_started = false; > ! > /* Now we can allow interrupts again */ > RESUME_INTERRUPTS(); > } > --- 3172,3179 ---- > > /* We don't have a transaction command open anymore */ > xact_started = false; > ! command_initialized = false; > ! > /* Now we can allow interrupts again */ > RESUME_INTERRUPTS(); > } > *************** > *** 3305,3311 **** > pgstat_report_activity("<FASTPATH> function call"); > > /* start an xact for this function invocation */ > ! start_xact_command(); > > /* switch back to message context */ > MemoryContextSwitchTo(MessageContext); > --- 3341,3347 ---- > pgstat_report_activity("<FASTPATH> function call"); > > /* start an xact for this function invocation */ > ! initialize_command(); > > /* switch back to message context */ > MemoryContextSwitchTo(MessageContext); > *************** > *** 3328,3334 **** > } > > /* commit the function-invocation transaction */ > ! finish_xact_command(); > > send_ready_for_query = true; > break; > --- 3364,3370 ---- > } > > /* commit the function-invocation transaction */ > ! finalize_command(); > > send_ready_for_query = true; > break; > *************** > *** 3416,3422 **** > > case 'S': /* sync */ > pq_getmsgend(&input_message); > ! finish_xact_command(); > send_ready_for_query = true; > break; > > --- 3452,3458 ---- > > case 'S': /* sync */ > pq_getmsgend(&input_message); > ! finalize_command(); > send_ready_for_query = true; > break; > > 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 24 Apr 2006 02:44:17 -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 24 Apr 2006 02:44:18 -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 24 Apr 2006 02:44:25 -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 24 Apr 2006 02:44:26 -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 5: don't forget to increase your free space map settings -- Bruce Momjian http://candle.pha.pa.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +