RE: "generated by default" question

[Mike <ms...@segel.com>]

Craig,

I haven't looked at the code which generates an identity value. There are a
couple of ways Derby could be doing this and it would be best to clean up
that code.

The simplest solution would be to write an exception handler that will auto
increment the counter at each error and try to insert in the following
position. 

While this is simple, the downside is that it will have a performance hit.

Since this is an index column, why not walk the index until you have a node
where next node > (current_node +1)?  Or some derivative of this? Ie. Use
the index to find a gap in the ID numbers and use it. It could be recursive
and relatively "fast"....  Does this make sense?

More work, but better performance.

My suggestion is to hand this off to one of your support staff at either IBM
or Sun since both companies sell support.

But hey! What do I know? ;-)

> -----Original Message-----
> From: Craig.Russell@Sun.COM [mailto:Craig.Russell@Sun.COM]
> Sent: Tuesday, May 30, 2006 10:23 AM
> To: Derby Discussion
> Subject: Re: "generated by default" question
> 
> 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!