Sorry Michael, I have to agree with Bernt and Craig. I think, for performance reasons, it doesn't make sense to have it select max() on the identity columns, especially since they're not guaranteed to be indexed. I don't think you'd want Derby doing a table scan just because you previously inserted a value that collided with the sequence generator. I think it would probably make more sense to build the "smarts" into your application rather than relying on Derby to do something that could cause performance problems.
-Vic P.S. The first CPU that I learned assembly language on was the 6809. It was cool. Michael Segel wrote: > Sigh. > > Don't they teach math in engineering anymore? > > Lets try this one more time. > > In 9.21, if N does exist, then N represents a solution set of potential > values. > > In your interpretation, you are *implicitly* adding an additional boundary > that the sequence returns the MIN(N) regardless of the overall > implementation; > > That is not part of the spec! > > Thus, if you fail to insert a value in to an identity row and the failure is > due to the fact that the sequence returned a value that was in use, while > there were other values within the solution set N, it is a bug. > > However, if you could guarantee that the MIN(N) will be a value such that > the will succeed then you will be ok. > > Again, here's how trivial the solution is: > > If you have a row to be inserted within a table that has an identity column, > and that row has a value X for the identity column... > > If that insert is successful and if (X>CBV) then reassign CBV to be X or the > next non-negative integer larger than X such that it complies with the math > in 9.21 (Note: You have to account for INC when INC > 1) > > This will always guarantee that if implemented properly, then you will > always have the correct value. > > > It's that simple. > So why doesn't anyone from IBM or SUN who sells support fix this bug? > HINT: You should be able to implement a fix and do some unit testing within > 3 business days. (24 hours of coding time.) [Longer if you're not familiar > with the code.] > > Note: The cost per transaction of implementing this solution is minimal. All > the necessary information should be in memory at the time of the insert. > > But hey! What do I know? > Its not like I wrote the operating system of an embedded 6809 controller and > the initial application used to add Chlorine during the water purification > process used in major cities throughout the US .... ;-) > > -G > > [P.S. Ok, I did. The point being that if you write careless code, you run > the risk of doing harm to others. That is why you need to be careful when > you follow a spec.] > > >>> However, that does not mean that the implementation of the sequence >>> generator is compliant. >>> >> That's exactly what it means. And that's my final word. I will not >> pursue this debate further. >> >> "OH, East is East, and West is West, and never the twain shall meet" >> (Rudyard Kipling) >> >> -- >> Bernt Marius Johnsen, Database Technology Group, >> Staff Engineer, Technical Lead Derby/Java DB >> Sun Microsystems, Trondheim, Norway >> > > -- Vic Ricker http://www.ricker.us/
