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

2002-10-04 Thread Roland Roberts

 Mike == Mike Mascari [EMAIL PROTECTED] writes:

Mike Tom Lane wrote:
 Yury Bokhoncovich [EMAIL PROTECTED] 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
[EMAIL PROTECTED] 76-15 113th Street, Apt 3B
[EMAIL PROTECTED]   Forest Hills, NY 11375

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly



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

2002-10-04 Thread Rob Fullerton


Howdy All,

You have to explicitly commit transactions in oracle using SQL*Plus.
However, DUAL (eg. SELECT current_timestamp FROM DUAL;) is special in this
case.  It is a table in the sys schema, used for selecting constants,
pseudo-columns, etc.

I'm not sure if this helps but see:

http://download-east.oracle.com/otndoc/oracle9i/901_doc/server.901/a90125/queries2.htm#2054162http://download-east.oracle.com/otndoc/oracle9i/901_doc/server.901/a90125/queries2.htm#2054162

rob
'Oracle 9 tester' :P

On Mon, 30 Sep 2002, Bruce Momjian wrote:


 OK, I just received this answer from an Oracle 9 tester.  It shows
 CURRENT_TIMESTAMP changing during the transaction.  Thanks, Dan.

 Dan, it wasn't clear if this was in a transaction or not.  Does Oracle
 have autocommit off by default so you are always in a transaction?

 ---

 Dan Langille wrote:
  A very quick answer:
 
  --- Forwarded message follows ---
  Date: Mon, 30 Sep 2002 13:03:51 -0400 (EDT)
  From: Agent Drek [EMAIL PROTECTED]
  To: [EMAIL PROTECTED] [EMAIL PROTECTED]
  Cc: [EMAIL PROTECTED] [EMAIL PROTECTED]
  Subject: Re: Any Oracle 9 users?  A test please...
  In-Reply-To: 3D984877.19685.801EEC30@localhost
  Message-ID: Pine.BSF.4.44.0209301303030.50384-
  [EMAIL PROTECTED]
  MIME-Version: 1.0
  Content-Type: TEXT/PLAIN; charset=US-ASCII
  Sender: [EMAIL PROTECTED]
 
  On Mon, 30 Sep 2002, Dan Langille wrote:
 
   Date: Mon, 30 Sep 2002 12:49:59 -0400
   From: Dan Langille [EMAIL PROTECTED]
   Reply-To: [EMAIL PROTECTED]
   [EMAIL PROTECTED] To: [EMAIL PROTECTED]
   [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]
   [EMAIL PROTECTED] Subject: Any Oracle 9 users?  A test
   please...
  
   Followups to [EMAIL PROTECTED] 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
  
 
  Our DBA says:
 
  snip from irc
 
  data SQL SELECT current_timestamp FROM DUAL;
  data CURRENT_TIMESTAMP
  data
  --
 
  - data 30-SEP-02 01.06.42.660969 PM -04:00 data SQL SELECT
  current_timestamp FROM DUAL; data CURRENT_TIMESTAMP data
  --
 
  - data 30-SEP-02 01.06.48.837372 PM -04:00 data (you have to
  include 'from dual' for 'non-table' selects)
 
  --
 Derek Marshall
 
  Smash and Pow Inc  'digital plumber'
  http://www.smashpow.net
 
 
  To Unsubscribe: send mail to [EMAIL PROTECTED]
  with unsubscribe freebsd-database in the body of the message
 
  --- 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
   [EMAIL PROTECTED]   |  (610) 359-1001
   +  If your life is a hard drive, |  13 Roberts Road
   +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073




---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html



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

2002-10-04 Thread Dan Langille

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 [EMAIL PROTECTED]
  To: Dan Langille [EMAIL PROTECTED]
  Subject: Re: Any Oracle 9 users?  A test please...
  In-Reply-To: 3D985663.24174.80554E83@localhost
  Message-ID: [EMAIL PROTECTED]
  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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED]
 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
   [EMAIL PROTECTED]   |  (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


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])



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

2002-10-03 Thread Mario Weilguni

Tom Lane wrote:
  

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


