Re: [GENERAL] CURRENT_TIMESTAMP - Mailing list pgsql-sql

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