Hi Mikey, On Jun 13, 2006, at 10:47 AM, derby@segel.com wrote: > > > by default" question >> >> Hi Mikey, >> >> On Jun 12, 2006, at 10:05 PM, derby@segel.com wrote: >> >>> [mjs] >>> I believe the problem is in how you're interpreting clause 3): >>> " >>> 3) If there exists a non-negative integer N such that SMIN <= >>> CBV + N >>> * INC <= SMAX and the value (CBV + N * INC) has not >>> already been >>> returned in the current cycle, then let V1 be (CBV + N * >>> INC). Otherwise, ... >>> " >>> -=- >>> >>> It doesn't say what N is. That is to say... >>> Suppose you have a sequence 0,1,2,3,4 inserted so that the next >>> number >>> should be 5. Yet suppose someone inserts a row with 5. Thus when >>> you try to >>> use 5, you generate an error. In sub-section 3), N could =6 or any >>> number >6 >>> but less than the MAX value of an integer. >>> >>> It can be interpreted that the sequence should attempt to generate >>> N such >>> that it doesn't fail on the insert.... >>> >>> This is where the idea of selecting the MAX() value in the identity >>> column >>> and incrementing it by one for the failed insert. >>> >>> And that would be a compliant solution. >> >> As I read this part of the specification, it refers to the generation >> of the sequence number, and not to the usage. The trick phrase is >> "the value (CBV + N * INC) has not already been returned in the >> current cycle". As I understand "returned" it means returned by the >> sequence generator, and nothing to do with the usage as a column >> value. >> >> In fact, you could argue that if the implementation skipped returning >> a sequence value just because that value had been inserted by the >> user into a column, it would be a bug. >> >> Regards, >> >> Craig > > [mjs] Hi Craig, > > Errr. No. > In short, the sequence generation is outside of the transaction, > therefore > its possible to get a jump in the sequence number due to transactions > rolling back or individual inserts failing due to additional > constraints. Not my point. > > Please note that I'm assuming an incremental value of 1 to prove > the point. > If the sequence use a different value for its increment, then you > will have > to adjust the logic a bit. > > Here's the section 4.21 language:" > When a row R is presented for insertion into > BT, if R does not contain a column corresponding to IC, then > the value > V for IC in the row inserted into BT is obtained by applying the > General Rules of Subclause 9.21, "Generation of the next value > of a > sequence generator", to SG." All this defines is the "default" instead of "always" behavior. > > Ok so you're looking at what they term is a "general rule". (Not > going to > define what *that* means. > > However looking at 9.21, > > 9.21 defines a mathematical statement. And the solution under 9.21 > is really > a solution set. There a potential of (MAX - CBV)/INC elements (and > the empty > set) that could be applied where CBV < MAX. ok. > > Your suggestion violates this because the number of possible values > for N > will be >= 1 iff (MAX-CBV)/INC >1. no. What I'm saying is that the sequence generator is defined to return all values between the start and max value without skipping any. And at the risk of repeating myself, this is behavior of the sequence generator not the constraint on the column values. > > IF you return a sequence value N that meets the criteria, then > you're not > violating 9.21. > > Note again that this is a generalization and that implementations > of these > rules can vary. > > The first piece of the fix would be to do the following logic: > > If (row inserted contains a value R that corresponds to IC) { > After Insert: > If ( R > CBV) Then CBV=R; // Or R+1; > } else { > CBV += INC; // INC = incremental value > } > > Sorry for the bad pseudo code. > > The idea is that you'll always be setting CBV to the max value when > someone > inserts a row with their own identity value that is greater than > the current > base value. You still haven't responded to my main point: As I read this part of the specification, it refers to the generation of the sequence number, and not to the usage. The trick phrase is "the value (CBV + N * INC) has not already been returned in the current cycle". As I understand "returned" it means returned by the sequence generator, and nothing to do with the usage as a column value. Simply put, the sequence generator must return all legal values of SMIN < return-value < SMAX before reusing a return-value. > > This is what Informix appears to be doing with their Serial value. > > Of course when you cycle over, then you have to worry about the > issue what > happens if the row already has a value in it. And that's the next > issue. > > Sorry, but this is still a bug. The current version of Derby does > violate > the specs as stated here. Sorry, not convinced. Craig > > Look at it this way. As long as a value N exists between the bounds > of CBV > and MAX, you will always have a sequence number. > > By following this logic, and going through a single cycle, you will > always > have either a value or an empty set when MAX - CBV < INC. > > -G > > > Craig Russell Architect, Sun Java Enterprise System http://java.sun.com/products/jdo 408 276-5638 mailto:Craig.Russell@sun.com P.S. A good JDO? O, Gasp!