Re: (Fwd) Re: Any Oracle 9 users? A test please...

On Tue, 2002-10-01 at 01:10, Bruce Momjian wrote:
>
> > Given what Tom has posted regarding the standard, I think Oracle
> > is wrong. I'm wondering how the others handle multiple
> > references in CURRENT_TIMESTAMP in a single stored
> > procedure/function invocation. It seems to me that the lower
> > bound is #4, not #5, and the upper bound is implementation
> > dependent. Therefore PostgreSQL is in compliance, but its
> > compliance is not very popular.
>
> I don't see how we can be compliant if SQL92 says:
>
> The time of evaluation of the <datetime value function> during the
> execution of the SQL-statement is implementation-dependent.
>
> It says it has to be "during the SQL statement", or is SQL statement
> also ambiguous?

It can be, as "during the SQL statement" can mean either the single
statement inside the PL/SQL function (SELECT CURRENT_TIMESTAMP INTO
time1 FROM DUAL;) or the whole invocation of the Pl/SQL funtion (the /
command in Mikes sample, i believe)

--------------
Hannu

It is not clear to me; is this its own transaction or a function call?

---------------------------------------------------------------------------

Dan Langille wrote:
> And just for another opinion, which supports the first.
>
> >From now, unless you indicate otherwise, I'll only report tests which
> have both values the same.
>
> From: "Shawn O'Connor" <soconnor(at)mail(dot)e-perception(dot)com>
> To: Dan Langille <dan(at)langille(dot)org>
> Subject: Re: Any Oracle 9 users? A test please...
> In-Reply-To: <3D985663(dot)24174(dot)80554E83(at)localhost>
> Message-ID: <20020930114241(dot)E45374-100000(at)mail(dot)e-perception(dot)com>
> MIME-Version: 1.0
> Content-Type: TEXT/PLAIN; charset=US-ASCII
> X-PMFLAGS: 35127424 0 1 P2A7A0.CNM
>
> Okay, here you are:
> ----------------------------------
>
> DECLARE
> time1 TIMESTAMP;
> time2 TIMESTAMP;
> sleeptime NUMBER;
> BEGIN
> sleeptime := 5;
> SELECT CURRENT_TIMESTAMP INTO time1 FROM DUAL;
> DBMS_LOCK.SLEEP(sleeptime);
> SELECT CURRENT_TIMESTAMP INTO time2 FROM DUAL;
> DBMS_OUTPUT.PUT_LINE(TO_CHAR(time1));
> DBMS_OUTPUT.PUT_LINE(TO_CHAR(time2));
> END;
> /
> 30-SEP-02 11.54.09.583576 AM
> 30-SEP-02 11.54.14.708333 AM
>
> PL/SQL procedure successfully completed.
>
> ----------------------------------
>
> Hope this helps!
>
> -Shawn
>
>
> On Mon, 30 Sep 2002, Dan Langille wrote:
>
> > We're testing this just to see what Oracle does. What you are
> > saying is what we expect to happen. But could you do that test for
> > us from the command line? Thanks.
> >
> > On 30 Sep 2002 at 10:31, Shawn O'Connor wrote:
> >
> > > I'm assuming your doing this as some sort of anonymous
> > > PL/SQL function:
> > >
> > > Don't you need to do something like:
> > >
> > > SELECT CURRENT_TIMESTAMP FROM DUAL INTO somevariable?
> > >
> > > and to wait five seconds probably:
> > >
> > > EXECUTE DBMS_LOCK.SLEEP(5);
> > >
> > > But to answer your question-- When this PL/SQL function
> > > is run the values of current_timestamp are not the same, they will
> > > be sepearated by five seconds or so.
> > >
> > > Hope this helps!
> > >
> > > -Shawn
> > >
> > > On Mon, 30 Sep 2002, Dan Langille wrote:
> > >
> > > > Followups to freebsd-database(at)freebsd(dot)org please!
> > > >
> > > > Any Oracle 9 users out there?
> > > >
> > > > I need this run:
> > > >
> > > > BEGIN;
> > > > SELECT CURRENT_TIMESTAMP;
> > > > -- wait 5 seconds
> > > > SELECT CURRENT_TIMESTAMP;
> > > >
> > > > Are those two timestamps the same?
> > > >
> > > > Thanks
> > > > --
> > > > Dan Langille
> > > > I'm looking for a computer job:
> > > > http://www.freebsddiary.org/dan_langille.php
> > > >
> > > >
> > > > To Unsubscribe: send mail to majordomo(at)FreeBSD(dot)org
> > > > with "unsubscribe freebsd-database" in the body of the message
> > > >
> > >
> > >
> >
> >
> > --
> > Dan Langille
> > I'm looking for a computer job:
> > http://www.freebsddiary.org/dan_langille.php
> >
>
>
> ------- End of forwarded message -------
> --
> Dan Langille
> I'm looking for a computer job:
> http://www.freebsddiary.org/dan_langille.php
>
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian wrote:
> It is not clear to me; is this its own transaction or a function call?
>

