db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Michael Segel <de...@segel.com>
Subject Re: "generated by default" question
Date Tue, 30 May 2006 21:38:09 GMT
On Tuesday 30 May 2006 1:22 pm, Mamta Satoor wrote:
> On 5/30/06, Michael Segel <derby@segel.com> wrote:
> > I tried to send this out earlier from a different machine...
> >
> > I read the thread of articles, and unfortunately, you're wrong, or rather
> > the
> > material that you posted regarding the spec does not match this issue.
> >
> > From your earlier post:
> > -=-
> > I looked at the SQL 2000 spec to see what it says about generated keys.
> > Following are some lines copied from the Identity columns section and
> > from Sequence generators section. The value generation for identity
> > column follows
> > the gules of Sequence generator. And in the Sequence generator section,
> > the
> > spec says that value generation is done in a transaction of its own and
> > is not associated with the outside user transaction.
> >
> > 4.14.4 Identity columns
> >
> > An identity column is associated with an internal sequence generator
> > SG. Let IC be the identity column of BT . 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 . The definition of an identity column may specify
> > GENERATED ALWAYS or GENERATED BY DEFAULT.
> > [\n added by MJS]
> > 4.21 Sequence generators
> >
> > Changes to the current base value of a sequence generator are not
> > controlled
> > by SQL-transactions; therefore, commits and rollbacks of SQL-transactions
> > have no effect on the current base value of a sequence generator.
> >
> > -=-
> > First in 4.14.4 it references 9.21.
> >
> > You include 4.21.
> >
> > So either you looked at the wrong sub section or you have a typo.
>
> It's not a wrong subsection or typo. I was copying only the relevant
> sections from SQL 2002 spec for the thread
> http://www.nabble.com/-Derby-359-Skipping+over+user+inserted+values+into+GE
>NERATED+BY+DEFAULT+identity+columns....-t735573.html#a1944069
> <http://www.nabble.com/-Derby-359-Skipping+over+user+inserted+values+into+G
>ENERATED+BY+DEFAULT+identity+columns....-t735573.html#a1944069> Section
> 4.21 had information on how sequence generation happens in it's own
> transactions.
>
So why didn't you include 9.21?

Again 4.21 has nothing to do with the issue of what happens when an Identity 
column hits a value that is already in use. 
(I suggest you re-read what you posted. It deals with the fact that identity 
sequences need to be outside of transactions.)

> Second...
>
> > 4.21 doesn't talk about the issue at hand. What 4.21 talks about is that
> > when
> > you implement a sequence generator, it needs to be outside of any
> > transactions.
> >
> > Suppose thread 1 inserts 3 rows in to a table with an identity column.
> > Suppose thread 2 inserts 2 rows.
> > Thread 2 commits.
> > Thread 1 rollsback.
> >
> > Suppose that the starting value of the identity column was one.
> > At the end of all transactions, there will be two rows in the table, with
> > the
> > identity values of 4 and 5 respectively. The next value in the identity
> > sequence is 6.
> >
> > That is what is meant by 4.21.
> >
> > The issue that we see is that Derby barfs when it hits a row that was
> > inserted
> > without using the identity value, and Derby doesn't know how to generate
> > the
> > next identity value.
>
> I would not claim that I went through entire SQL2002 spec to know this for
> sure but from what I can remember, I don't think the spec covers what
> should happen if a generated key is already used.
>
Ah then thats the  crux of the problem. 
4.14 started to talk about this and then identified more issues in 9.21 which 
you didn't post.

What people have seen, along with the existing JIRA entry is that Derby does 
not comply with what should be expected behavior.

Thus its a bug.

Here's another way to look at it.
Derby fails to insert a row that complies with the criteria for entering a row 
without a value in the identity column.


> That sir, is a bug.
>
> > And no, the SQL 2000 spec, as presented in this discussion does not
> > concur with how derby is implementing their auto sequence generation.
> >
> > So, when will either Sun or IBM fix this? ;-)
> >
> > -G
> >
> > On Tuesday 30 May 2006 11:02 am, Mamta Satoor wrote:
[SNIP]
-- 
--
Michael Segel
Principal 
Michael Segel Consulting Corp.
derby@segel.com
(312) 952-8175 [mobile]

Mime
View raw message