This applies up to Oracle 8.1.6, maybe it helps:
According to a co-worker, Oracle advances the time in transactions:
select to_char(sysdate, 'dd.mm. hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'DD
---
03.10.2002 10:16:28

(wait ...)

SQL r
  1* select to_char(sysdate, 'dd.mm. hh24:mi:ss') from dual

TO_CHAR(SYSDATE,'DD
---
03.10.2002 10:17:41


It even advances within procedures/functions, example:

 create or replace procedure foobar is 
   s1 varchar(2000);
   s2 varchar(2000);
 begin
   select to_char(sysdate, 'dd.mm. hh24:mi:ss') into s1 from dual;
   (... put long running query here ...)
   select to_char(sysdate, 'dd.mm. hh24:mi:ss') into s2 from dual;
   dbms_output.put_line(s1);
   dbms_output.put_line(s2);
 end; 
/

set serverout on
execute foobar;


Hope it helps.

Regards,
Mario Weilguni

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]



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

2002-10-03 Thread Mark Kirkwood

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
DB2I  The SQL command completed successfully.

$ db2 +c
db2 = call stamp();

STAMP RETURN_STATUS: 0

db2 = commit;

DB2I  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


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])



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

2002-10-02 Thread Yury Bokhoncovich

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- 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- 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: [EMAIL PROTECTED]
Unix is like a wigwam -- no Gates, no Windows, and an Apache inside.



---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]



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

2002-10-02 Thread Tom Lane

Yury Bokhoncovich [EMAIL PROTECTED] 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

---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://archives.postgresql.org



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

2002-10-02 Thread Mike Mascari

Tom Lane wrote:
 Yury Bokhoncovich [EMAIL PROTECTED] 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
[EMAIL PROTECTED]


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster



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

2002-10-02 Thread Bruce Momjian

Mike Mascari wrote:
 Tom Lane wrote:
  Yury Bokhoncovich [EMAIL PROTECTED] 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
  [EMAIL PROTECTED]   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://archives.postgresql.org



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

2002-10-02 Thread Mike Mascari

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
[EMAIL PROTECTED]





---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])



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

2002-10-02 Thread Bruce Momjian

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
  [EMAIL PROTECTED]   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])



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

2002-10-02 Thread Mike Mascari

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
--
.34722

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

Mike Mascari
[EMAIL PROTECTED]













---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html



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

2002-10-02 Thread Tom Lane

Mike Mascari [EMAIL PROTECTED] 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

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster



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

2002-10-02 Thread Bruce Momjian


I received this via personal email.  I assume the author wants it
shared.  It shows CURRENT_TIMESTAMP changing within a function!

---

Steve Hulcher wrote:
 Oracle 9i.
 
 Hope this is helpful
 
 
 --SQL RUN
 /*
 CREATE TABLE foo (a DATE);
 CREATE OR REPLACE PROCEDURE test
 AS
 BEGIN
   INSERT INTO foo SELECT CURRENT_TIMESTAMP FROM dual;
   dbms_lock.sleep(5);
   INSERT INTO foo SELECT CURRENT_TIMESTAMP FROM dual;
 END;
 /
 show errors;
 */
 
 DELETE FROM foo;
 EXECUTE test;
 
 SELECT TO_CHAR(a, '-MM-DD HH24:MI:SS') FROM foo;
 
 --RESULTS
 0 rows deleted.
 
 
 PL/SQL procedure successfully completed.
 
 
 TO_CHAR(A,'-MM-
 ---
 2002-10-02 11:33:12
 2002-10-02 11:33:17
 
 
 
 -Original Message-
 From: Mike Mascari [mailto:[EMAIL PROTECTED]]
 Sent: Wednesday, October 02, 2002 11:20 AM
 To: Bruce Momjian
 Cc: Yury Bokhoncovich; Dan Langille; Roland Roberts;
 PostgreSQL-development
 Subject: Re: [HACKERS] (Fwd) Re: Any Oracle 9 users? A test please...
 
 
 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
 --
 .34722
 
 I can't test the use of CURRENT_TIMESTAMP because I have Oracle 
 8, not 9.
 
 Mike Mascari
 [EMAIL PROTECTED]
 
 
 
 
 
 
 
 
 
 
 
 
 
 ---(end of broadcast)---
 TIP 5: Have you checked our extensive FAQ?
 
 http://www.postgresql.org/users-lounge/docs/faq.html
 

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

---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://archives.postgresql.org



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

2002-10-02 Thread Michael Paesold

Mike Mascari [EMAIL PROTECTED] 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


---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://archives.postgresql.org



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

2002-10-02 Thread Mike Mascari

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
[EMAIL PROTECTED]


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]



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

2002-10-02 Thread Michael Paesold