That looks like an anonymous PL/SQL procedure to me. Another
question might be, given:

"more than one reference to one or more <datetime value
function>s, then all such references are effectively evaluated
simultaneously"

under what conditions does Oracle report *the same* value for
CURRENT_TIMESTAMP? So far, in this discussion, we have the
following scenarios:

1. RDBMS start: No one
2. Session start: No one
3. Transaction start: PostgreSQL
4. Statement start: ???
5. CURRENT_TIMESTAMP evaluation: Oracle 9, ???

Given what Tom has posted regarding the standard, I think Oracle
is wrong. I'm wondering how the others handle multiple
references in CURRENT_TIMESTAMP in a single stored
procedure/function invocation. It seems to me that the lower
bound is #4, not #5, and the upper bound is implementation
dependent. Therefore PostgreSQL is in compliance, but its
compliance is not very popular.

Mike Mascari
mascarm(at)mascari(dot)com

> Dan Langille wrote:
>>
>>
>>DECLARE
>> time1 TIMESTAMP;
>> time2 TIMESTAMP;
>> sleeptime NUMBER;
>>BEGIN
>> sleeptime := 5;
>> SELECT CURRENT_TIMESTAMP INTO time1 FROM DUAL;
>> DBMS_LOCK.SLEEP(sleeptime);
>> SELECT CURRENT_TIMESTAMP INTO time2 FROM DUAL;
>> DBMS_OUTPUT.PUT_LINE(TO_CHAR(time1));
>> DBMS_OUTPUT.PUT_LINE(TO_CHAR(time2));
>>END;
>>/
>>30-SEP-02 11.54.09.583576 AM
>>30-SEP-02 11.54.14.708333 AM
>>
>>PL/SQL procedure successfully completed.

I am starting to see Tom's issue here. If you have a PL/pgSQL function
that does:

> >>DECLARE

> >>BEGIN
> >> SELECT CURRENT_TIMESTAMP INTO time1 FROM DUAL;

> >> SELECT CURRENT_TIMESTAMP INTO time2 FROM DUAL;
> >>END;

You would want those two to be the same because they are in the same
function, but by looking at it, they look the same as interactive
queries. In a sense if we change CURRENT_TIMESTAMP, we are scoping the
variable to match the users/client's perspective.

However, we have added statement_timeout, so it does seem we have had to
move to a more user-centered perspective on some of these things. The
big question is whether a variable that would be inserted into the
database should have such scoping. I can see cases where people would
want that, and others where they wouldn't.

> 1. RDBMS start: No one
> 2. Session start: No one
> 3. Transaction start: PostgreSQL
> 4. Statement start: ???
> 5. CURRENT_TIMESTAMP evaluation: Oracle 9, ???

This is a nice chart. Oracle already has transaction start reported by
sysdate:

> SQL> begin
> 2 insert into rbr_foo select sysdate from dual;
> [...wait about 10 seconds...]
> 3 insert into rbr_foo select sysdate from dual;
> 4 end;
> 5 /
>
> PL/SQL procedure successfully completed.
>
> SQL> select * from rbr_foo;
>
> A
> ---------------------
> SEP 27, 2002 12:57:27
> SEP 27, 2002 12:57:27

so for CURRENT_TIMESTAMP it seems they have evaluation-time, while
MSSQL/Interbase have statement time.

> Given what Tom has posted regarding the standard, I think Oracle
> is wrong. I'm wondering how the others handle multiple
> references in CURRENT_TIMESTAMP in a single stored
> procedure/function invocation. It seems to me that the lower
> bound is #4, not #5, and the upper bound is implementation
> dependent. Therefore PostgreSQL is in compliance, but its
> compliance is not very popular.

I don't see how we can be compliant if SQL92 says:

