Skip site navigation (1) Skip section navigation (2)

Additional current timestamp values

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: Brendan Jurd <direvus(at)gmail(dot)com>
Cc: pgsql-patches(at)postgresql(dot)org
Subject: Additional current timestamp values
Date: 2006-03-20 22:34:24
Message-ID: 200603202234.k2KMYOM04077@candle.pha.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-patches
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. +

In response to

Responses

pgsql-hackers by date

Next:From: Peter EisentrautDate: 2006-03-20 23:00:06
Subject: Re: Additional current timestamp values
Previous:From: Josh BerkusDate: 2006-03-20 18:10:45
Subject: Re: [HACKERS] PostgreSQL Anniversary Proposals --Important

pgsql-patches by date

Next:From: Peter EisentrautDate: 2006-03-20 23:00:06
Subject: Re: Additional current timestamp values
Previous:From: Neil ConwayDate: 2006-03-20 20:51:20
Subject: Re: Removal of backward-compatibility docs mentions

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group