Additional current timestamp values

I hBrendan Jurd wrote:
> On 8/7/05, Brendan Jurd <direvus(at)gmail(dot)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. +