The time of evaluation of the <datetime value function> during the
execution of the SQL-statement is implementation-dependent.

It says it has to be "during the SQL statement", or is SQL statement
also ambiguous? Is that why Oracle did what they did?

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Hannu Krosing wrote:
> On Tue, 2002-10-01 at 01:10, Bruce Momjian wrote:
> >
> > > Given what Tom has posted regarding the standard, I think Oracle
> > > is wrong. I'm wondering how the others handle multiple
> > > references in CURRENT_TIMESTAMP in a single stored
> > > procedure/function invocation. It seems to me that the lower
> > > bound is #4, not #5, and the upper bound is implementation
> > > dependent. Therefore PostgreSQL is in compliance, but its
> > > compliance is not very popular.
> >
> > I don't see how we can be compliant if SQL92 says:
> >
> > The time of evaluation of the <datetime value function> during the
> > execution of the SQL-statement is implementation-dependent.
> >
> > It says it has to be "during the SQL statement", or is SQL statement
> > also ambiguous?
>
> It can be, as "during the SQL statement" can mean either the single
> statement inside the PL/SQL function (SELECT CURRENT_TIMESTAMP INTO
> time1 FROM DUAL;) or the whole invocation of the Pl/SQL funtion (the /
> command in Mikes sample, i believe)

Which is what Oracle may have done. SQL99 talks about triggers seeing
the same date/time, but then again if your trigger is a function, it has
to see the same values for all of its calls. This doesn't match Oracle,
unless they have some switch that returns consistent values when the
function is called as a trigger (yuck).

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian wrote:
> Hannu Krosing wrote:
>
>>It can be, as "during the SQL statement" can mean either the single
>>statement inside the PL/SQL function (SELECT CURRENT_TIMESTAMP INTO
>>time1 FROM DUAL;) or the whole invocation of the Pl/SQL funtion (the /
>>command in Mikes sample, i believe)
>
>
> Which is what Oracle may have done. SQL99 talks about triggers seeing
> the same date/time, but then again if your trigger is a function, it has
> to see the same values for all of its calls. This doesn't match Oracle,
> unless they have some switch that returns consistent values when the
> function is called as a trigger (yuck).
>

I think there is a #6 level in that chart. For example:

INSERT INTO foo(field1, field2, field3)
SELECT CURRENT_TIMESTAMP, (some time-intensive subquery),
CURRENT_TIMESTAMP
FROM bar;

I'd bet Oracle inserts the same value for CURRENT_TIMESTAMP for
both fields for every row. And that is what they view as a "SQL
Statement". I've only got 8, so I can't test. Also, as you point
out, Oracle may distinguish between PL/SQL created anonymously
or with CREATE PROCEDURE vs. PL/SQL code created with CREATE
FUNCTION. It may be that UDFs return a single CURRENT_TIMESTAMP
for the life of the invocation, while stored procedures don't.
It is PostgreSQL, after all, that has merged the two concepts
into one.

Maybe someone could test version 9 with a FUNCTION that executes
the same PL/SQL code and returns the difference between the two
times.

Mike Mascari
mascarm(at)mascari(dot)com

On Mon, 30 Sep 2002 15:29:07 -0400, Mike Mascari <mascarm(at)mascari(dot)com>
wrote:
> I'm wondering how the others handle multiple
>references in CURRENT_TIMESTAMP in a single stored
>procedure/function invocation.

MSSQL 7 seems to evaluate CURRENT_TIMESTAMP for each statement,
Interbase 6 once per procedure call. Here are my test procedures:

