From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <200209301620.g8UGKsq24305@candle.pha.pa.us>
Subject: Re: [SQL] CURRENT_TIMESTAMP
In-Reply-To: <14905.1033360594@sss.pgh.pa.us>
To: Tom Lane <tgl@sss.pgh.pa.us>
Date: Mon, 30 Sep 2002 12:20:54 -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
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Content-Type: text/plain; charset=US-ASCII

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