db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Michael Segel <de...@segel.com>
Subject Re: "generated by default" question
Date Tue, 13 Jun 2006 18:20:48 GMT
Craig,

On a hunch, I did a quick test.
Since I have Informix up and running, 
I created a table foo with two columns.
The first is a serial column, the second is a text string.

The table already had a row 
        id desc

          1 Test 1

I then did the following statement(s):
 INSERT INTO foo VALUES (2147483647, "Trying to find the top");
 INSERT INTO foo VALUES (0, "Trying to find the top");
 INSERT INTO foo VALUES (0, "Trying to find the top");
SELECT * FROM foo;

This errored out on the second statement with a duplicate row.
I ran the select again...
       id desc

          1 Test 1
 2147483647 Trying to find the t

I then tried to run the the following:
 INSERT INTO foo VALUES (0, "Trying to find the top");
 INSERT INTO foo VALUES (0, "Trying to find the top");
SELECT * FROM foo;

         id desc

          1 Test 1
 2147483647 Trying to find the t
          2 Trying to find the t
          3 Trying to find the t
 
So as you can see, if the sequence is cycled, then it doesn't catch the error.

Now my take...
This too is a bug. However, the required fix is a tad more complex and the 
odds of cycling through 2 billion rows was considered a low probability.

Using a sequence of a LONG vs an INTEGER, you have (2^63) -1 rows to cycle 
through. (8 Bytes vs 4 Bytes). 8 Bytes ~= 9.22 X 10^18 potential values.

Since I don't own a copy of the spec, I don't know if they get in to the 
detail on how to handle cycling a sequence. 

And yes, the spec deals with sequences, and with an Identity Column , yet 
doesn't detail how to handle situations when the sequence generates a number 
that is already in use.

The approach by Informix works for the first cycle. To handle issues beyond 
that, then you have to get creative.... ;-)

But hey! What do I know? ;-)

BTW, I think if you  look at the index on the identity column, you may find a 
way to handle the cycles and trap for the constraint so that the only time 
you fail is if the table is full. 


-- 
--
Michael Segel
Principal 
Michael Segel Consulting Corp.
derby@segel.com
(312) 952-8175 [mobile]

Mime
View raw message