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.
[EMAIL PROTECTED]
(312) 952-8175 [mobile]
