Re: [SQL] CURRENT_TIMESTAMP

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)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".

regards, tom lane