Re: [SQL] CURRENT_TIMESTAMP

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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(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