Re: [SQL] CURRENT_TIMESTAMP

Josh Berkus wrote:
>
> Tom,
>
> > I'd be happier with the whole thing if anyone had exhibited a convincing
> > use-case for statement timestamp. So far I've not seen any actual
> > examples of situations that are not better served by either transaction
> > timestamp or true current time. And the spec is perfectly clear that
> > CURRENT_TIMESTAMP does not mean true current time...
>
> Are we still planning on putting the three different versions of now() on the
> TODO? I.e.,
> now('transaction'),
> now('statement'), and
> now('immediate')
> With now() = now('transaction')?
>
> I still think it's a good idea, provided that we have some easy means to
> determine now('statement').

I did a little more research on CURRENT_TIMESTAMP. I read the Oracle
docs, and while they mention it, they don't say if the date is xact,
statement, or timeofday. They do mention it was only added in their
newest product, 9.X, so it isn't surpising no one is using it.

I also researched the SQL99 standards and found a much more specific
definition:

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.

They basically seem to be saying that CURRENT_TIMESTAMP has to be the
same for all triggers as it is for the submitted SQL statement. When
they say "the time of evaluation ... is implementation-dependent" they
mean that is can be the beginning of the statement, or the end of the
statement. In fact, you can make a strong argument that it should be
the statement end time that is the proper time, but for implementation
reasons, it is certainly easier to make it start.

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. They say statement, so I think we need
to follow that if we want to be standard-compliant. We already have two
other databases who are doing this timing at statement level.

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.

--
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