db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mamta Satoor" <msat...@gmail.com>
Subject Re: "generated by default" question
Date Tue, 30 May 2006 18:22:52 GMT
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+GENERATED+BY+DEFAULT+identity+columns....-t735573.html#a1944069
<http://www.nabble.com/-Derby-359-Skipping+over+user+inserted+values+into+GENERATED+BY+DEFAULT+identity+columns....-t735573.html#a1944069>
Section
4.21 had information on how sequence generation happens in it's own
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.

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:
> > Based on the thread
> >
> http://www.nabble.com/-Derby-359-Skipping+over+user+inserted+values+into+GE
> >NERATED+BY+DEFAULT+identity+columns....-t735573.html#a1944069 Derby
> > behavior is SQL 2000 compatible.
> >
> > Mamta
> >
> > On 5/30/06, Craig L Russell <Craig.Russell@sun.com> wrote:
> > > Hi G,
> > >
> > > Do you have a good solution that you can write up in detail and post
> > > to JIRA? Then someone with an itch can fix it. Do-ocracy in action.
> > >
> > > Craig
> > >
> > > On May 30, 2006, at 7:38 AM, derby@segel.com wrote:
> > > > Hmmm,
> > > >
> > > > Yeah, that's what I'm afraid of.
> > > >
> > > > Essentially what they are asking is that if you try to insert a row
> > > > and it
> > > > fails, you have two options.
> > > >
> > > > 1) Increment your count and try again (Restart) until you succeed,
> > > > or, find
> > > > the max value, and reset to max value + 1.
> > > >
> > > > The trouble in using #2, is that lets say you have rows 1,2,3,4,5
> > > > entered,
> > > > and someone manually enters 10, 1001, 1002, 1004. So when you
> > > > search for the
> > > > max value, you'll end up 1004, and restart with 1005.
> > > >
> > > > This is something that should be done behind the scenes.
> > > >
> > > > SO its not a "bug" but a design defect.
> > > >
> > > > -G
> > > >
> > > >> -----Original Message-----
> > > >> From: news [mailto:news@sea.gmane.org] On Behalf Of hilz
> > > >> Sent: Monday, May 29, 2006 4:19 PM
> > > >> To: derby-user@db.apache.org
> > > >> Subject: Re: "generated by default" question
> > > >>
> > > >> I wish it was a bug!
> > > >> it seems this is by design, but i hope there is some other way to
> > > >> overcome this behavior.
> > > >>
> > > >> here is a quote from the docs at
> > > >>    http://db.apache.org/derby/docs/dev/ref/ref-single.html
> > > >> that show it is by design:
> > > >>
> > > >> <quote>
> > > >> RESTART WITH integer-constant specifies the next value to be
> > > >> generated
> > > >> for the identity column. RESTART WITH is useful for a table that
> > > >> has an
> > > >> identity column that was defined as GENERATED BY DEFAULT and that
> > > >> has a
> > > >> unique key defined on that identity column. Because GENERATED BY
> > > >> DEFAULT
> > > >> allows both manual inserts and system generated values, it is
> > > >> possible
> > > >> that manually inserted values can conflict with system generated
> > > >> values.
> > > >> To work around such conflicts, use the RESTART WITH syntax to
> specify
> > > >> the next value that will be generated for the identity column.
> > > >> Consider
> > > >> the following example, which involves a combination of
> automatically
> > > >> generated data and manually inserted data:
> > > >>
> > > >> CREATE TABLE tauto(i INT GENERATED BY DEFAULT AS IDENTITY, k INT)
> > > >> CREATE
> > > >> UNIQUE INDEX tautoInd ON tauto(i) INSERT INTO tauto(k) values 1,2
> > > >>
> > > >> The system will automatically generate values for the identity
> > > >> column.
> > > >> But now you need to manually insert some data into the identity
> > > >> column:
> > > >>
> > > >> INSERT INTO tauto VALUES (3,3) INSERT INTO tauto VALUES (4,4)
> INSERT
> > > >> INTO tauto VALUES (5,5)
> > > >>
> > > >> The identity column has used values 1 through 5 at this point.  If
> > > >> you
> > > >> now want the system to generate a value, the system will generate
> > > >> a 3,
> > > >> which will result in a unique key exception because the value 3 has
> > > >> already been manually inserted.  To compensate for the manual
> > > >> inserts,
> > > >> issue an ALTER TABLE statement for the identity column with
> > > >> RESTART WITH
> > > >> 6:
> > > >>
> > > >> ALTER TABLE tauto ALTER COLUMN i RESTART WITH 6
> > > >>
> > > >> ALTER TABLE does not affect any view that references the table
> being
> > > >> altered. This includes views that have an "*" in their SELECT
> > > >> list. You
> > > >> must drop and re-create those views if you wish them to return the
> > > >> new
> > > >> columns.
> > > >> </quote>
> > > >>
> > > >> Michael Segel wrote:
> > > >>> On Monday 29 May 2006 3:31 pm, hilz wrote:
> > > >>> After a quick glance,
> > > >>>
> > > >>> This looks like a bug.
> > > >>>
> > > >>> You should be able to insert your own values in the ID column,
> > > >>> which you
> > > >>
> > > >> do...
> > > >>
> > > >>> then on rows that are auto generated, they should chose an
> > > >>> incremental
> > > >>
> > > >> value.
> > > >>
> > > >>> Using your example, it should have trapped the error and then
> > > >>> tried to
> > > >>
> > > >> insert
> > > >>
> > > >>> using 2... until it found an integer value that was not in use.
> > > >>>
> > > >>> But hey, what do I know.
> > > >>> I'm sure someone is going to tell me that this functionality is
> > > >>> behaving
> > > >>
> > > >> per
> > > >>
> > > >>> spec....
> > > >>>
> > > >>> -G
> > > >>>
> > > >>>> Hi all.
> > > >>>> If i have a table A defined as follows:
> > > >>>>
> > > >>>> create table A
> > > >>>> (
> > > >>>> ID     INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
> > > >>>> NAME   VARCHAR(255)
> > > >>>> );
> > > >>>>
> > > >>>> then i do the following:
> > > >>>>
> > > >>>>      insert into A (ID, NAME) values (1,'hello 1');
> > > >>>>
> > > >>>> and then i do the following:
> > > >>>>
> > > >>>>      insert into A (NAME) values ('hello 2');
> > > >>>>
> > > >>>> I will get this error:
> > > >>>>
> > > >>>> The statement was aborted because it would have caused a
> > > >>>> duplicate key
> > > >>>> value in a unique or primary key constraint or unique index
> > > >>>> identified
> > > >>>> by 'SQL060529010004440' defined on 'A'.
> > > >>>>
> > > >>>> To avoid this, I will have to do :
> > > >>>>
> > > >>>>      alter table....RESTART WITH....
> > > >>>>
> > > >>>> Is there another way to make the autoincrement smart enough
to
> know
> > > >>
> > > >> that
> > > >>
> > > >>>> the value already exists and just generate a new value for
me?
> > > >>>> I find it odd to have to set the "restart with" to skip the
> > > >>>> values that
> > > >>>> i set manually.
> > > >>>>
> > > >>>> thanks for any help.
> > >
> > > Craig Russell
> > > Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
> > > 408 276-5638 mailto:Craig.Russell@sun.com
> > > P.S. A good JDO? O, Gasp!
>
> --
> --
> Michael Segel
> Principal
> Michael Segel Consulting Corp.
> derby@segel.com
> (312) 952-8175 [mobile]
>

Mime
View raw message