MSSQL 7
create table tst (i integer, d datetime not null)
go
create procedure tstInsert
as begin
delete from tst
insert into tst(i, d) select count(*),CURRENT_TIMESTAMP
from tst a, tst b, tst c, tst d, tst e
insert into tst(i, d) select count(*),CURRENT_TIMESTAMP
from tst a, tst b, tst c, tst d, tst e
insert into tst(i, d) select count(*),CURRENT_TIMESTAMP
from tst a, tst b, tst c, tst d, tst e
insert into tst(i, d) select count(*),CURRENT_TIMESTAMP
from tst a, tst b, tst c, tst d, tst e
insert into tst(i, d) select count(*),CURRENT_TIMESTAMP
from tst a, tst b, tst c, tst d, tst e
insert into tst(i, d) select count(*),CURRENT_TIMESTAMP
from tst a, tst b, tst c, tst d, tst e
insert into tst(i, d) select count(*),CURRENT_TIMESTAMP
from tst a, tst b, tst c, tst d, tst e
insert into tst(i, d) select count(*),CURRENT_TIMESTAMP
from tst a, tst b, tst c, tst d, tst e
insert into tst(i, d) select count(*),CURRENT_TIMESTAMP
from tst a, tst b, tst c, tst d, tst e
insert into tst(i, d) select count(*),CURRENT_TIMESTAMP
from tst a, tst b, tst c, tst d, tst e
end
go
begin transaction
exec tstInsert
commit transaction
select * from tst
i d
----------- ---------------------------
0 2002-09-30 22:26:06.540
1 2002-09-30 22:26:06.540
32 2002-09-30 22:26:06.540
243 2002-09-30 22:26:06.540
1024 2002-09-30 22:26:06.550
3125 2002-09-30 22:26:06.550
7776 2002-09-30 22:26:06.550
16807 2002-09-30 22:26:06.560
32768 2002-09-30 22:26:06.570
59049 2002-09-30 22:26:06.590

(10 row(s) affected)

Interbase 6
SQL> create table tst(i integer, d timestamp);
SQL> commit;
SQL> set term !!;
SQL> create procedure tstInsert as begin
CON> delete from tst;
CON> insert into tst(i, d) select count(*),CURRENT_TIMESTAMP
CON> from tst a, tst b, tst c, tst d, tst e;
CON> insert into tst(i, d) select count(*),CURRENT_TIMESTAMP
CON> from tst a, tst b, tst c, tst d, tst e;
CON> insert into tst(i, d) select count(*),CURRENT_TIMESTAMP
CON> from tst a, tst b, tst c, tst d, tst e;
CON> insert into tst(i, d) select count(*),CURRENT_TIMESTAMP
CON> from tst a, tst b, tst c, tst d, tst e;
CON> insert into tst(i, d) select count(*),CURRENT_TIMESTAMP
CON> from tst a, tst b, tst c, tst d, tst e;
CON> insert into tst(i, d) select count(*),CURRENT_TIMESTAMP
CON> from tst a, tst b, tst c, tst d, tst e;
CON> insert into tst(i, d) select count(*),CURRENT_TIMESTAMP
CON> from tst a, tst b, tst c, tst d, tst e;
CON> insert into tst(i, d) select count(*),CURRENT_TIMESTAMP
CON> from tst a, tst b, tst c, tst d, tst e;
CON> insert into tst(i, d) select count(*),CURRENT_TIMESTAMP
CON> from tst a, tst b, tst c, tst d, tst e;
CON> insert into tst(i, d) select count(*),CURRENT_TIMESTAMP
CON> from tst a, tst b, tst c, tst d, tst e;
CON> end;
CON> !!

SQL> set term ; !!
SQL> commit;
SQL> execute procedure tstInsert; -- takes approx. 5 seconds.
SQL> select * from tst;

I D
============ =========================

0 1858-11-17 00:00:00.0000
1 2002-09-30 22:37:54.0000
32 2002-09-30 22:37:54.0000
243 2002-09-30 22:37:54.0000
1024 2002-09-30 22:37:54.0000
3125 2002-09-30 22:37:54.0000
7776 2002-09-30 22:37:54.0000
16807 2002-09-30 22:37:54.0000
32768 2002-09-30 22:37:54.0000
59049 2002-09-30 22:37:54.0000

SQL> commit;

BTW, it's interesting (but OT) how they handle

select count(*), current_timestamp, 1 from tst where 0=1;

differently.

MSSQL: 0 2002-09-30 22:53:55.920 1
Interbase: 0 1858-11-17 00:00:00.0000 0 <--- bug here?
Postgres: 0 2002-09-30 21:10:35.660781+02 1

Servus
Manfred

The original tester says "this is an anonymous procedure".

On 30 Sep 2002 at 15:07, Bruce Momjian wrote:

