Hi,
PostgreSQL also throws an error if NEXTVAL was not yet called:
drop sequence abc;
create sequence abc;
select currval('ABC');
-- PostgreSQL: ERROR: currval of sequence "abc" is not yet defined in
this session 55000/0
-- H2: 0
select nextval('ABC'); -- 1
select currval('ABC'); -- 1
It looks like in PostgreSQL and Oracle, CURRVAL is session specific. I
didn't know that. In H2, CURRVAL is the last value (independent of the
session). This is an incompatibility, I guess I need to fix that.
The current value is also available in the system table
INFORMATION_SCHEMA.SEQUENCES, in that case it's probably OK to return
the last used value (as done now), or maybe NULL if NEXTVAL was never
called by any session.
Regards,
Thomas
On Mon, Mar 2, 2009 at 5:06 PM, Evan Nelson <[email protected]> wrote:
>
> Interesting. That just seems like a blatantly stupid way to implement
> a sequence... but, if that's the way Oracle does it, then I guess
> that's the way we need to support it.
>
> Thanks for the info, I appreciate it.
>
> -Evan
>
> On Mar 2, 1:28 am, Johann Schleier-Smith <[email protected]> wrote:
>> The START WITH value for a sequence is returned after the first call to
>> NEXTVAL, so the H2 implementation and tests are sensible.
>> For comparison I ran this code in Oracle. It throws an error (ORA-08002) if
>> call TESTSEQ.CURRVAL before TESTSEQ.NEXTVAL. Returning start with minus
>> increment rather than an error for an initial call to CURRVAL is a
>> reasonable implementation.
>>
>> On Sun, Mar 1, 2009 at 5:16 PM, Evan Nelson <[email protected]> wrote:
>>
>> > I've been banging my head trying to figure out why my new code for
>> > SEQUENCEs isn't passing the tests stored in the testing script. I
>> > think it's because the tests are all based on an incorrect
>> > implementation of SEQUENCE!
>>
>> > In the current version of the code, try the following:
>>
>> > CREATE SEQUENCE testSeq START WITH 5;
>> > SELECT testSeq.currval;
>>
>> > Because this sequence is starting with 5 and we haven't modified it,
>> > it stands to reason that currval would return 5, doesn't it? It
>> > doesn't do that, though. Instead, it returns 4. Similarly, the
>> > following SQL code...
>>
>> > CREATE SEQUENCE testSeq START WITH 10 INCREMENT BY 3;
>> > SELECT testSeq.currval;
>>
>> > ...will return 7. In Sequence.java, the value is stored correctly, but
>> > the function "getCurrentValue()" returns the current value minus the
>> > increment. However, all the script tests seem to think this is normal.
>> > Am I missing something, or isn't this incorrect behavior?
>>
>> > -Evan
> >
>
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en
-~----------~----~----~----~------~----~------~--~---
