Re: [SQL] CURRENT_TIMESTAMP

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > Now, they are _not_ saying the statement can't have the same time as
> > other statements in the transaction, but I don't see why they would
> > explicitly have to state that.
>
> Allow me to turn that around: given that they clearly do NOT state that,
> how can you argue that "the spec requires it"? AFAICS the spec does not
> require it. In most places they are considerably more explicit than
> this about stating what is required.

I just looked at the SQL99 spec again:

3) Let S be an <SQL procedure statement> that is not generally
contained in a <triggered action>. All <datetime value
function>s that are generally contained, without an intervening
<routine invocation> whose subject routines do not include an
SQL function, in <value expression>s that are contained 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.

Notice the part I highlighted. The time returned is
implementation-dependent "during the execution of S". Now, if we do:

BEGIN;
SELECT CURRENT_TIMESTAMP;
SELECT CURRENT_TIMESTAMP;

the time currently returned for the second query is _not_ during the
duration of S (S being an SQL procedure statement) so I don't see how we
can be viewed as spec-compliant.

> > We already have two other databases who are doing this timing at
> > statement level.
>
> The behavior of CURRENT_TIMESTAMP is clearly stated by the spec to be
> implementation-dependent. We are under no compulsion to follow any
> specific other implementation. If we were going to follow some other
> lead, I'd look to Oracle first...

Only "implementation-dependent" during the execution of the statement.
We can't just return the session start time or 1970-01-01 for every
invocation of CURRENT_TIMESTAMP.

> > If we change CURRENT_TIMESTAMP to statement time, I don't think we need
> > now(""), but if we don't change it, I think we do --- somehow we should
> > allow users to access statement time.
>
> I have no problem with providing a function to access statement time,
> and now('something') seems a reasonable spelling of that function.
> But I think the argument that we should change our historical behavior
> of CURRENT_TIMESTAMP is very weak.

Hard to see how it is "very weak". What do you base that on?
Everything I have seen looks pretty strong that we are wrong in our
current implementation.

> One reason why I have a problem with the notion that the spec requires
> CURRENT_TIMESTAMP to mean "time of arrival of the current interactive
> command" (which is the only specific definition I've seen mentioned
> here) is that the spec does not truly have a notion of interactive
> command to begin with. AFAICT the spec's model of command execution
> is ecpg-like: you have commands embedded in a calling language with
> all sorts of opportunities for pre-planning, pre-execution, etc.
> The notion of command arrival time is extremely fuzzy in this model.
> It could very well be the time you compiled the ecpg application, or
> the time you started the application running.

The spec says "during the execution of S" so that is what I think we
have to follow.

Hopefully we will get an Oracle 9 tester soon.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073