>
> It is not clear to me; is this its own transaction or a function
> call?
>
> ----------------------------------------------------------------------
> -----
>
> Dan Langille wrote:
> > And just for another opinion, which supports the first.
> >
> > >From now, unless you indicate otherwise, I'll only report tests
> > >which
> > have both values the same.
> >
> > From: "Shawn O'Connor" <soconnor(at)mail(dot)e-perception(dot)com>
> > To: Dan Langille <dan(at)langille(dot)org>
> > Subject: Re: Any Oracle 9 users? A test please...
> > In-Reply-To: <3D985663(dot)24174(dot)80554E83(at)localhost>
> > Message-ID: <20020930114241(dot)E45374-100000(at)mail(dot)e-perception(dot)com>
> > MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII
> > X-PMFLAGS: 35127424 0 1 P2A7A0.CNM
> >
> > Okay, here you are:
> > ----------------------------------
> >
> > DECLARE
> > time1 TIMESTAMP;
> > time2 TIMESTAMP;
> > sleeptime NUMBER;
> > BEGIN
> > sleeptime := 5;
> > SELECT CURRENT_TIMESTAMP INTO time1 FROM DUAL;
> > DBMS_LOCK.SLEEP(sleeptime);
> > SELECT CURRENT_TIMESTAMP INTO time2 FROM DUAL;
> > DBMS_OUTPUT.PUT_LINE(TO_CHAR(time1));
> > DBMS_OUTPUT.PUT_LINE(TO_CHAR(time2));
> > END;
> > /
> > 30-SEP-02 11.54.09.583576 AM
> > 30-SEP-02 11.54.14.708333 AM
> >
> > PL/SQL procedure successfully completed.
> >
> > ----------------------------------
> >
> > Hope this helps!
> >
> > -Shawn
> >
> >
> > On Mon, 30 Sep 2002, Dan Langille wrote:
> >
> > > We're testing this just to see what Oracle does. What you are
> > > saying is what we expect to happen. But could you do that test
> > > for us from the command line? Thanks.
> > >
> > > On 30 Sep 2002 at 10:31, Shawn O'Connor wrote:
> > >
> > > > I'm assuming your doing this as some sort of anonymous
> > > > PL/SQL function:
> > > >
> > > > Don't you need to do something like:
> > > >
> > > > SELECT CURRENT_TIMESTAMP FROM DUAL INTO somevariable?
> > > >
> > > > and to wait five seconds probably:
> > > >
> > > > EXECUTE DBMS_LOCK.SLEEP(5);
> > > >
> > > > But to answer your question-- When this PL/SQL function
> > > > is run the values of current_timestamp are not the same, they
> > > > will be sepearated by five seconds or so.
> > > >
> > > > Hope this helps!
> > > >
> > > > -Shawn
> > > >
> > > > On Mon, 30 Sep 2002, Dan Langille wrote:
> > > >
> > > > > Followups to freebsd-database(at)freebsd(dot)org please!
> > > > >
> > > > > Any Oracle 9 users out there?
> > > > >
> > > > > I need this run:
> > > > >
> > > > > BEGIN;
> > > > > SELECT CURRENT_TIMESTAMP;
> > > > > -- wait 5 seconds
> > > > > SELECT CURRENT_TIMESTAMP;
> > > > >
> > > > > Are those two timestamps the same?
> > > > >
> > > > > Thanks
> > > > > --
> > > > > Dan Langille
> > > > > I'm looking for a computer job:
> > > > > http://www.freebsddiary.org/dan_langille.php
> > > > >
> > > > >
> > > > > To Unsubscribe: send mail to majordomo(at)FreeBSD(dot)org
> > > > > with "unsubscribe freebsd-database" in the body of the message
> > > > >
> > > >
> > > >
> > >
> > >
> > > --
> > > Dan Langille
> > > I'm looking for a computer job:
> > > http://www.freebsddiary.org/dan_langille.php
> > >
> >
> >
> > ------- End of forwarded message -------
> > --
> > Dan Langille
> > I'm looking for a computer job:
> > http://www.freebsddiary.org/dan_langille.php
> >
> >
>
> --
> Bruce Momjian | http://candle.pha.pa.us
> pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001 + If your
> life is a hard drive, | 13 Roberts Road + Christ can be your
> backup. | Newtown Square, Pennsylvania 19073
>

--
Dan Langille
I'm looking for a computer job:
http://www.freebsddiary.org/dan_langille.php

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> I don't see how we can be compliant if SQL92 says:
> The time of evaluation of the <datetime value function> during the
> execution of the SQL-statement is implementation-dependent.
> It says it has to be "during the SQL statement", or is SQL statement
> also ambiguous? Is that why Oracle did what they did?

Yes, you're finally seeing my issue: "SQL statement" isn't all that
well-defined a concept.

ISTM that the reported behavior of Oracle's pl/sql is *clearly* in
violation of SQL92: the body of a pl/sql function is a single <SQL
procedure statement> per SQL92 4.17, so how can they allow
current_timestamp to change within it?

