Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (asf.osuosl.org: local policy)
From: Michael Segel <derby@segel.com>
Reply-To: derby@segel.com
Organization: MSCC 
To: "Derby Discussion" <derby-user@db.apache.org>
Subject: Re: "generated by default" question
Date: Tue, 13 Jun 2006 13:20:48 -0500
User-Agent: KMail/1.8.2
References: <20060613050704.751692BB55@dbrack01.segel.com>
 <1DD636C2-0694-406E-B245-30A807FDF6AD@SUN.com>
In-Reply-To: <1DD636C2-0694-406E-B245-30A807FDF6AD@SUN.com>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="utf-8"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200606131320.48497.derby@segel.com>

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]