db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Satheesh Bandaram <sathe...@Sourcery.Org>
Subject Re: [Derby-359]Skipping over user inserted values into GENERATED BY DEFAULT identity columns....
Date Wed, 21 Dec 2005 22:27:55 GMT
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<body bgcolor="#ffffff" text="#000000">
Mamta Satoor wrote:<br>
  <div>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. </div>
  <div><font face="NewCenturySchlbk-Bold">
  <p align="left"><strong>4.14.4 Identity columns</strong></p>
  <font face="NewCenturySchlbk-Roman" size="2">
  <p align="left">An identity column is associated with an internal
sequence generator </p>
  </font><i><font face="NewCenturySchlbk-Italic" size="2">SG</font></i></font><font
 face="NewCenturySchlbk-Roman" size="2">. Let </font>
  <i><font face="NewCenturySchlbk-Italic" size="2">IC </font></i><font
 face="NewCenturySchlbk-Roman" size="2">be the identity column of </font><i><font
 face="NewCenturySchlbk-Italic" size="2">BT</font></i><font
 face="NewCenturySchlbk-Roman" size="2">
. When a row </font><i><font face="NewCenturySchlbk-Italic" size="2">R
  </font></i><font face="NewCenturySchlbk-Roman" size="2">is presented
for insertion into </font><i><font face="NewCenturySchlbk-Italic"
  </i><font face="NewCenturySchlbk-Roman" size="2">, if </font><i><font
 face="NewCenturySchlbk-Italic" size="2">R </font></i><font
 face="NewCenturySchlbk-Roman" size="2">does not contain a column
corresponding to </font><i>
  <font face="NewCenturySchlbk-Italic" size="2">IC</font></i><font
 face="NewCenturySchlbk-Roman" size="2">, then the value </font><i><font
 face="NewCenturySchlbk-Italic" size="2">V </font></i><font
 face="NewCenturySchlbk-Roman" size="2">
for </font><i><font face="NewCenturySchlbk-Italic" size="2">IC </font></i><font
 face="NewCenturySchlbk-Roman" size="2">in the row inserted into </font><i><font
 face="NewCenturySchlbk-Italic" size="2">BT </font></i><font
 face="NewCenturySchlbk-Roman" size="2">
is obtained by applying the General Rules of Subclause 9.21,
''Generation of the next value of a sequence generator'', to </font><i><font
 face="NewCenturySchlbk-Italic" size="2">SG</font></i><font
 face="NewCenturySchlbk-Roman" size="2">
. The de</font><font face="NewCenturySchlbk-Roman" size="2">fi</font><font
 face="NewCenturySchlbk-Roman" size="2">nition of an identity column
may specify GENERATED ALWAYS or GENERATED BY DEFAULT.</font></div>
  <div><font face="NewCenturySchlbk-Roman" size="2"><b><font
 face="NewCenturySchlbk-Bold" size="4">
  <p align="left">4.21 Sequence generators</p>
  </font></b></font><font face="NewCenturySchlbk-Roman" size="2"></font>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&nbsp;should be consumed by the engine.&nbsp;The next attempt of the same
insert should try to use the next generated value. An eg to understand
the SQL spec behavior
  <div>ij(CONNECTION0)&gt; create table tauto(i int generated by
default as identity, k int); <br>
0 rows inserted/updated/deleted <br>
ij(CONNECTION0)&gt; create unique index tautoInd on tauto(i); <br>
0 rows inserted/updated/deleted <br>
ij(CONNECTION0)&gt; insert into tauto(k) values 1,2; <br>
2 rows inserted/updated/deleted <br>
ij(CONNECTION0)&gt; select * from tauto; <br>
I |K <br>
----------------------- <br>
1 |1 <br>
2 |2 <br>
2 rows selected <br>
ij(CONNECTION0)&gt; insert into tauto values (4,4); <br>
1 row inserted/updated/deleted <br>
ij(CONNECTION0)&gt; insert into tauto(k) values 3; <br>
1 row inserted/updated/deleted <br>
ij(CONNECTION0)&gt; insert into tauto(k) values 4; &lt;=== Expected
ERROR 23505: The statement was aborted because it would have caused a
duplicate <br>
key value in a unique or primary key constraint or unique index
identified by 'T <br>
AUTOIND' defined on 'TAUTO'. <br>
ij(CONNECTION0)&gt; insert into tauto(k) values 5; &lt;=== DERBY SHOULD
  <div>As for&nbsp;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 example.
  <div>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.
  <div><span class="gmail_quote">On 12/15/05, <b
 class="gmail_sendername">Bryan Pendleton</b> &lt;<a
 onclick="return top.js.OpenExtLink(window,event,this)"
 href="mailto:bpendleton@amberpoint.com" target="_blank">bpendleton@amberpoint.com
  </a>&gt; wrote:</span>
  <blockquote class="gmail_quote"
 style="border-left: 1px solid rgb(204, 204, 204); margin: 0px 0px 0px 0.8ex; padding-left:
would be happy if Derby can consume identity values even if current<br>
&gt;&gt;insert statement fails. For this case, some insert statements
may fail <br>
&gt;&gt;when they generate a value that is already present. But
&gt;&gt;inserts should pass.<br>
&gt; I wonder if the counter should be bumped to the max value on a
&gt; insert, there is only a problem if a unique index exists, so
getting the <br>
&gt; max will be fast.<br>
I'm not sure why the user would want *any* inserts to fail. If the<br>
database can figure out the current max, and generate a new value which<br>
is beyond that, thus causing the insert to succeed, then that seems
like <br>
the best outcome.<br>

View raw message