It would be even more interesting to try the same function called
from another pl/sql function --- in that scenario, hardly anyone
could deny that the whole execution of the inner function is contained
within one statement of the outer function, and therefore
current_timestamp should not be changing within it.

regards, tom lane

Hello!

On Mon, 30 Sep 2002, Bruce Momjian wrote:

> It is not clear to me; is this its own transaction or a function call?

BTW.
As reported by my friend:
Oracle 8.1.7 (ver.9 behaves the same way):

--- cut ---
SQL> SET TRANSACTION READ WRITE;

Transaction set.

SQL> SELECT TO_CHAR(SYSDATE, 'DD-MM-YYYY HH24:MI:SS') FROM DUAL;

TO_CHAR(SYSDATE,'MM
-------------------
02-10-2002 10:04:19

SQL> -- wait a lot

SQL> SELECT TO_CHAR(SYSDATE, 'DD-MM-YYYY HH24:MI:SS') FROM DUAL;

TO_CHAR(SYSDATE,'MM
-------------------
02-10-2002 10:04:27

SQL> COMMIT;

Commit complete.
--- cut ---

> > > > > Any Oracle 9 users out there?
> > > > >
> > > > > I need this run:
> > > > >
> > > > > BEGIN;
> > > > > SELECT CURRENT_TIMESTAMP;
> > > > > -- wait 5 seconds
> > > > > SELECT CURRENT_TIMESTAMP;
> > > > >
> > > > > Are those two timestamps the same?

--
WBR, Yury Bokhoncovich, Senior System Administrator, NOC of F1 Group.
Phone: +7 (3832) 106228, ext.140, E-mail: byg(at)center-f1(dot)ru(dot)
Unix is like a wigwam -- no Gates, no Windows, and an Apache inside.

Yury Bokhoncovich <byg(at)center-f1(dot)ru> writes:
> As reported by my friend:
> Oracle 8.1.7 (ver.9 behaves the same way):
> [ to_char(sysdate) advances in a transaction ]

Now I'm really confused; this directly contradicts the report of Oracle
8's behavior that we had earlier from Roland Roberts. Can someone
explain why the different results?

regards, tom lane

Tom Lane wrote:
> Yury Bokhoncovich <byg(at)center-f1(dot)ru> writes:
>
>>As reported by my friend:
>>Oracle 8.1.7 (ver.9 behaves the same way):
>>[ to_char(sysdate) advances in a transaction ]
>
>
> Now I'm really confused; this directly contradicts the report of Oracle
> 8's behavior that we had earlier from Roland Roberts. Can someone
> explain why the different results?

Roland used an anonymous PL/SQL procedure:

SQL> begin
2 insert into rbr_foo select sysdate from dual;
[...wait about 10 seconds...]
3 insert into rbr_foo select sysdate from dual;
4 end;
5 /

PL/SQL procedure successfully completed.

SQL> select * from rbr_foo;

Oracle isn't processing those statements interactively. SQL*Plus
is waiting on the "/" to send the PL/SQL block to the database.
I suspect its not going to take Oracle more than a second to
insert a row...

Mike Mascari
mascarm(at)mascari(dot)com

>>>>> "Mike" == Mike Mascari <mascarm(at)mascari(dot)com> writes:

Mike> Tom Lane wrote:
>> Yury Bokhoncovich <byg(at)center-f1(dot)ru> writes:

>>> As reported by my friend: Oracle 8.1.7 (ver.9 behaves the same way):

>>> [ to_char(sysdate) advances in a transaction ]

>> Now I'm really confused; this directly contradicts the report
>> of Oracle 8's behavior that we had earlier from Roland Roberts.
>> Can someone explain why the different results?

Mike> Roland used an anonymous PL/SQL procedure:

You're right and I didn't think enough about what was happening. This
also explains why I so often see the same timestamp throughout a
transaction---the transaction is all taking place inside a PL/SQL
procedure.

roland
--
PGP Key ID: 66 BC 3B CD
Roland B. Roberts, PhD RL Enterprises
roland(at)rlenter(dot)com 76-15 113th Street, Apt 3B
roland(at)astrofoto(dot)org Forest Hills, NY 11375

Mike Mascari wrote:
> Tom Lane wrote:
> > Yury Bokhoncovich <byg(at)center-f1(dot)ru> writes:
> >
> >>As reported by my friend:
> >>Oracle 8.1.7 (ver.9 behaves the same way):
> >>[ to_char(sysdate) advances in a transaction ]
> >
> >
> > Now I'm really confused; this directly contradicts the report of Oracle
> > 8's behavior that we had earlier from Roland Roberts. Can someone
> > explain why the different results?
>
> Roland used an anonymous PL/SQL procedure:
>
> SQL> begin
> 2 insert into rbr_foo select sysdate from dual;
> [...wait about 10 seconds...]
> 3 insert into rbr_foo select sysdate from dual;
> 4 end;
> 5 /
>
> PL/SQL procedure successfully completed.
>
> SQL> select * from rbr_foo;
>
> Oracle isn't processing those statements interactively. SQL*Plus
> is waiting on the "/" to send the PL/SQL block to the database.
> I suspect its not going to take Oracle more than a second to
> insert a row...

Oh, I understand now. He delayed when entering the function body, but
that has no effect when he sends it. Can someone add an explicit sleep
in the function body and try that?

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian wrote:
> Mike Mascari wrote:
>>
>>Oracle isn't processing those statements interactively. SQL*Plus
>>is waiting on the "/" to send the PL/SQL block to the database.
>>I suspect its not going to take Oracle more than a second to
>>insert a row...
>
>
> Oh, I understand now. He delayed when entering the function body, but
> that has no effect when he sends it. Can someone add an explicit sleep
> in the function body and try that?
>

SQL> create table foo (a date);

Table created.

SQL> begin
2 insert into foo select sysdate from dual;
3 dbms_lock.sleep(5);
4 insert into foo select sysdate from dual;
5 end;
6 /

PL/SQL procedure successfully completed.

SQL> select to_char(a, 'HH24:MI:SS') from foo;

TO_CHAR(
--------
11:31:02
11:31:07

Mike Mascari
mascarm(at)mascari(dot)com

Mike Mascari wrote:
> Bruce Momjian wrote:
> > Mike Mascari wrote:
> >>
> >>Oracle isn't processing those statements interactively. SQL*Plus
> >>is waiting on the "/" to send the PL/SQL block to the database.
> >>I suspect its not going to take Oracle more than a second to
> >>insert a row...
> >
> >
> > Oh, I understand now. He delayed when entering the function body, but
> > that has no effect when he sends it. Can someone add an explicit sleep
> > in the function body and try that?
> >
>
> SQL> create table foo (a date);
>
> Table created.
>
> SQL> begin
> 2 insert into foo select sysdate from dual;
> 3 dbms_lock.sleep(5);
> 4 insert into foo select sysdate from dual;
> 5 end;
> 6 /
>
> PL/SQL procedure successfully completed.
>
> SQL> select to_char(a, 'HH24:MI:SS') from foo;
>
> TO_CHAR(
> --------
> 11:31:02
> 11:31:07

OK, two requests. First, would you create a _named_ PL/SQL function
with those contents and try it again. Also, would you test
CURRENT_TIMESTAMP too?

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian wrote:
>
> OK, two requests. First, would you create a _named_ PL/SQL function
> with those contents and try it again. Also, would you test
> CURRENT_TIMESTAMP too?
>

SQL> CREATE TABLE foo(a date);

Table created.

As a PROCEDURE:

SQL> CREATE PROCEDURE test
2 AS
3 BEGIN
4 INSERT INTO foo SELECT SYSDATE FROM dual;
5 dbms_lock.sleep(5);
6 INSERT INTO foo SELECT SYSDATE FROM dual;
7 END;
8 /

Procedure created.

SQL> execute test;

PL/SQL procedure successfully completed.

SQL> select to_char(a, 'HH24:MI:SS') from foo;

TO_CHAR(
--------
12:01:07
12:01:12

As a FUNCTION:

SQL> CREATE FUNCTION mydiff
2 RETURN NUMBER
3 IS
4 time1 DATE;
5 time2 DATE;
6 c NUMBER;
7 BEGIN
8 SELECT SYSDATE
9 INTO time1
10 FROM DUAL;
11 SELECT COUNT(*)
12 INTO c
13 FROM bar, bar, bar, bar, bar, bar, bar, bar;
14 SELECT SYSDATE
15 INTO time2
16 FROM DUAL;
17 RETURN (time2 - time1);
18 END;
19 /

Function created.

SQL> select mydiff FROM dual;

MYDIFF
----------
.000034722

I can't test the use of CURRENT_TIMESTAMP because I have Oracle
8, not 9.

Mike Mascari
mascarm(at)mascari(dot)com

Mike Mascari <mascarm(at)mascari(dot)com> writes:
> SQL> CREATE PROCEDURE test
> 2 AS
> 3 BEGIN
> 4 INSERT INTO foo SELECT SYSDATE FROM dual;
> 5 dbms_lock.sleep(5);
> 6 INSERT INTO foo SELECT SYSDATE FROM dual;
> 7 END;
> 8 /

> Procedure created.

> SQL> execute test;

> PL/SQL procedure successfully completed.

> SQL> select to_char(a, 'HH24:MI:SS') from foo;

> TO_CHAR(
> --------
> 12:01:07
> 12:01:12

What fun. So in reality, SYSDATE on Oracle behaves like timeofday():
true current time. That's certainly not a spec-compliant interpretation
for CURRENT_TIMESTAMP :-(

Has anyone done the corresponding experiments on the other DBMSes to
identify exactly when they allow CURRENT_TIMESTAMP to advance?

regards, tom lane

Mike Mascari <mascarm(at)mascari(dot)com> wrote:

> I can't test the use of CURRENT_TIMESTAMP because I have Oracle
> 8, not 9.

What about NOW()? It should be available in Oracle 8? Is it the same as
SYSDATE?

Regards,
Michael Paesold

Michael Paesold wrote:

> What about NOW()? It should be available in Oracle 8? Is it the same as
> SYSDATE?
>

Unless I'm missing something, NOW() neither works in Oracle 8
nor appears in the Oracle 9i online documentation:

http://download-west.oracle.com/otndoc/oracle9i/901_doc/server.901/a90125/functions2.htm#80856

Mike Mascari
mascarm(at)mascari(dot)com

Mike Mascari <mascarm(at)mascari(dot)com> wrote:

> Michael Paesold wrote:
>
> > What about NOW()? It should be available in Oracle 8? Is it the same as
> > SYSDATE?
> >
>
> Unless I'm missing something, NOW() neither works in Oracle 8
> nor appears in the Oracle 9i online documentation:
>
>
http://download-west.oracle.com/otndoc/oracle9i/901_doc/server.901/a90125/fu
nctions2.htm#80856
>
> Mike Mascari

I am sorry, if that is so. I thought it was available, but obviously, I was
wrong.

Regards,
Michael

Tom Lane wrote:

>
>
>Has anyone done the corresponding experiments on the other DBMSes to
>identify exactly when they allow CURRENT_TIMESTAMP to advance ?
>

I have Db2 on hand and examined CURRENT TIMESTAMP in an sql procedure.
(IBM have implemented it without the "_" ....)

The short of it is that CURRENT TIMESTAMP is the not frozen to the
transaction start,
but reflects time movement within the transaction.

Note that "db2 +c" is equivalent to issueing BEGIN in Pg,
and the command line tool (db2) keeps (the same) connection open until
the TERMINATE is issued :

$ cat stamp.sql

create procedure stamp()
language sql
begin
insert into test values(1,current timestamp);
insert into test values(2,current timestamp);
insert into test values(3,current timestamp);
insert into test values(4,current timestamp);
insert into test values(5,current timestamp);
insert into test values(6,current timestamp);
insert into test values(7,current timestamp);
insert into test values(8,current timestamp);
insert into test values(9,current timestamp);
end
@

$ db2 connect to dss
Database Connection Information

Database server = DB2/LINUX 7.2.3
SQL authorization ID = DB2
Local database alias = DSS

$ db2 -td@ -f stamp.sql
DB20000I The SQL command completed successfully.

$ db2 +c
db2 => call stamp();

"STAMP" RETURN_STATUS: "0"

db2 => commit;

DB20000I The SQL command completed successfully.

db2 => select * from test;

ID VAL
----------- --------------------------
1 2002-10-03-19.35.16.286019
2 2002-10-03-19.35.16.286903
3 2002-10-03-19.35.16.287549
4 2002-10-03-19.35.16.288235
5 2002-10-03-19.35.16.288925
6 2002-10-03-19.35.16.289571
7 2002-10-03-19.35.16.290209
8 2002-10-03-19.35.16.290884
9 2002-10-03-19.35.16.291522

9 record(s) selected.

db2 => terminate;

regards

Mark