db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Michael Segel <mse...@segel.com>
Subject Re: "generated by default" question
Date Fri, 16 Jun 2006 07:03:10 GMT
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 

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 

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.

View raw message