Mike Mascari [EMAIL PROTECTED] 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


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]



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

2002-09-30 Thread Bruce Momjian


OK, I just received this answer from an Oracle 9 tester.  It shows
CURRENT_TIMESTAMP changing during the transaction.  Thanks, Dan.

Dan, it wasn't clear if this was in a transaction or not.  Does Oracle
have autocommit off by default so you are always in a transaction?

---

Dan Langille wrote:
 A very quick answer:
 
 --- Forwarded message follows ---
 Date: Mon, 30 Sep 2002 13:03:51 -0400 (EDT)
 From: Agent Drek [EMAIL PROTECTED]
 To: [EMAIL PROTECTED] [EMAIL PROTECTED]
 Cc: [EMAIL PROTECTED] [EMAIL PROTECTED]
 Subject: Re: Any Oracle 9 users?  A test please...
 In-Reply-To: 3D984877.19685.801EEC30@localhost
 Message-ID: Pine.BSF.4.44.0209301303030.50384-
 [EMAIL PROTECTED]
 MIME-Version: 1.0
 Content-Type: TEXT/PLAIN; charset=US-ASCII
 Sender: [EMAIL PROTECTED]
 
 On Mon, 30 Sep 2002, Dan Langille wrote:
 
  Date: Mon, 30 Sep 2002 12:49:59 -0400
  From: Dan Langille [EMAIL PROTECTED]
  Reply-To: [EMAIL PROTECTED]
  [EMAIL PROTECTED] To: [EMAIL PROTECTED]
  [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]
  [EMAIL PROTECTED] Subject: Any Oracle 9 users?  A test
  please...
 
  Followups to [EMAIL PROTECTED] 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
 
 
 Our DBA says:
 
 snip from irc
 
 data SQL SELECT current_timestamp FROM DUAL;
 data CURRENT_TIMESTAMP
 data
 --
 
 - data 30-SEP-02 01.06.42.660969 PM -04:00 data SQL SELECT
 current_timestamp FROM DUAL; data CURRENT_TIMESTAMP data
 --
 
 - data 30-SEP-02 01.06.48.837372 PM -04:00 data (you have to
 include 'from dual' for 'non-table' selects)
 
 --
Derek Marshall
 
 Smash and Pow Inc  'digital plumber'
 http://www.smashpow.net
 
 
 To Unsubscribe: send mail to [EMAIL PROTECTED]
 with unsubscribe freebsd-database in the body of the message
 
 --- 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
  [EMAIL PROTECTED]   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])



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

2002-09-30 Thread Bruce Momjian


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 [EMAIL PROTECTED]
 To: Dan Langille [EMAIL PROTECTED]
 Subject: Re: Any Oracle 9 users?  A test please...
 In-Reply-To: 3D985663.24174.80554E83@localhost
 Message-ID: [EMAIL PROTECTED]
 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 [EMAIL PROTECTED] 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 [EMAIL PROTECTED]
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
  [EMAIL PROTECTED]   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly



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

2002-09-30 Thread Mike Mascari

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 
functions, 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
[EMAIL PROTECTED]

 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.



---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html



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

2002-09-30 Thread Bruce Momjian


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
  [EMAIL PROTECTED]   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly



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

2002-09-30 Thread Hannu Krosing

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



---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://archives.postgresql.org



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

2002-09-30 Thread Bruce Momjian

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
  [EMAIL PROTECTED]   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://archives.postgresql.org



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

2002-09-30 Thread Mike Mascari

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
[EMAIL PROTECTED]







---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])



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

2002-09-30 Thread Manfred Koizar

On Mon, 30 Sep 2002 15:29:07 -0400, Mike Mascari [EMAIL PROTECTED]
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
10242002-09-30 22:26:06.550
31252002-09-30 22:26:06.550
77762002-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.
   1 2002-09-30 22:37:54.
  32 2002-09-30 22:37:54.
 243 2002-09-30 22:37:54.
1024 2002-09-30 22:37:54.
3125 2002-09-30 22:37:54.
7776 2002-09-30 22:37:54.
   16807 2002-09-30 22:37:54.
   32768 2002-09-30 22:37:54.
   59049 2002-09-30 22:37:54.

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.0  --- bug here?
Postgres:   0   2002-09-30 21:10:35.660781+02   1

Servus
 Manfred

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html



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

2002-09-30 Thread Tom Lane

Bruce Momjian [EMAIL PROTECTED] 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

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html