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 Thu, 15 Jun 2006 21:12:21 GMT

I'm sorry, but where did I ever say 'select max()'?
Hint: I didn't because its not that efficient.

Daniel made that suggestion, however I think it was off the cuff and was
meant to address error trapping.

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.

Again, here's the simple and *EFFICIENT* solution....

When you have an insert statement against a table with an identity column...
  If the insert statement has a value for the identity column and
      If the value is > than CBV, then 
      In an after insert clause, assign CBV to be that value or the next
largest non-negative integer value that satisfies the equation in 9.21
paragraph 3.

That simple. Every value you need should be in memory at the time of the

The logic is simple. Yes you want to return the MIN(N) where N is the set of
solution values in 9.21. However you need to ensure that the MIN(N) will
return a valid number that can be used for an insert.

So if you think about it, the only time you'll have a problem is if the user
inserts a row that already has a value and that value is larger than the
last sequence number generated. (CBV). Therefore, if after the insert is
completed, you grab a mutex lock on the sequence generator, and then reset
the CBV, you'll always know that the MIN(N) will work.

That simple.

Now, if you want to do a work around, then you need to do an after insert
trigger and a stored procedure to handle the logic. Again all the data
should be in memory, so it should be a snap.

But hey, what do I know? ;-)
If you don't believe me, just check out how IBM's IDS handles a serial /
serial8 data type.


> -----Original Message-----
> From: Vic Ricker [mailto:vic@ricker.us]
> Sent: Thursday, June 15, 2006 2:05 PM
> To: Derby Discussion
> Subject: Re: "generated by default" question
> 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/

View raw message