Re: CURRENT_TIMESTAMP not work correctly insinde a transaction.

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> Thomas Lockhart writes:
>> This is not a bug, but the behavior required by SQL9x afaicr.

> AFAIK, this is PostgreSQL bugward compatibility. SQL says that
> current_timestamp returns the "current timestamp", which you can interpret
> any way you want to.

Not entirely. The SQL92 spec says (sec. 6.8):

3) If an SQL-statement generally contains more than one reference
to one or more <datetime value function>s, then all such ref-
erences are effectively evaluated simultaneously. The time of
evaluation of the <datetime value function> during the execution
of the SQL-statement is implementation-dependent.

This clearly requires all current_timestamp calls within a single query
to return the same result. We extend that to be the same result across
a whole transaction. I consider that to be legal given the
"implementation-dependent" stipulation of the spec, and also to be quite
useful since you can reliably insert the same timestamp into multiple
rows in different tables.

If you happen to want true realtime rather than a transaction start
time, you can get it from timeofday(). But current_timestamp is not
supposed to give true realtime.

I'm not quite sure what the usefulness would be of start-of-statement
timestamps as opposed to start-of-transaction timestamps, which'd be
the other plausible way of conforming to the spec. (For one thing,
it'd be less than clear what to do with SQL statements executed inside
functions: is there an "inner statement" current_timestamp that's
different from the one prevailing outside the function call? Ugh.)

regards, tom lane