Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id A5B284761EE
	for <pgsql-general@postgresql.org>;
	Mon, 23 Sep 2002 12:59:14 -0400 (EDT)
Received: from email01.aon.at (WARSL402PIP3.highway.telekom.at [195.3.96.97])
	by postgresql.org (Postfix) with SMTP id 37CE34760AF
	for <pgsql-general@postgresql.org>;
	Mon, 23 Sep 2002 12:59:13 -0400 (EDT)
Received: (qmail 171270 invoked from network); 23 Sep 2002 16:59:11 -0000
Received: from m160p019.dipool.highway.telekom.at (HELO cantor)
	([62.46.9.243]) (envelope-sender <mkoi-pg@aon.at>)
	by qmail1rs.highway.telekom.at (qmail-ldap-1.03) with SMTP
	for <pgman@candle.pha.pa.us>; 23 Sep 2002 16:59:11 -0000
From: Manfred Koizar <mkoi-pg@aon.at>
To: Bruce Momjian <pgman@candle.pha.pa.us>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, Aaron Held <aaron@MetroNY.com>,
	Roberto Mello <rmello@cc.usu.edu>, Neil Conway <neilc@samurai.com>,
	pgsql-sql@postgresql.org, pgsql-general@postgresql.org
Subject: Re: [SQL] Monitoring a Query
Date: Mon, 23 Sep 2002 19:01:01 +0200
Message-ID: <0meuousc373fdokv3bbke116jsbkpakno2@4ax.com>
References: <21724.1032793386@sss.pgh.pa.us>
	<200209231506.g8NF6JP13779@candle.pha.pa.us>
In-Reply-To: <200209231506.g8NF6JP13779@candle.pha.pa.us>
X-Mailer: Forte Agent 1.8/32.548
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200209/1007
X-Sequence-Number: 30588

On Mon, 23 Sep 2002 11:06:19 -0400 (EDT), Bruce Momjian
<pgman@candle.pha.pa.us> wrote:
>Tom Lane wrote:
>> It would be nearly free to include the start time of the current
>> transaction, because we already save that for use by now().  Is
>> that good enough, or do we need start time of the current query?
>
>Current query, I am afraid.  We could optimize it so single-query
>transactions wouldn't need to call that again.

This has been discussed before and I know I'm going to get flamed for
this, but IMHO having now() (which is a synonym for CURRENT_TIMESTAMP)
return the start time of the current transaction is a bug, or at least
it is not conforming to the standard.

SQL92 says in 6.8  <datetime value function>:

  General Rules

  1) The <datetime value function>s CURRENT_DATE, CURRENT_TIME, and
     CURRENT_TIMESTAMP respectively return the current date, current
     time, and current timestamp [...]
               ^^^^^^^

  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.

SQL99 says in 6.19  <datetime value function>:

  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.

I cannot say that I fully understand the second sentence (guess I have
to read it for another 100 times), but "during the execution of S"
seems to mean "not before the start and not after the end of S".

What do you think?

Servus
 Manfred