db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mamta Satoor <msat...@gmail.com>
Subject Re: [Derby-359]Skipping over user inserted values into GENERATED BY DEFAULT identity columns....
Date Wed, 21 Dec 2005 19:18:32 GMT
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

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.

Based on this, I believe that Satheesh's initial approach on solving this
bug is correct which is if the value generated causes a duplicate key
failure for the insert and then that generated value for that insert should
be consumed by the engine. The next attempt of the same insert should try to
use the next generated value. An eg to understand the SQL spec behavior
ij(CONNECTION0)> create table tauto(i int generated by default as identity,
k int);
0 rows inserted/updated/deleted
ij(CONNECTION0)> create unique index tautoInd on tauto(i);
0 rows inserted/updated/deleted
ij(CONNECTION0)> insert into tauto(k) values 1,2;
2 rows inserted/updated/deleted
ij(CONNECTION0)> select * from tauto;
I |K
1 |1
2 |2

2 rows selected
ij(CONNECTION0)> insert into tauto values (4,4);
1 row inserted/updated/deleted
ij(CONNECTION0)> insert into tauto(k) values 3;
1 row inserted/updated/deleted
ij(CONNECTION0)> insert into tauto(k) values 4; <=== Expected error. AND
ERROR 23505: The statement was aborted because it would have caused a
key value in a unique or primary key constraint or unique index identified
by 'T
AUTOIND' defined on 'TAUTO'.
ij(CONNECTION0)> insert into tauto(k) values 5; <=== DERBY SHOULD GENERATE 5

As for Dan's concern "If I pre-loaded a table with 100,000 rows with
consective values, then it's going to require 100,000 subsequent failing
inserts before one succeeds", I think Derby should add the ability to set
identity starting value by alter table which can be used after a load for

If anyone has objections/opinions on this, please send in your comments. In
the mean time, I will start looking at what it will take to have Derby
consume the value that it generates irrespective of whether the outer user
transaction fails or succeeds.


On 12/15/05, Bryan Pendleton <bpendleton@amberpoint.com> wrote:
> >>I would be happy if Derby can consume identity values even if current
> >>insert statement fails. For this case, some insert statements may fail
> >>when they generate a value that is already present. But subsequent
> >>inserts should pass.
> >
> > I wonder if the counter should be bumped to the max value on a failed
> > insert, there is only a problem if a unique index exists, so getting the
> > max will be fast.
> I'm not sure why the user would want *any* inserts to fail. If the
> database can figure out the current max, and generate a new value which
> is beyond that, thus causing the insert to succeed, then that seems like
> the best outcome.
> thanks,
> bryan

View raw message