> -----Original Message----- > From: Daniel John Debrunner [mailto:[EMAIL PROTECTED] > Sent: Thursday, June 29, 2006 3:22 PM > To: Derby Discussion > Subject: Re: "generated by default" question > > [EMAIL PROTECTED] wrote: > > > > > [mjs] > > Bzzzt! > > No. > > > > Already solved your first example. Just reset the sequence if the row > has a > > value in the identity column between CBV and MAX. > > Oops! Guess that blows away your theory. > > I don't have any theory, I was pointing out that the proposed "very > simple solution" had some issues. To become workable it needs some > additional changes, potentially like the ones you described, thus the > solution becomes more complex. I just didn't want someone to start > implementing it and assume it would automatically be accepted as a > solution. > [mjs] The solution really hasn't changed. In my first suggestion, I assumed that your MAX value couldn't be set and would be 2^63 -1 since the sequence uses an INTEGER (8 Bytes).
If you want to allow for the Derby Sequence to implement changing the MAX value, then you need to account for it in the solution. True, I did ignore the possibility of updating the identity value. Most likely because I've never had a use case where someone did update identity values. The solution is truly trivial. The issue occurs when you cycle the sequence. That's a more complex issue and more than likely, you'll have to work through the backing index to find the next open value. It can be done, but I don't know how efficient the code could be. (Meaning that the cost of the solution per transaction doesn't justify implementing it.) > This is how open source works, people suggest solutions, others see > issues with them, others provide additional input, and hopefully a good > solution emerges from the community. > > Dan. [mjs] Well. Sure. 'Cept for one minor problem. Up until now, the push back from mainly Sun posters has been that this isn't really a bug and that there's nothing wrong with the current implementation. Now everyone is entitled to their opinion, however what makes this really disappointing is that both IBM and SUN sell commercial versions of Derby. (Cloudscape and JavaDB). I believe that both are selling support contracts as well for bug fixes. This should be considered either a bug, a product defect, or a design defect. Note too that this same bug came up last around last Dec timeframe.... The solution I suggest is a reverse engineering of IBM (Informix) IDS's Serial data type. The reason that it's so effective is that during any insert in to a table that has an identity column defined is that you'll have the row about to be inserted in memory, and the sequence generator for that table. (You don't need to use the index at all.) [Read: No need to flush pages in memory beyond what occurs within a normal insert.] Going on an assumption, the identity backing index is a B+ Tree? If so, then you can walk the tree to find the next gap in the numbers. Not sure how fast this would be but it's doable.
