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

Brendan Jurd wrote:

Hi all,

I propose to add an internal function gettime() that transparently
returns the current system time, as a timestamptz with maximum
precision.

Calling gettime() would be a more elegant approach than calling
timeofday() and converting it to a timestamp, and avoids some of the
potential problems in that conversion (such as "Sat" being
misinterpreted as an Australian timezone).

I'm open to alternate suggestions for the name of the function.

If there are no objections, I'll start cooking up a patch right away.

We already have a TODO for this:

* 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.

-- 
  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

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

We already have a TODO for this:

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

I like the idea of having a function for statement start time. I
think I'll incorporate it into my patch.

The suggested naming convention in the TODO is good as well. I'd be
inclined to make those *_timestamp() functions the core functions,
and then make things like now() and gettime() shorthand equivalents.

On 8/14/05, Bruce Momjian <pgman@candle.pha.pa.us> wrote:

Brendan Jurd wrote:

We already have a TODO for this:

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

I like the idea of having a function for statement start time. I
think I'll incorporate it into my patch.

Regarding the statement_timestamp() ... if the entire query path is
parser -> rewriter -> planner/optimiser -> executor, what point in
that path would be considered the true start of the "statement"? I
would assume that it's right at the beginning, when it is first handed
to the parser, but that's a very naive assumption.

On Sun, Aug 14, 2005 at 05:51:16AM +1000, Brendan Jurd wrote:

Regarding the statement_timestamp() ... if the entire query path is
parser -> rewriter -> planner/optimiser -> executor, what point in
that path would be considered the true start of the "statement"? I
would assume that it's right at the beginning, when it is first handed
to the parser, but that's a very naive assumption.

The latest time before any index or table has been accessed to derive
results?

For prepared statements, for example, the time the statement was prepared
doesn't seem useful to me.

Cheers,
mark

--
mark@mielke.cc / markm@ncf.ca / markm@nortel.com __________________________
. . _ ._ . . .__ . . ._. .__ . . . .__ | Neighbourhood Coder
|\/| |_| |_| |/ |_ |\/| | |_ | |/ |_ |
| | | | | \ | \ |__ . | | .|. |__ |__ | \ |__ | Ottawa, Ontario, Canada

One ring to rule them all, one ring to find them, one ring to bring them all
and in the darkness bind them...

http://mark.mielke.cc/

Brendan Jurd <direvus@gmail.com> writes:

Regarding the statement_timestamp() ... if the entire query path is
parser -> rewriter -> planner/optimiser -> executor, what point in
that path would be considered the true start of the "statement"?

IIRC, what we actually intended that to mean is the time of receipt of
the current interactive command --- that is, it gets set in the
postgres.c outer loop, not anywhere in the parser/etc path. Otherwise
there's not a unique answer (consider statements issued inside SQL
functions for instance).

regards, tom lane

On Sat, Aug 13, 2005 at 06:24:01PM -0400, Tom Lane wrote:

Brendan Jurd <direvus@gmail.com> writes:

Regarding the statement_timestamp() ... if the entire query path is
parser -> rewriter -> planner/optimiser -> executor, what point in
that path would be considered the true start of the "statement"?

IIRC, what we actually intended that to mean is the time of receipt of
the current interactive command --- that is, it gets set in the
postgres.c outer loop, not anywhere in the parser/etc path. Otherwise
there's not a unique answer (consider statements issued inside SQL
functions for instance).

ISTM that it would be useful to be able to use timestamp_statement
within a function though... although I guess timestamp_clock might
suffice in most cases. Another consideration is that this is a potential
source of confusion; people could easily think that timestamp_statement
would operate the same inside a function as it would outside.

Would it be reasonable to add one more timestamp that works the same
inside and outside a function? In either case, can anyone think of a
less-ambiguous name for timestamp_statement?
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com 512-569-9461

Jim C. Nasby wrote:

On Sat, Aug 13, 2005 at 06:24:01PM -0400, Tom Lane wrote:

Brendan Jurd <direvus@gmail.com> writes:

Regarding the statement_timestamp() ... if the entire query path is
parser -> rewriter -> planner/optimiser -> executor, what point in
that path would be considered the true start of the "statement"?

IIRC, what we actually intended that to mean is the time of receipt of
the current interactive command --- that is, it gets set in the
postgres.c outer loop, not anywhere in the parser/etc path. Otherwise
there's not a unique answer (consider statements issued inside SQL
functions for instance).

ISTM that it would be useful to be able to use timestamp_statement
within a function though... although I guess timestamp_clock might
suffice in most cases. Another consideration is that this is a potential
source of confusion; people could easily think that timestamp_statement
would operate the same inside a function as it would outside.

Would it be reasonable to add one more timestamp that works the same
inside and outside a function? In either case, can anyone think of a
less-ambiguous name for timestamp_statement?

timestamp_client_statement? That highlights it is when the client sends
the statement.

-- 
  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

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Jim C. Nasby wrote:

On Sat, Aug 13, 2005 at 06:24:01PM -0400, Tom Lane wrote:

IIRC, what we actually intended that to mean is the time of receipt of
the current interactive command --- that is, it gets set in the
postgres.c outer loop, not anywhere in the parser/etc path. Otherwise
there's not a unique answer (consider statements issued inside SQL
functions for instance).

Would it be reasonable to add one more timestamp that works the same
inside and outside a function? In either case, can anyone think of a
less-ambiguous name for timestamp_statement?

timestamp_client_statement? That highlights it is when the client sends
the statement.

timestamp_command, maybe, would convey the right image.

(I don't think we need yet a fourth flavor of this, nor do I see anything
about it that "works differently inside and outside a function".)

regards, tom lane

From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Bruce Momjian <pgman@candle.pha.pa.us> writes:

Jim C. Nasby wrote:

On Sat, Aug 13, 2005 at 06:24:01PM -0400, Tom Lane wrote:

IIRC, what we actually intended that to mean is the time

of receipt of

the current interactive command --- that is, it gets set in the
postgres.c outer loop, not anywhere in the parser/etc

path. Otherwise

there's not a unique answer (consider statements issued inside SQL
functions for instance).

Would it be reasonable to add one more timestamp that

works the same

inside and outside a function? In either case, can anyone

think of a

less-ambiguous name for timestamp_statement?

timestamp_client_statement? That highlights it is when the

client sends

the statement.

timestamp_command, maybe, would convey the right image.

(I don't think we need yet a fourth flavor of this, nor do I
see anything
about it that "works differently inside and outside a function".)

Here's what I thought was going to happen:

psql> BEGIN; -- sets timestamp_transaction();
psql> SELECT func1(); -- sets timestamp_statement()
func1:
SELECT something; -- doesn't set timestamp_statement(), because it's in a function
call func2();
return;
psql> SELECT something; -- sets timestamp_statement() again

Maybe I just mis-understood and each statement that's issued will update timestamp_statement(). If that's the case I think we're fine (I can't really think of a use-case for timestamp_command() myself...).

OTOH, if the intention is to do what I outlined in the above timeline, I think we should also have timestamp_command(), and change timestamp_statement() so that it always indicates the timestamp at the start of the current statement.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com 512-569-9461

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. +

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