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

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