To: Bruce Momjian <pgman@candle.pha.pa.us>
Cc: josh@agliodbs.com, Manfred Koizar <mkoi-pg@aon.at>,
	Aaron Held <aaron@MetroNY.com>, Roberto Mello <rmello@cc.usu.edu>,
	Neil Conway <neilc@samurai.com>, pgsql-sql@postgresql.org
Subject: Re: [GENERAL] CURRENT_TIMESTAMP 
In-reply-to: <200209232041.g8NKfix19001@candle.pha.pa.us> 
References: <200209232041.g8NKfix19001@candle.pha.pa.us>
Comments: In-reply-to Bruce Momjian <pgman@candle.pha.pa.us>
	message dated "Mon, 23 Sep 2002 16:41:44 -0400"
Date: Mon, 23 Sep 2002 16:55:48 -0400
Message-ID: <4619.1032814548@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I don't see how we can defend returning the start of the transaction as
> the current_timestamp.

Here's an example:

CREATE RULE foo AS ON INSERT TO mytable DO
( INSERT INTO log1 VALUES (... , now(), ...);
  INSERT INTO log2 VALUES (... , now(), ...) );

I think it's important that these commands store the same timestamp in
both log tables (not to mention that any now() being stored into mytable
itself generate that same timestamp).

If you scale that up just a little bit, you can devise scenarios where
successive client-issued commands (within a single transaction) want to
store the same timestamp.  After all, it's only a minor implementation
detail that you chose to fire these logging operations via a rule and
not by client-side logic.

In short, there are plenty of situations where it's critical for
application correctness that a series of commands all be able to operate
with the same value of now().  I don't think that it's wise for Postgres
to try to decide where within a transaction it's safe to advance now().
That will inevitably break some applications, and it's not obvious what
the benefit is.

In short: if you want exact current time, there's timeofday().  If you
want start of transaction time, we've got that.  If you want start of
current statement time, I have two questions: why, and exactly how do
you want to define current statement, considering functions, rules,
triggers, and all that other stuff that makes it interesting?

ISTM that if a client or function wants to record intratransaction
times, it can call timeofday() at the appropriate points for itself.

			regards, tom lane