Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP
	id A4EBC4765DE; Mon, 30 Sep 2002 14:50:46 -0400 (EDT)
Received: from candle.pha.pa.us (momjian.navpoint.com [207.106.42.251])
	by postgresql.org (Postfix) with ESMTP
	id 3851E47659E; Mon, 30 Sep 2002 14:50:44 -0400 (EDT)
Received: (from pgman@localhost)
	by candle.pha.pa.us (8.11.6/8.10.1) id g8UInoE16701;
	Mon, 30 Sep 2002 14:49:50 -0400 (EDT)
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <200209301849.g8UInoE16701@candle.pha.pa.us>
Subject: Re: [SQL] CURRENT_TIMESTAMP
In-Reply-To: <23265.1033408740@sss.pgh.pa.us>
To: Tom Lane <tgl@sss.pgh.pa.us>
Date: Mon, 30 Sep 2002 14:49:50 -0400 (EDT)
Cc: josh@agliodbs.com, Martijn van Oosterhout <kleptog@svana.org>,
	Manfred Koizar <mkoi-pg@aon.at>,
	Roland Roberts <roland@astrofoto.org>, pgsql-sql@postgresql.org,
	pgsql-general@postgresql.org
X-Mailer: ELM [version 2.4ME+ PL99 (25)]
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Type: text/plain; charset=US-ASCII
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200209/1411
X-Sequence-Number: 30992

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