On Friday 16 June 2006 1:29 am, Vic Ricker wrote: > Michael Segel wrote: > > Look, if you grok the math, you'll see that if you return a result from > > the sequence that causes an error, then you've got a bug, when there's > > another number within the result set that doesn't cause the exception. > > I don't know how you can call it a bug. The behavior is documented. > Just because it doesn't work the way you think it should doesn't make it > wrong. There are other databases like PostgreSQL that behave similarly. > I suppose they are buggy as well? > Yes, when the spec is written the way it is, the math implies that the solution for N is a set of values. If Derby doesn't return a value that will result in a successful insert, then yes it is a bug / design defect.
Can I help it if those who read the spec don't grok the implications of the math? Look at it this way. The spec didn't say "Choose the Min(N) where N is the solution set to ...". It said to choose a nonnegative integer that was in between the CBV and the MAX() that had not been used. So obviously you'll want to return the Min(N) which you can guarantee will work.... Which is what I'm suggesting. If you can't return a value from the sequence that does work then you've failed the spec. > > Again, here's the simple and *EFFICIENT* solution.... > > [SNIP] > > It sounds pretty expensive, especially if you're doing a lot of > inserts/updates. > Uhm, you really think so? Try it, you'll find out that its cheap. really. You'll need the following: 1) X, the value inserted in to the table in place of the default value. 2) the CSV which is part of the sequence object, which you'll have in memory. 3) a MUTEX lock or set of locks so that no one can perform an insert until you finish. (Note. if you hold a mutex lock on the sequence, then those who need a default value are blocked. and then those who want to insert a value of X > CSV are also blocked. Only those who are inserting with a value X < CSV are not blocked. And that's pretty much it. And they will all be in memory and should be in active pages. Now I'm making an assumption that Derby will still contain a copy of the input values of the corresponding insert when you hit an after insert condition... if not, then you need some small pre-insert logic. But to your point.... You have one additional conditional check for those inserts that use the auto generation of a sequence value. If you don't you just use the sequence generator.... You have an additional check or condition to see if the insert that contains a value X for an identity column has a value of X > CBV. If you dont, then you just do the insert.... If this is the case, then you have the cost of holding a mutex lock, then updating the variable, and then releasing the lock. Not very expensive at all. Nope that about sums it up. Not very expensive. I guess it all depends on how you implement this trival logic. But hey! What do I know? ;-) > > I think we'll just have to agree to disagree. Whether you agree or disagree doesn't change the fact that this is a bug. Here's a free clue. If I can't trust Derby to autogenerate a value by default that will insert a row which has no other problems or contraints, then I can't use Derby.
