Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (asf.osuosl.org: local policy)
From: Michael Segel <derby@segel.com>
Reply-To: derby@segel.com
Organization: MSCC 
To: "Derby Discussion" <derby-user@db.apache.org>
Subject: Re: "generated by default" question
Date: Tue, 30 May 2006 13:09:19 -0500
User-Agent: KMail/1.8.2
References: <20060530144043.52834243A4@dbrack01.segel.com>
 <DD522070-780A-4C31-B095-0009E49EFEA2@SUN.com>
 <d9619e4a0605300902n5038df99x4fd6e909c4492e6@mail.gmail.com>
In-Reply-To: <d9619e4a0605300902n5038df99x4fd6e909c4492e6@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Message-Id: <200605301309.19884.derby@segel.com>

I tried to send this out earlier from a different machine...

I read the thread of articles, and unfortunately, you're wrong, or rather t=
he=20
material that you posted regarding the spec does not match this issue.

=46rom your earlier post:
=2D=3D-
I looked at the SQL 2000 spec to see what it says about generated keys.=20
=46ollowing are some lines copied from the Identity columns section and fro=
m=20
Sequence generators section. The value generation for identity column follo=
ws=20
the gules of Sequence generator. And in the Sequence generator section, the=
=20
spec says that value generation is done in a transaction of its own and is=
=20
not associated with the outside user transaction.=20
=20
4.14.4 Identity columns
=20
An identity column is associated with an internal sequence generator=20
SG. Let IC be the identity column of BT . When a row R is presented for=20
insertion into BT , if R does not contain a column corresponding to IC, the=
n=20
the value V for IC in the row inserted into BT is obtained by applying the=
=20
General Rules of Subclause 9.21, ''Generation of the next value of a sequen=
ce=20
generator'', to SG . The definition of an identity column may specify=20
GENERATED ALWAYS or GENERATED BY DEFAULT.
[\n added by MJS]
4.21 Sequence generators
=20
Changes to the current base value of a sequence generator are not controlle=
d=20
by SQL-transactions; therefore, commits and rollbacks of SQL-transactions=20
have no effect on the current base value of a sequence generator.=20

=2D=3D-
=46irst 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.

Second...
4.21 doesn't talk about the issue at hand. What 4.21 talks about is that wh=
en=20
you implement a sequence generator, it needs to be outside of any=20
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 t=
he=20
identity values of 4 and 5 respectively. The next value in the identity=20
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 inser=
ted=20
without using the identity value, and Derby doesn't know how to generate th=
e=20
next identity value.

That sir, is a bug.
And no, the SQL 2000 spec, as presented in this discussion does not concur=
=20
with how derby is implementing their auto sequence generation.

So, when will either Sun or IBM fix this? ;-)

=2DG

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!

=2D-=20
=2D-
Michael Segel
Principal=20
Michael Segel Consulting Corp.
derby@segel.com
(312) 952-8175 [mobile]