Re: [SQL] Monitoring a Query

On Mon, 23 Sep 2002 11:06:19 -0400 (EDT), Bruce Momjian
<pgman(at)candle(dot)pha(dot)pa(dot)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