Horton

Thread

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

    Hannu Krosing <hannu@tm.ee> — 2002-09-30T18:35:57Z

    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

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

    Bruce Momjian <pgman@candle.pha.pa.us> — 2002-09-30T19:07:27Z

    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@mail.e-perception.com>
> To: Dan Langille <dan@langille.org>
> Subject: Re: Any Oracle 9 users?  A test please...
> In-Reply-To: <3D985663.24174.80554E83@localhost>
> Message-ID: <20020930114241.E45374-100000@mail.e-perception.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@freebsd.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@FreeBSD.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@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

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

    Mike Mascari <mascarm@mascari.com> — 2002-09-30T19:29:07Z

    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@mascari.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.

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

    Bruce Momjian <pgman@candle.pha.pa.us> — 2002-09-30T20:10:06Z

    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@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

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

    Bruce Momjian <pgman@candle.pha.pa.us> — 2002-09-30T20:37:55Z

    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@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

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

    Mike Mascari <mascarm@mascari.com> — 2002-09-30T20:53:33Z

    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@mascari.com

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

    Manfred Koizar <mkoi-pg@aon.at> — 2002-09-30T21:04:34Z

    On Mon, 30 Sep 2002 15:29:07 -0400, Mike Mascari <mascarm@mascari.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

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

    Dan Langille <dan@langille.org> — 2002-09-30T21:33:23Z

    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@mail.e-perception.com>
> > To: Dan Langille <dan@langille.org>
> > Subject: Re: Any Oracle 9 users?  A test please...
> > In-Reply-To: <3D985663.24174.80554E83@localhost>
> > Message-ID: <20020930114241.E45374-100000@mail.e-perception.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@freebsd.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@FreeBSD.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@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
> 


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

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2002-09-30T22:40:28Z

    Bruce Momjian <pgman@candle.pha.pa.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

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

    Yury Bokhoncovich <byg@center-f1.ru> — 2002-10-02T07:33:29Z

    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@center-f1.ru.
Unix is like a wigwam -- no Gates, no Windows, and an Apache inside.

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2002-10-02T13:17:19Z

    Yury Bokhoncovich <byg@center-f1.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

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

    Mike Mascari <mascarm@mascari.com> — 2002-10-02T14:08:34Z

    Tom Lane wrote:
> Yury Bokhoncovich <byg@center-f1.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@mascari.com

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

    Roland Roberts <roland@astrofoto.org> — 2002-10-02T14:48:45Z

    >>>>> "Mike" == Mike Mascari <mascarm@mascari.com> writes:

    Mike> Tom Lane wrote:
    >> Yury Bokhoncovich <byg@center-f1.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@rlenter.com                     76-15 113th Street, Apt 3B
roland@astrofoto.org                       Forest Hills, NY 11375

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

    Bruce Momjian <pgman@candle.pha.pa.us> — 2002-10-02T15:14:08Z

    Mike Mascari wrote:
> Tom Lane wrote:
> > Yury Bokhoncovich <byg@center-f1.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@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

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

    Mike Mascari <mascarm@mascari.com> — 2002-10-02T15:29:29Z

    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@mascari.com

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

    Bruce Momjian <pgman@candle.pha.pa.us> — 2002-10-02T15:41:48Z

    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@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

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

    Mike Mascari <mascarm@mascari.com> — 2002-10-02T16:20:01Z

    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@mascari.com

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2002-10-02T17:19:11Z

    Mike Mascari <mascarm@mascari.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

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

    Michael Paesold <mpaesold@gmx.at> — 2002-10-02T22:43:55Z

    Mike Mascari <mascarm@mascari.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

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

    Mike Mascari <mascarm@mascari.com> — 2002-10-02T22:52:34Z

    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@mascari.com

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

    Michael Paesold <mpaesold@gmx.at> — 2002-10-02T23:00:56Z

    Mike Mascari <mascarm@mascari.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

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

    Mark Kirkwood <markir@paradise.net.nz> — 2002-10-03T07:56:47Z

    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