Thread: 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@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
Bruce Momjian <pgman@candle.pha.pa.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.
> 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...
> 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.
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.
regards, tom lane
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.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@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> 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)
Not so fast. What is an "SQL procedure statement"?
Our interactive commands do not map real well to the spec's definitions.
Consider for example SQL92 section 4.17:
4.17 Procedures
A <procedure> consists of a <procedure name>, a sequence of <pa-
rameter declaration>s, and a single <SQL procedure statement>.
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
A <procedure> in a <module> is invoked by a compilation unit as-
sociated with the <module> by means of a host language "call"
statement that specifies the <procedure name> of the <procedure>
and supplies a sequence of parameter values corresponding in number
and in <data type> to the <parameter declaration>s of the <proce-
dure>. A call of a <procedure> causes the <SQL procedure statement>
that it contains to be executed.
The only thing you can easily map this onto in Postgres is stored
functions; your reading would then say that each Postgres function call
requires its own evaluation of current_timestamp, which I think we are
all agreed would be a disastrous interpretation.
It would be pretty easy to make the case that an ECPG module represents
a "procedure" in the spec's meaning, in which case it is *necessary* for
spec compliance that the ECPG module be able to execute all its commands
with the same value of current_timestamp. This would look like a series
of interactive commands to the backend.
So I do not think that the spec provides clear support for your position.
The only thing that is really clear is that there is a minimum unit
of execution in which current_timestamp is not supposed to change.
It does not clearly define any maximum unit; and it is even less clear
that our interactive commands should be equated to "SQL procedure
statement".
regards, tom lane
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > 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)
>
> Not so fast. What is an "SQL procedure statement"?
>
> Our interactive commands do not map real well to the spec's definitions.
> Consider for example SQL92 section 4.17:
>
> 4.17 Procedures
>
> A <procedure> consists of a <procedure name>, a sequence of <pa-
> rameter declaration>s, and a single <SQL procedure statement>.
> ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
> A <procedure> in a <module> is invoked by a compilation unit as-
> sociated with the <module> by means of a host language "call"
> statement that specifies the <procedure name> of the <procedure>
> and supplies a sequence of parameter values corresponding in number
> and in <data type> to the <parameter declaration>s of the <proce-
> dure>. A call of a <procedure> causes the <SQL procedure statement>
> that it contains to be executed.
>
> The only thing you can easily map this onto in Postgres is stored
> functions; your reading would then say that each Postgres function call
> requires its own evaluation of current_timestamp, which I think we are
> all agreed would be a disastrous interpretation.
>
> It would be pretty easy to make the case that an ECPG module represents
> a "procedure" in the spec's meaning, in which case it is *necessary* for
> spec compliance that the ECPG module be able to execute all its commands
> with the same value of current_timestamp. This would look like a series
> of interactive commands to the backend.
>
> So I do not think that the spec provides clear support for your position.
> The only thing that is really clear is that there is a minimum unit
> of execution in which current_timestamp is not supposed to change.
> It does not clearly define any maximum unit; and it is even less clear
> that our interactive commands should be equated to "SQL procedure
> statement".
OK, you don't like "SQL procedure statement". Let's look at SQL92:
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.
^^^^^^^^^^^^^^^^^^^^
so, again, we have wording that is has to be "during" the SQL statement.
Also, we have MSSQL, Interbase, and now Oracle modifying
CURRENT_TIMESTAMP during the transaction. (The Oracle report just came
in a few hours ago.)
Perhaps we need a vote on this.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Also, we have MSSQL, Interbase, and now Oracle modifying
> CURRENT_TIMESTAMP during the transaction. (The Oracle report just came
> in a few hours ago.)
Weren't you dissatisfied with the specificity of that Oracle report?
> Perhaps we need a vote on this.
Perhaps, but let's wait till the facts are in.
regards, tom lane
Bruce,
> 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'd argue that we need the 3 kinds of now() regardless, just to limit user
confusion. If we set things up as:
now() = transaction time
current_timestamp = statement time
timeofday() = exact time
That does give users access to all 3 timestamps, but using a competely
non-intuitive nomenclature. It's likely that the three types of now() would
just be pointers to other time functions, but would provide nomenative
clarity.
--
-Josh Berkus
Aglio Database Solutions
San Francisco