You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by hilz <hs...@hotmail.com> on 2006/05/29 22:31:20 UTC
"generated by default" question
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.
RE: "generated by default" question
Posted by de...@segel.com.
> -----Original Message-----
> From: Daniel John Debrunner [mailto:djd@apache.org]
> Sent: Thursday, June 29, 2006 3:22 PM
> To: Derby Discussion
> Subject: Re: "generated by default" question
>
> derby@segel.com wrote:
>
> >
> > [mjs]
> > Bzzzt!
> > No.
> >
> > Already solved your first example. Just reset the sequence if the row
> has a
> > value in the identity column between CBV and MAX.
> > Oops! Guess that blows away your theory.
>
> I don't have any theory, I was pointing out that the proposed "very
> simple solution" had some issues. To become workable it needs some
> additional changes, potentially like the ones you described, thus the
> solution becomes more complex. I just didn't want someone to start
> implementing it and assume it would automatically be accepted as a
> solution.
>
[mjs]
The solution really hasn't changed. In my first suggestion, I assumed that
your MAX value couldn't be set and would be 2^63 -1 since the sequence uses
an INTEGER (8 Bytes).
If you want to allow for the Derby Sequence to implement changing the MAX
value, then you need to account for it in the solution.
True, I did ignore the possibility of updating the identity value.
Most likely because I've never had a use case where someone did update
identity values.
The solution is truly trivial.
The issue occurs when you cycle the sequence.
That's a more complex issue and more than likely, you'll have to work
through the backing index to find the next open value. It can be done, but I
don't know how efficient the code could be. (Meaning that the cost of the
solution per transaction doesn't justify implementing it.)
> This is how open source works, people suggest solutions, others see
> issues with them, others provide additional input, and hopefully a good
> solution emerges from the community.
>
> Dan.
[mjs]
Well. Sure.
'Cept for one minor problem.
Up until now, the push back from mainly Sun posters has been that this isn't
really a bug and that there's nothing wrong with the current implementation.
Now everyone is entitled to their opinion, however what makes this really
disappointing is that both IBM and SUN sell commercial versions of Derby.
(Cloudscape and JavaDB). I believe that both are selling support contracts
as well for bug fixes. This should be considered either a bug, a product
defect, or a design defect. Note too that this same bug came up last around
last Dec timeframe....
The solution I suggest is a reverse engineering of IBM (Informix) IDS's
Serial data type.
The reason that it's so effective is that during any insert in to a table
that has an identity column defined is that you'll have the row about to be
inserted in memory, and the sequence generator for that table. (You don't
need to use the index at all.) [Read: No need to flush pages in memory
beyond what occurs within a normal insert.]
Going on an assumption, the identity backing index is a B+ Tree?
If so, then you can walk the tree to find the next gap in the numbers. Not
sure how fast this would be but it's doable.
Re: "generated by default" question
Posted by Daniel John Debrunner <dj...@apache.org>.
derby@segel.com wrote:
>
> [mjs]
> Bzzzt!
> No.
>
> Already solved your first example. Just reset the sequence if the row has a
> value in the identity column between CBV and MAX.
> Oops! Guess that blows away your theory.
I don't have any theory, I was pointing out that the proposed "very
simple solution" had some issues. To become workable it needs some
additional changes, potentially like the ones you described, thus the
solution becomes more complex. I just didn't want someone to start
implementing it and assume it would automatically be accepted as a solution.
This is how open source works, people suggest solutions, others see
issues with them, others provide additional input, and hopefully a good
solution emerges from the community.
Dan.
RE: "generated by default" question
Posted by de...@segel.com.
> -----Original Message-----
> From: Daniel John Debrunner [mailto:djd@apache.org]
> Sent: Thursday, June 29, 2006 1:37 PM
> To: Derby Discussion
> Subject: Re: "generated by default" question
>
> --- derby@segel.com wrote:
>
> >>
> >> There is a very simple solution.
>
>
> [solution details snipped]
>
>
> >>
> >> This is the simplest solution, lowest cost solution,
> >> and doesn't violate the
> >> definition of an SQL sequence.
>
>
> Tthis solution creates a couple different
> problems that are probably unexpected by the user.
>
[mjs]
Not really.
You need to think this through...
First, lets all be clear that we're talking about creating a solution that
will work in the general case. No matter what solution is implemented,
someone, somewhere at some time will figure out a way to break it. Having
said that... the goal is to find a solution that will work in the general
case under reasonable circumstances.
The current implementation of an Identity column in Derby is really more
problematic in that for any insert on a table that has an identity column,
you have to then trap for the specific exception of a non unique value in
the identity index and then figure out your next move....
(Not a good idea.)
What are you going to do?
In each program, write the logic to reset the sequence number? (NOT GOOD)
In each program, loop until you can successfully insert a row? (NOT GOOD)
> 1) An insert with a non-generated value near or at the
> end of the range of generated values will exhaust the
> generator much earlier, leading to failed inserts that
> would succeed under the current scheme.
> E.g. if the range of of the generator was 1-10
> and the CBV was 3, then inserting a non-generated
> value of 10 means that no more inserts with generated
> values would work. In the current scheme six more
> inserts would succed (4-9).
>
[mjs] Ok, I was working under the assumption that you couldn't set the max
value of the sequence within Derby. A simple fix would be to check for the
condition "if the inserted value is larger than the maximum allowable
sequence number, then ignore..."
Thus in your example, nothing happens to the sequence if you insert a row
with a value > 10.
But your case does bring up an important point.
The simple solution to guarantee a unique value will only work for the first
cycle. This is true of IBM's IDS too. Using a Serial8, if you insert a row
with a value of 2^63 -1, then you've gone through the first cycle.
In your example, if you inserted a row at 10, then you've hit the last value
in the sequence and if you reset the sequence number, you're going to have
problems.
So in the real world, if you have an integer value for your sequence, you
will have the ability to insert a lot of rows prior to having a problem.
Sure, you can have a joker who throws out the 2^63 -1 number, but that is
not the norm....
Remember we're looking for a solution that works best in most cases.
> 2) An insert with a a non-generated value that is
> subsequently deleted or updated before any insert with a
> generated value affects the generator when under the
> current scheme it would not. This will reduce the
> range of available values when there is no need to.
> E.g. if the range of of the generator was 1-10
> and the CBV was 3, then inserting a non-generated
> value of 10 means that no more inserts with generated
> values would work, even if the row with 10 is deleted
> before any other inserts occur. In the current scheme
> seven more inserts would succed (4-10).
>
[mjs]
Bzzzt!
No.
Already solved your first example. Just reset the sequence if the row has a
value in the identity column between CBV and MAX.
Oops! Guess that blows away your theory.
Of course you did point out another potential problem. What happens if you
update the value in the identity column. Again there you'd have to perform
the same check.
> So solved one scenario and caused (at least) two new ones.
>
> Dan.
>
[mjs]
Sigh.
Must be that "new math" that was hyped in the 80's and 90's at work...
The current implementation fails miserably.
Suppose you create a table with an identity column and it has the "GENERATE
BY DEFAULT" option selected.
You insert rows 1... 10 using the default sequence generator. Next you have
someone inserting data from another source that already has values in the
identity column. Lets say starting at 100 and going all the way through
10,100.
What happens when you want to use the sequence generator?
Well sure, you can now insert rows 11 through 99.
But then look what happens.
You then have to trap for the exception and retry to insert the row.
This has to be written in every single app that hits your database.
So if you have only 1 app hitting your database, it will be stuck trying to
insert a row in to the database for the 10,000 times trying to find a row.
(Now think about multiple apps. Lots of unnecessary overhead.)
Now that's really good logic at work.
Oh and what about having your app reset the sequence number after a failure?
Well, you run in to the same issue. You need to find the next available row.
There are a couple of ways, however my posted solution is the simplest and
fastest method.
Hey don't take my word. Check out how other databases which are also SQL
compliant handle this.
The bottom line: If I can't trust Derby to auto generate a value and
successfully insert a row, then I can't rely on this function will not use
it.
Seems to me, you're just too darn lazy to fix this bug.
(Note: IBM does sell support contracts for Derby/Cloudscape. If there is
anyone who has Cloudscape on their PPA, feel free to log a call with support
and make it a sev 1. ;-)
But hey! What do I know?
I've been called a troll over this one. ;-)
Initial problem still exists. Proposed solution (with that mod) still holds
true.
NEXT!
Re: "generated by default" question
Posted by Daniel John Debrunner <dj...@apache.org>.
--- derby@segel.com wrote:
>>
>> There is a very simple solution.
[solution details snipped]
>>
>> This is the simplest solution, lowest cost solution,
>> and doesn't violate the
>> definition of an SQL sequence.
Tthis solution creates a couple different
problems that are probably unexpected by the user.
1) An insert with a non-generated value near or at the
end of the range of generated values will exhaust the
generator much earlier, leading to failed inserts that
would succeed under the current scheme.
E.g. if the range of of the generator was 1-10
and the CBV was 3, then inserting a non-generated
value of 10 means that no more inserts with generated
values would work. In the current scheme six more
inserts would succed (4-9).
2) An insert with a a non-generated value that is
subsequently deleted or updated before any insert with a
generated value affects the generator when under the
current scheme it would not. This will reduce the
range of available values when there is no need to.
E.g. if the range of of the generator was 1-10
and the CBV was 3, then inserting a non-generated
value of 10 means that no more inserts with generated
values would work, even if the row with 10 is deleted
before any other inserts occur. In the current scheme
seven more inserts would succed (4-10).
So solved one scenario and caused (at least) two new ones.
Dan.
RE: "generated by default" question
Posted by de...@segel.com.
Uhm, not to beat a dead horse...
Some feel that Derby is behaving properly, others, like myself and you feel
that Derby is not adequately performing.
There is a very simple solution.
When you have an insert on a table with an identity column, which has
GENERATE BY DEFAULT ..., if the row can be inserted, if the row contains a
value in the identity column and its value is greater than the CBV (current
base value) in the sequence, then reset the current base value to the
inserted value. (This will ensure that the next number from the sequence
will be the largest value in the table and should be inserted without
generating an exception.)
NOTE: When I say generate an exception, I mean specifically that you will
not get an exception of the sequence value not being unique to the table.
Now if you don't believe me, then we can always bet bottle of scotches.
And you can thank IBM's IDS for the solution. Its how their SERIAL/SERIAL8
datatypes work. [A Serial Data type is an integer that has an associated
sequence and identity index as part of the data type.]
This is the simplest solution, lowest cost solution, and doesn't violate the
definition of an SQL sequence.
But hey! What do I know? ;-)
-G
> -----Original Message-----
> From: Jimisola Laursen [mailto:lists@jimisola.com]
> Sent: Wednesday, June 28, 2006 7:05 PM
> To: derby-user@db.apache.org
> Subject: Re: "generated by default" question
>
>
> I'll second that as it sure does. We just switched from HSQLDB to Derby
> due
> to Derby's better handling of isolation levels, but this issue is really
> time consuming as we have to rewrite many of our tests.
>
> Jimisola
> --
> View this message in context: http://www.nabble.com/%22generated-by-
> default%22-question-tf1701190.html#a5095042
> Sent from the Apache Derby Users forum at Nabble.com.
Re: "generated by default" question
Posted by Jimisola Laursen <li...@jimisola.com>.
I'll second that as it sure does. We just switched from HSQLDB to Derby due
to Derby's better handling of isolation levels, but this issue is really
time consuming as we have to rewrite many of our tests.
Jimisola
--
View this message in context: http://www.nabble.com/%22generated-by-default%22-question-tf1701190.html#a5095042
Sent from the Apache Derby Users forum at Nabble.com.
Re: "generated by default" question
Posted by hilz <hs...@hotmail.com>.
Michael Segel wrote:
> Right.
>
> I don't think hilz was really suggesting piracy.
>
Thank you very much..
I wasn't asking anyone to copy anything.
I was just suggesting if someone would look at the HSQLDB code and see
how they do it to get an idea. That's all. Would that still be against
whatever license HSQLDB is under?
RE: "generated by default" question
Posted by Michael Segel <ms...@segel.com>.
Right.
I don't think hilz was really suggesting piracy.
Its just as easy as to test how Informix/DB2/... handles this and to them
mimic that behavior.
You've already made a good suggestion that would be "easy" to implement.
I believe that there may be a more elegant solution if you look at the
associated index, however I'm not sure on how efficient it would be.
(Meaning, I've thought a little bit about it, just haven't really played
with the idea yet...)
> -----Original Message-----
> From: Daniel John Debrunner [mailto:djd@apache.org]
> Sent: Friday, June 09, 2006 10:37 AM
> To: Derby Discussion
> Subject: Re: "generated by default" question
>
> hilz wrote:
> > Well...
> > A good solution would probably be if someone would look at the HSQLDB
> > code and see how they do it because it works like a charm over there!
>
> Anyone doing this must adhere to the licence of HSQLDB.
>
> Just because projects are open-source does not mean one can copy code
> between them, each licene must be respected.
>
> Dan.
>
Re: "generated by default" question
Posted by Daniel John Debrunner <dj...@apache.org>.
hilz wrote:
> Well...
> A good solution would probably be if someone would look at the HSQLDB
> code and see how they do it because it works like a charm over there!
Anyone doing this must adhere to the licence of HSQLDB.
Just because projects are open-source does not mean one can copy code
between them, each licene must be respected.
Dan.
Re: "generated by default" question
Posted by Michael Segel <de...@segel.com>.
Craig,
On a hunch, I did a quick test.
Since I have Informix up and running,
I created a table foo with two columns.
The first is a serial column, the second is a text string.
The table already had a row
id desc
1 Test 1
I then did the following statement(s):
INSERT INTO foo VALUES (2147483647, "Trying to find the top");
INSERT INTO foo VALUES (0, "Trying to find the top");
INSERT INTO foo VALUES (0, "Trying to find the top");
SELECT * FROM foo;
This errored out on the second statement with a duplicate row.
I ran the select again...
id desc
1 Test 1
2147483647 Trying to find the t
I then tried to run the the following:
INSERT INTO foo VALUES (0, "Trying to find the top");
INSERT INTO foo VALUES (0, "Trying to find the top");
SELECT * FROM foo;
id desc
1 Test 1
2147483647 Trying to find the t
2 Trying to find the t
3 Trying to find the t
So as you can see, if the sequence is cycled, then it doesn't catch the error.
Now my take...
This too is a bug. However, the required fix is a tad more complex and the
odds of cycling through 2 billion rows was considered a low probability.
Using a sequence of a LONG vs an INTEGER, you have (2^63) -1 rows to cycle
through. (8 Bytes vs 4 Bytes). 8 Bytes ~= 9.22 X 10^18 potential values.
Since I don't own a copy of the spec, I don't know if they get in to the
detail on how to handle cycling a sequence.
And yes, the spec deals with sequences, and with an Identity Column , yet
doesn't detail how to handle situations when the sequence generates a number
that is already in use.
The approach by Informix works for the first cycle. To handle issues beyond
that, then you have to get creative.... ;-)
But hey! What do I know? ;-)
BTW, I think if you look at the index on the identity column, you may find a
way to handle the cycles and trap for the constraint so that the only time
you fail is if the table is full.
--
--
Michael Segel
Principal
Michael Segel Consulting Corp.
derby@segel.com
(312) 952-8175 [mobile]
RE: "generated by default" question
Posted by Michael Segel <ms...@segel.com>.
Andrew,
Its disappointing that you just don't want to produce a product that works
and works well.
If flight control systems were designed by programmers like yourself, we'd
be in a world of hurt. (Don't even get me started on weapon's control
systems either.... ;-)
Now I do realize that not everyone here is a software engineer, nor adheres
to the disciplines taught in software engineering programs. Nor did everyone
here have to sit through hours of boring math classes taught by professors
who had poor communication skills.
Don't you take pride in your code?
I mean, heck, it's a simple fix. Low overhead and makes Derby more reliable.
I bet you're the type of guy who owns a Fred Flinstone car. You know, the
type where you have a rusted floorboard that when you want to stop, you put
your feet down?
I'm more of a 4 wheel anti-lock disk break kind of guy, where I know that
they'll work and work well. Cause lord knows, I don't want to have to rely
on my air bag to stay alive!
Nuf said.
-G
> -----Original Message-----
> From: Andrew McIntyre [mailto:mcintyre.a@gmail.com]
> Sent: Friday, June 16, 2006 3:04 AM
> To: Derby Discussion; msegel@segel.com
> Subject: Re: "generated by default" question
>
> On 6/16/06, Michael Segel <ms...@segel.com> wrote:
> >
> > > I think we'll just have to agree to disagree.
> >
> > Whether you agree or disagree doesn't change the fact that this is a
> bug.
>
> Whether or not this is a bug is not a 'fact'. That there is
> disagreement would imply that such a determination lies more in the
> realm of opinion.
>
> > Here's a free clue. If I can't trust Derby to autogenerate a value by
> default
> > that will insert a row which has no other problems or contraints, then I
> > can't use Derby.
>
> Are you trolling for responses here? Sure seems like it.
>
> Hey folks: don't feed a troll.
>
> A database system should be expected to issue an error when a sequence
> generator hits an unusual condition. Unexpected data in a table with a
> primary key that is an identity column which is generated by default
> is but one of many such possibilities.
>
> cheers,
> andrew
Re: "generated by default" question
Posted by Andrew McIntyre <mc...@gmail.com>.
On 6/16/06, Michael Segel <ms...@segel.com> wrote:
>
> > I think we'll just have to agree to disagree.
>
> Whether you agree or disagree doesn't change the fact that this is a bug.
Whether or not this is a bug is not a 'fact'. That there is
disagreement would imply that such a determination lies more in the
realm of opinion.
> Here's a free clue. If I can't trust Derby to autogenerate a value by default
> that will insert a row which has no other problems or contraints, then I
> can't use Derby.
Are you trolling for responses here? Sure seems like it.
Hey folks: don't feed a troll.
A database system should be expected to issue an error when a sequence
generator hits an unusual condition. Unexpected data in a table with a
primary key that is an identity column which is generated by default
is but one of many such possibilities.
cheers,
andrew
Re: "generated by default" question
Posted by Michael Segel <ms...@segel.com>.
On Friday 16 June 2006 1:29 am, Vic Ricker wrote:
> Michael Segel wrote:
> > Look, if you grok the math, you'll see that if you return a result from
> > the sequence that causes an error, then you've got a bug, when there's
> > another number within the result set that doesn't cause the exception.
>
> I don't know how you can call it a bug. The behavior is documented.
> Just because it doesn't work the way you think it should doesn't make it
> wrong. There are other databases like PostgreSQL that behave similarly.
> I suppose they are buggy as well?
>
Yes, when the spec is written the way it is, the math implies that the
solution for N is a set of values. If Derby doesn't return a value that will
result in a successful insert, then yes it is a bug / design defect.
Can I help it if those who read the spec don't grok the implications of the
math?
Look at it this way.
The spec didn't say "Choose the Min(N) where N is the solution set to ...".
It said to choose a nonnegative integer that was in between the CBV and the
MAX() that had not been used.
So obviously you'll want to return the Min(N) which you can guarantee will
work.... Which is what I'm suggesting.
If you can't return a value from the sequence that does work then you've
failed the spec.
> > Again, here's the simple and *EFFICIENT* solution....
>
> [SNIP]
>
> It sounds pretty expensive, especially if you're doing a lot of
> inserts/updates.
>
Uhm, you really think so?
Try it, you'll find out that its cheap. really.
You'll need the following:
1) X, the value inserted in to the table in place of the default value.
2) the CSV which is part of the sequence object, which you'll have in memory.
3) a MUTEX lock or set of locks so that no one can perform an insert until you
finish. (Note. if you hold a mutex lock on the sequence, then those who need
a default value are blocked. and then those who want to insert a value of X >
CSV are also blocked. Only those who are inserting with a value X < CSV are
not blocked.
And that's pretty much it.
And they will all be in memory and should be in active pages.
Now I'm making an assumption that Derby will still contain a copy of the input
values of the corresponding insert when you hit an after insert condition...
if not, then you need some small pre-insert logic.
But to your point....
You have one additional conditional check for those inserts that use the auto
generation of a sequence value. If you don't you just use the sequence
generator....
You have an additional check or condition to see if the insert that contains a
value X for an identity column has a value of X > CBV.
If you dont, then you just do the insert....
If this is the case, then you have the cost of holding a mutex lock, then
updating the variable, and then releasing the lock.
Not very expensive at all.
Nope that about sums it up. Not very expensive. I guess it all depends on how
you implement this trival logic.
But hey! What do I know? ;-)
>
> I think we'll just have to agree to disagree.
Whether you agree or disagree doesn't change the fact that this is a bug.
Here's a free clue. If I can't trust Derby to autogenerate a value by default
that will insert a row which has no other problems or contraints, then I
can't use Derby.
Re: "generated by default" question
Posted by Vic Ricker <vi...@ricker.us>.
Michael Segel wrote:
> Look, if you grok the math, you'll see that if you return a result from the
> sequence that causes an error, then you've got a bug, when there's another
> number within the result set that doesn't cause the exception.
I don't know how you can call it a bug. The behavior is documented.
Just because it doesn't work the way you think it should doesn't make it
wrong. There are other databases like PostgreSQL that behave similarly.
I suppose they are buggy as well?
>
> Again, here's the simple and *EFFICIENT* solution....
>
[SNIP]
It sounds pretty expensive, especially if you're doing a lot of
inserts/updates.
I think we'll just have to agree to disagree.
Re: "generated by default" question
Posted by Michael Segel <ms...@segel.com>.
On Friday 16 June 2006 12:10 am, Vic Ricker wrote:
> Daniel Noll wrote:
> > Ah. I was making the assumption that the indexes were implemented
> > "properly." For instance, an index should keep a certain amount in
> > memory in order to have a reasonable chance of running quickly. So the
> > max and min should already be in-memory.
> >
> > I take it that Derby doesn't do this?
>
> The column doesn't necessarily have to have an index or even be unique.
> An insert will only fail when there's a unique constraint on the
> column and the sequence generator returns a value that's already been
> used.
However, when you have an identity column, or even a unique constraint, you
have what is considered a "backing index". That is an index created by Derby
so that it could ensure uniqueness.
Again, this is irrelevant. You don't need to look at the index. You only need
to know the CBV and the value that is being inserted in to the identity
column.
Re: "generated by default" question
Posted by Michael Segel <ms...@segel.com>.
On Thursday 15 June 2006 8:52 pm, Daniel Noll wrote:
Sigh.
If you're doing an insert on a table with an identity column, then yes I
believe that the index would be in memory. Or a memory page that was cached.
But you're missing the point.
When do you get the max() value of the index? ;-)
And why do you need it? ;-)
...
The point is that you don't need it.
If the insert has a value X for the identity column and X > CSV, then set CSV
= X;
It works every time.
> Michael Segel wrote:
> >> Presumably he column would be indexed if you're doing operations like
> >> max() on it anyway, so efficiency isn't the problem. I suspect the
> >> problem would be what to do when two users tried to insert rows at
> >> around the same time. The second would fail to commit the transaction
> >> and have to try again.
> >>
> >> Daniel
> >
> > No.
> > First, you're still returning a value from the sequence that will cause
> > an exception. So you have overhead. Also you'd have a lot harder time
> > trying to manage the inserts. Also, you have to consider that its cheaper
> > to get a value from memory than it is to apply the max() function on a
> > value from the index.
>
> Ah. I was making the assumption that the indexes were implemented
> "properly." For instance, an index should keep a certain amount in
> memory in order to have a reasonable chance of running quickly. So the
> max and min should already be in-memory.
>
> I take it that Derby doesn't do this?
>
> Daniel
Re: "generated by default" question
Posted by Vic Ricker <vi...@ricker.us>.
Daniel Noll wrote:
>
> Ah. I was making the assumption that the indexes were implemented
> "properly." For instance, an index should keep a certain amount in
> memory in order to have a reasonable chance of running quickly. So the
> max and min should already be in-memory.
>
> I take it that Derby doesn't do this?
The column doesn't necessarily have to have an index or even be unique.
An insert will only fail when there's a unique constraint on the
column and the sequence generator returns a value that's already been
used.
Re: "generated by default" question
Posted by Daniel Noll <da...@nuix.com.au>.
Michael Segel wrote:
>> Presumably he column would be indexed if you're doing operations like
>> max() on it anyway, so efficiency isn't the problem. I suspect the
>> problem would be what to do when two users tried to insert rows at
>> around the same time. The second would fail to commit the transaction
>> and have to try again.
>>
>> Daniel
> No.
> First, you're still returning a value from the sequence that will cause an
> exception. So you have overhead. Also you'd have a lot harder time trying
> to manage the inserts. Also, you have to consider that its cheaper to get a
> value from memory than it is to apply the max() function on a value from the
> index.
Ah. I was making the assumption that the indexes were implemented
"properly." For instance, an index should keep a certain amount in
memory in order to have a reasonable chance of running quickly. So the
max and min should already be in-memory.
I take it that Derby doesn't do this?
Daniel
--
Daniel Noll
Nuix Pty Ltd
Suite 79, 89 Jones St, Ultimo NSW 2007, Australia Ph: +61 2 9280 0699
Web: http://www.nuix.com.au/ Fax: +61 2 9212 6902
This message is intended only for the named recipient. If you are not
the intended recipient you are notified that disclosing, copying,
distributing or taking any action in reliance on the contents of this
message or attachment is strictly prohibited.
Re: "generated by default" question
Posted by Michael Segel <ms...@segel.com>.
On Thursday 15 June 2006 6:38 pm, Daniel Noll wrote:
> Michael Segel wrote:
> > Uhm,
> >
> > I'm sorry, but where did I ever say 'select max()'?
> > Hint: I didn't because its not that efficient.
>
> Presumably he column would be indexed if you're doing operations like
> max() on it anyway, so efficiency isn't the problem. I suspect the
> problem would be what to do when two users tried to insert rows at
> around the same time. The second would fail to commit the transaction
> and have to try again.
>
> Daniel
No.
First, you're still returning a value from the sequence that will cause an
exception. So you have overhead. Also you'd have a lot harder time trying
to manage the inserts. Also, you have to consider that its cheaper to get a
value from memory than it is to apply the max() function on a value from the
index.
Again the easiest way is to update the sequence when you have a successful
insert where there is a value in the identity column greater than the current
base value. Everything is in memory and of course you'll have to deal with a
mutex lock on the sequence while you're doing the update.
again pretty trivial or rather straight forward....
Re: "generated by default" question
Posted by Daniel Noll <da...@nuix.com.au>.
Michael Segel wrote:
> Uhm,
>
> I'm sorry, but where did I ever say 'select max()'?
> Hint: I didn't because its not that efficient.
Presumably he column would be indexed if you're doing operations like
max() on it anyway, so efficiency isn't the problem. I suspect the
problem would be what to do when two users tried to insert rows at
around the same time. The second would fail to commit the transaction
and have to try again.
Daniel
--
Daniel Noll
Nuix Pty Ltd
Suite 79, 89 Jones St, Ultimo NSW 2007, Australia Ph: +61 2 9280 0699
Web: http://www.nuix.com.au/ Fax: +61 2 9212 6902
This message is intended only for the named recipient. If you are not
the intended recipient you are notified that disclosing, copying,
distributing or taking any action in reliance on the contents of this
message or attachment is strictly prohibited.
RE: "generated by default" question
Posted by Michael Segel <ms...@segel.com>.
Uhm,
I'm sorry, but where did I ever say 'select max()'?
Hint: I didn't because its not that efficient.
Daniel made that suggestion, however I think it was off the cuff and was
meant to address error trapping.
Look, if you grok the math, you'll see that if you return a result from the
sequence that causes an error, then you've got a bug, when there's another
number within the result set that doesn't cause the exception.
Again, here's the simple and *EFFICIENT* solution....
When you have an insert statement against a table with an identity column...
If the insert statement has a value for the identity column and
If the value is > than CBV, then
In an after insert clause, assign CBV to be that value or the next
largest non-negative integer value that satisfies the equation in 9.21
paragraph 3.
That simple. Every value you need should be in memory at the time of the
insert.
The logic is simple. Yes you want to return the MIN(N) where N is the set of
solution values in 9.21. However you need to ensure that the MIN(N) will
return a valid number that can be used for an insert.
So if you think about it, the only time you'll have a problem is if the user
inserts a row that already has a value and that value is larger than the
last sequence number generated. (CBV). Therefore, if after the insert is
completed, you grab a mutex lock on the sequence generator, and then reset
the CBV, you'll always know that the MIN(N) will work.
That simple.
Now, if you want to do a work around, then you need to do an after insert
trigger and a stored procedure to handle the logic. Again all the data
should be in memory, so it should be a snap.
But hey, what do I know? ;-)
If you don't believe me, just check out how IBM's IDS handles a serial /
serial8 data type.
-G
> -----Original Message-----
> From: Vic Ricker [mailto:vic@ricker.us]
> Sent: Thursday, June 15, 2006 2:05 PM
> To: Derby Discussion
> Subject: Re: "generated by default" question
>
> Sorry Michael, I have to agree with Bernt and Craig. I think, for
> performance reasons, it doesn't make sense to have it select max() on
> the identity columns, especially since they're not guaranteed to be
> indexed. I don't think you'd want Derby doing a table scan just because
> you previously inserted a value that collided with the sequence
> generator. I think it would probably make more sense to build the
> "smarts" into your application rather than relying on Derby to do
> something that could cause performance problems.
>
> -Vic
>
> P.S. The first CPU that I learned assembly language on was the 6809. It
> was cool.
>
> Michael Segel wrote:
> > Sigh.
> >
> > Don't they teach math in engineering anymore?
> >
> > Lets try this one more time.
> >
> > In 9.21, if N does exist, then N represents a solution set of potential
> > values.
> >
> > In your interpretation, you are *implicitly* adding an additional
> boundary
> > that the sequence returns the MIN(N) regardless of the overall
> > implementation;
> >
> > That is not part of the spec!
> >
> > Thus, if you fail to insert a value in to an identity row and the
> failure is
> > due to the fact that the sequence returned a value that was in use,
> while
> > there were other values within the solution set N, it is a bug.
> >
> > However, if you could guarantee that the MIN(N) will be a value such
> that
> > the will succeed then you will be ok.
> >
> > Again, here's how trivial the solution is:
> >
> > If you have a row to be inserted within a table that has an identity
> column,
> > and that row has a value X for the identity column...
> >
> > If that insert is successful and if (X>CBV) then reassign CBV to be X or
> the
> > next non-negative integer larger than X such that it complies with the
> math
> > in 9.21 (Note: You have to account for INC when INC > 1)
> >
> > This will always guarantee that if implemented properly, then you will
> > always have the correct value.
> >
> >
> > It's that simple.
> > So why doesn't anyone from IBM or SUN who sells support fix this bug?
> > HINT: You should be able to implement a fix and do some unit testing
> within
> > 3 business days. (24 hours of coding time.) [Longer if you're not
> familiar
> > with the code.]
> >
> > Note: The cost per transaction of implementing this solution is minimal.
> All
> > the necessary information should be in memory at the time of the insert.
> >
> > But hey! What do I know?
> > Its not like I wrote the operating system of an embedded 6809 controller
> and
> > the initial application used to add Chlorine during the water
> purification
> > process used in major cities throughout the US .... ;-)
> >
> > -G
> >
> > [P.S. Ok, I did. The point being that if you write careless code, you
> run
> > the risk of doing harm to others. That is why you need to be careful
> when
> > you follow a spec.]
> >
> >
> >>> However, that does not mean that the implementation of the sequence
> >>> generator is compliant.
> >>>
> >> That's exactly what it means. And that's my final word. I will not
> >> pursue this debate further.
> >>
> >> "OH, East is East, and West is West, and never the twain shall meet"
> >> (Rudyard Kipling)
> >>
> >> --
> >> Bernt Marius Johnsen, Database Technology Group,
> >> Staff Engineer, Technical Lead Derby/Java DB
> >> Sun Microsystems, Trondheim, Norway
> >>
> >
> >
>
> --
> Vic Ricker
> http://www.ricker.us/
Re: "generated by default" question
Posted by Vic Ricker <vi...@ricker.us>.
Sorry Michael, I have to agree with Bernt and Craig. I think, for
performance reasons, it doesn't make sense to have it select max() on
the identity columns, especially since they're not guaranteed to be
indexed. I don't think you'd want Derby doing a table scan just because
you previously inserted a value that collided with the sequence
generator. I think it would probably make more sense to build the
"smarts" into your application rather than relying on Derby to do
something that could cause performance problems.
-Vic
P.S. The first CPU that I learned assembly language on was the 6809. It
was cool.
Michael Segel wrote:
> Sigh.
>
> Don't they teach math in engineering anymore?
>
> Lets try this one more time.
>
> In 9.21, if N does exist, then N represents a solution set of potential
> values.
>
> In your interpretation, you are *implicitly* adding an additional boundary
> that the sequence returns the MIN(N) regardless of the overall
> implementation;
>
> That is not part of the spec!
>
> Thus, if you fail to insert a value in to an identity row and the failure is
> due to the fact that the sequence returned a value that was in use, while
> there were other values within the solution set N, it is a bug.
>
> However, if you could guarantee that the MIN(N) will be a value such that
> the will succeed then you will be ok.
>
> Again, here's how trivial the solution is:
>
> If you have a row to be inserted within a table that has an identity column,
> and that row has a value X for the identity column...
>
> If that insert is successful and if (X>CBV) then reassign CBV to be X or the
> next non-negative integer larger than X such that it complies with the math
> in 9.21 (Note: You have to account for INC when INC > 1)
>
> This will always guarantee that if implemented properly, then you will
> always have the correct value.
>
>
> It's that simple.
> So why doesn't anyone from IBM or SUN who sells support fix this bug?
> HINT: You should be able to implement a fix and do some unit testing within
> 3 business days. (24 hours of coding time.) [Longer if you're not familiar
> with the code.]
>
> Note: The cost per transaction of implementing this solution is minimal. All
> the necessary information should be in memory at the time of the insert.
>
> But hey! What do I know?
> Its not like I wrote the operating system of an embedded 6809 controller and
> the initial application used to add Chlorine during the water purification
> process used in major cities throughout the US .... ;-)
>
> -G
>
> [P.S. Ok, I did. The point being that if you write careless code, you run
> the risk of doing harm to others. That is why you need to be careful when
> you follow a spec.]
>
>
>>> However, that does not mean that the implementation of the sequence
>>> generator is compliant.
>>>
>> That's exactly what it means. And that's my final word. I will not
>> pursue this debate further.
>>
>> "OH, East is East, and West is West, and never the twain shall meet"
>> (Rudyard Kipling)
>>
>> --
>> Bernt Marius Johnsen, Database Technology Group,
>> Staff Engineer, Technical Lead Derby/Java DB
>> Sun Microsystems, Trondheim, Norway
>>
>
>
--
Vic Ricker
http://www.ricker.us/
RE: "generated by default" question
Posted by de...@segel.com.
Ateesh,
If you set the Identity column to generate by default, you should be able to
insert the rows that already have a value, as long as that value is not
already in the table.
The catch is that your sequence will be screwed up.
I think that there's a way to do a work around using a stored procedure and
an after trigger statement, however, yes you point out why this bug is not a
small thing.
With respect to the specs, well, derby is wrong, no matter what those from
IBM or Sun would like to believe. ;-)
Its kind of sad, really. The fix is really trivial.
_____
From: Ateesh_Verma@rapid7.com [mailto:Ateesh_Verma@rapid7.com]
Sent: Thursday, June 15, 2006 1:05 PM
To: Derby Discussion
Subject: RE: "generated by default" question
Here is a different view to the problem.
I have a bulk export-import routine that exports all the tables into .del
files for backup using SYSCS_UTIL.SYSCS_EXPORT_TABLE and
SYSCS_UTIL.SYSCS_IMPORT_TABLE procedures.
The export-import worked just fine until I realized that I cannot INSERT
into any of the tables that have IDENTITY columns. This is because while
importing all the identity columns are filled "manually".
So, the autoincrement counter is still at 0 while there already are rows
with higher values.
I worked around that issue by using 'ALTER TABLE columnName ALTER COLUMN
columnName RESTART WITH (current_max + 1)'.
But this proves that the current derby behavior is misleading if not wrong
(no matter what the specs suggest). Derby SHOULD generate a unique id value.
I am pretty sure that, for someone who is familiar with the code fixing the
bug will be quicker and less painful than continuing with this debate.
Thanks
-Ateesh
RE: "generated by default" question
Posted by At...@rapid7.com.
Here is a different view to the problem.
I have a bulk export-import routine that exports all the tables into .del
files for backup using SYSCS_UTIL.SYSCS_EXPORT_TABLE and
SYSCS_UTIL.SYSCS_IMPORT_TABLE procedures.
The export-import worked just fine until I realized that I cannot INSERT
into any of the tables that have IDENTITY columns. This is because while
importing all the identity columns are filled "manually".
So, the autoincrement counter is still at 0 while there already are rows
with higher values.
I worked around that issue by using 'ALTER TABLE columnName ALTER COLUMN
columnName RESTART WITH (current_max + 1)'.
But this proves that the current derby behavior is misleading if not wrong
(no matter what the specs suggest). Derby SHOULD generate a unique id
value.
I am pretty sure that, for someone who is familiar with the code fixing
the bug will be quicker and less painful than continuing with this debate.
Thanks
-Ateesh
RE: "generated by default" question
Posted by Michael Segel <ms...@segel.com>.
Sigh.
Don't they teach math in engineering anymore?
Lets try this one more time.
In 9.21, if N does exist, then N represents a solution set of potential
values.
In your interpretation, you are *implicitly* adding an additional boundary
that the sequence returns the MIN(N) regardless of the overall
implementation;
That is not part of the spec!
Thus, if you fail to insert a value in to an identity row and the failure is
due to the fact that the sequence returned a value that was in use, while
there were other values within the solution set N, it is a bug.
However, if you could guarantee that the MIN(N) will be a value such that
the will succeed then you will be ok.
Again, here's how trivial the solution is:
If you have a row to be inserted within a table that has an identity column,
and that row has a value X for the identity column...
If that insert is successful and if (X>CBV) then reassign CBV to be X or the
next non-negative integer larger than X such that it complies with the math
in 9.21 (Note: You have to account for INC when INC > 1)
This will always guarantee that if implemented properly, then you will
always have the correct value.
It's that simple.
So why doesn't anyone from IBM or SUN who sells support fix this bug?
HINT: You should be able to implement a fix and do some unit testing within
3 business days. (24 hours of coding time.) [Longer if you're not familiar
with the code.]
Note: The cost per transaction of implementing this solution is minimal. All
the necessary information should be in memory at the time of the insert.
But hey! What do I know?
Its not like I wrote the operating system of an embedded 6809 controller and
the initial application used to add Chlorine during the water purification
process used in major cities throughout the US .... ;-)
-G
[P.S. Ok, I did. The point being that if you write careless code, you run
the risk of doing harm to others. That is why you need to be careful when
you follow a spec.]
> > However, that does not mean that the implementation of the sequence
> > generator is compliant.
>
> That's exactly what it means. And that's my final word. I will not
> pursue this debate further.
>
> "OH, East is East, and West is West, and never the twain shall meet"
> (Rudyard Kipling)
>
> --
> Bernt Marius Johnsen, Database Technology Group,
> Staff Engineer, Technical Lead Derby/Java DB
> Sun Microsystems, Trondheim, Norway
Re: "generated by default" question
Posted by "Bernt M. Johnsen" <Be...@Sun.COM>.
>>>>>>>>>>>> Michael Segel wrote (2006-06-14 09:23:45):
> > 6) I can find no relation, whatsoever, defined in the standard between
> > the existing values in a column and how the internal sequence
> > generator of an identity column behaves.
> >
> [mjs]
> Correct. Nor would you. That "gap" is left to the interpretation of the
> database designer. It's very possible to be "compliant" with a standard, yet
> offer a substandard product. (Thus we debunk the myth of the database as a
> commodity product....)
>
> However, that does not mean that the implementation of the sequence
> generator is compliant.
That's exactly what it means. And that's my final word. I will not
pursue this debate further.
"OH, East is East, and West is West, and never the twain shall meet"
(Rudyard Kipling)
--
Bernt Marius Johnsen, Database Technology Group,
Staff Engineer, Technical Lead Derby/Java DB
Sun Microsystems, Trondheim, Norway
Re: "generated by default" question
Posted by Craig L Russell <Cr...@Sun.COM>.
+1
Craig
On Jun 14, 2006, at 4:07 AM, Bernt M. Johnsen wrote:
> Let me clearify some items from the SQL 2003 standard related to the
> latest mails regarding this issue from Craig and Michael:
>
> 1) In the case of "generated always", it should not be possible to
> insert explicit values in identity columns, nor to alter generated
> values.
>
> 2) Internal and external sequence generators are by default not
> cyclical (Ch 9.22), so an exception will occur when they are
> exhausted (Ch 9.21)
>
> 3) In the case of a rollback, the sequence generator does not "skip"
> values, altough it may appear so. The standard says that "commits
> and rollbacks of SQL-transactions have no effect on the current
> base value of a sequence generator". (Ch 4.21.1) It is the *use* of
> the generated value that is rolled back.
>
> 4) A sequence generator which for some reason skips a value (which is
> allowed), will not issue that value in the current cycle, since the
> current base value will be set to the highest value (or lowest if
> the generator is descending) issued in that cycle (Ch 9.21, general
> rules, part 4)
>
> Then again:
>
> 5) The behaviour of sequence generators is described independently of
> the context they are used in (as Craig correctly points out but
> with
> other words).
>
> 6) I can find no relation, whatsoever, defined in the standard between
> the existing values in a column and how the internal sequence
> generator of an identity column behaves.
>
> And finally:
>
> 7) How other databases (or Derby for that matter) implement a feature
> is no proof of standards compliancy.
>
> --
> Bernt Marius Johnsen, Database Technology Group,
> Staff Engineer, Technical Lead Derby/Java DB
> Sun Microsystems, Trondheim, Norway
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!
RE: "generated by default" question
Posted by Michael Segel <ms...@segel.com>.
Bernt,
First, I'd like to thank you for bringing the argument in to a concise and
orderly fashion.
My comments are mixed in with yours and in the most part we are in
agreement.
However, there are differences of opinion and this still should be
considered either a bug/product defect, or a design defect. Here's Why:
If there exists a value N that is an element of the solution set to 9.21's
paragraph 3, and that the use of this value results in a successful
insertion in to the table, then this value of N should be used.
In simple and plain English:
The sequence should return a unique value (one that hasn't been used
before) such that the insert should not fail due to the identity column's
non-unique constraint.
An example:
We have an identity column with "GENERATE BY DEFAULT".
Sequence starts at 1. You enter 4 rows using the DEFAULT values. (1,2,3,4).
You then manually insert a row with a value of 5.
You attempt to insert a row using the DEFAULT value.
Currently Derby throws an SQL Exception because the insert violated the
Identity column's uniqueness constraint.
This is a bug/product defect or design defect because within the solution
set for N, there are other numbers which would not yield an error.
For example, had the sequence returned the value 6, which is a valid value
btw, then there would not be an error.
Also consider this...
The anticipated result of inserting a row using the generated value for an
identity column should be a successful insertion, as long as there is room
in the table. (That there exists a value for N in 9.21's paragraph 3).
Because this does not occur, it is a bug.
The solution is fairly trivial.
On an insert in to a table which has an identity column which specifies
GENERATE BY DEFAULT,
If the row to be inserted contains a value for the identity column,
After a successful insert,
Assign the CBV to that value just inserted.
That's it.
Using the previous example,
You manually insert a row using the value 10;
If the row can be successfully inserted in to the table,
Then you reset the CBV to 10.
Then when you next insert a row using the default value, it will insert at
11.
> -----Original Message-----
> From: Bernt.Johnsen@Sun.COM [mailto:Bernt.Johnsen@Sun.COM]
> Sent: Wednesday, June 14, 2006 6:07 AM
> To: Derby Discussion
> Subject: Re: "generated by default" question
>
> Let me clearify some items from the SQL 2003 standard related to the
> latest mails regarding this issue from Craig and Michael:
>
> 1) In the case of "generated always", it should not be possible to
> insert explicit values in identity columns, nor to alter generated
> values.
>
[mjs]
Correct.
> 2) Internal and external sequence generators are by default not
> cyclical (Ch 9.22), so an exception will occur when they are
> exhausted (Ch 9.21)
>
[mjs]
This is the first time anyone published a reference to 9.22. (Sorry but my
company is too cheap right now to pay for an electronic copy of the spec...
;-)...
So lets not worry about cyclical sequence generators.
> 3) In the case of a rollback, the sequence generator does not "skip"
> values, altough it may appear so. The standard says that "commits
> and rollbacks of SQL-transactions have no effect on the current
> base value of a sequence generator". (Ch 4.21.1) It is the *use* of
> the generated value that is rolled back.
>
[mjs]
Correct.
This was never really an issue, however it is important to point out that
the mathematical definition of the sequence generator allows for the
sequence number to return any value N that fits the criteria in 9.21.
> 4) A sequence generator which for some reason skips a value (which is
> allowed), will not issue that value in the current cycle, since the
> current base value will be set to the highest value (or lowest if
> the generator is descending) issued in that cycle (Ch 9.21, general
> rules, part 4)
>
[mjs]
Correct.
Now, for those that are mathematically challenged, the fact that you can
skip a value in a sequence is a very important component to its definition.
Mathematics is a very precise language. At least that is what my uncle kept
trying to pound in to my thick skull. ;-)
This key fact is why you can consider the current implementation of Derby
has either a product defect or a design defect that needs to be corrected!
> Then again:
>
> 5) The behaviour of sequence generators is described independently of
> the context they are used in (as Craig correctly points out but with
> other words).
>
[mjs]
Correct. The specification describes how the sequence generators work in
general. The specification describes how the identity column works in
general.
Craig used the term "orthogonal". While each component may be orthogonal to
each other, they are combined to deliver a function/feature of the database.
Its like having a plate of Fish and Chips. There's the fish and then there's
the chips. Each is orthogonal to each other however; when they are combined,
with a pint of your favorite brew to create what we call "dinner". ;-)
The key point is that you're implementing a database feature using
orthogonal components and you have to consider the context when deciding if
you are in fact following the standard.
> 6) I can find no relation, whatsoever, defined in the standard between
> the existing values in a column and how the internal sequence
> generator of an identity column behaves.
>
[mjs]
Correct. Nor would you. That "gap" is left to the interpretation of the
database designer. It's very possible to be "compliant" with a standard, yet
offer a substandard product. (Thus we debunk the myth of the database as a
commodity product....)
However, that does not mean that the implementation of the sequence
generator is compliant.
> And finally:
>
> 7) How other databases (or Derby for that matter) implement a feature
> is no proof of standards compliancy.
>
[mjs]
Sure enough. However, this has been pointed out countless times that its
appropriate to compare feature functionality with comparable products in an
effort to produce a better design.
> --
> Bernt Marius Johnsen, Database Technology Group,
> Staff Engineer, Technical Lead Derby/Java DB
> Sun Microsystems, Trondheim, Norway
[mjs]
But hey! What do I know?
Its not like I have a degree in Software Engineering or anything. ;-)
-Mikey
Michael Segel,
Chief Peon in Charge,
MSC Corp.
Chicago, IL, USA
Re: "generated by default" question
Posted by "Bernt M. Johnsen" <Be...@Sun.COM>.
Let me clearify some items from the SQL 2003 standard related to the
latest mails regarding this issue from Craig and Michael:
1) In the case of "generated always", it should not be possible to
insert explicit values in identity columns, nor to alter generated
values.
2) Internal and external sequence generators are by default not
cyclical (Ch 9.22), so an exception will occur when they are
exhausted (Ch 9.21)
3) In the case of a rollback, the sequence generator does not "skip"
values, altough it may appear so. The standard says that "commits
and rollbacks of SQL-transactions have no effect on the current
base value of a sequence generator". (Ch 4.21.1) It is the *use* of
the generated value that is rolled back.
4) A sequence generator which for some reason skips a value (which is
allowed), will not issue that value in the current cycle, since the
current base value will be set to the highest value (or lowest if
the generator is descending) issued in that cycle (Ch 9.21, general
rules, part 4)
Then again:
5) The behaviour of sequence generators is described independently of
the context they are used in (as Craig correctly points out but with
other words).
6) I can find no relation, whatsoever, defined in the standard between
the existing values in a column and how the internal sequence
generator of an identity column behaves.
And finally:
7) How other databases (or Derby for that matter) implement a feature
is no proof of standards compliancy.
--
Bernt Marius Johnsen, Database Technology Group,
Staff Engineer, Technical Lead Derby/Java DB
Sun Microsystems, Trondheim, Norway
Re: "generated by default" question
Posted by Craig L Russell <Cr...@Sun.COM>.
Hi G,
On Jun 13, 2006, at 12:07 PM, Michael Segel wrote:
> If there exists a value N such that you can return V1 that doesn't
> throw an
> exception due to the unique constraint on the identity column, then
> the
> sequence should return that number. Or rather Derby should trap for
> this and
> determine how to find a value N if it exists. If N does not exist,
> then you
> should indicate that the table is "full".
Cycling through my earlier comments now.
The generator and the unique constraint checking are orthogonal
concepts.
Craig
>
> Failing to do so would mean that there is a design defect that
> needs to be
> corrected.
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!
Re: "generated by default" question
Posted by Michael Segel <ms...@segel.com>.
On Tuesday 13 June 2006 1:01 pm, Craig L Russell wrote:
> Hi Mikey,
>
[SNIP]
> > [mjs] Hi Craig,
> >
> > Errr. No.
> > In short, the sequence generation is outside of the transaction,
> > therefore
> > its possible to get a jump in the sequence number due to transactions
> > rolling back or individual inserts failing due to additional
> > constraints.
>
> Not my point.
>
> > Please note that I'm assuming an incremental value of 1 to prove
> > the point.
> > If the sequence use a different value for its increment, then you
> > will have
> > to adjust the logic a bit.
> >
> > Here's the section 4.21 language:"
> > 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."
>
> All this defines is the "default" instead of "always" behavior.
>
Yes, of course. What would happen if you inserted a row with your own value
and the identity column is using the *always* behavior? Either you get an
exception thrown, or you ignore the input value and just generates the
value.
If you're always generating the value, you can still get gaps, however, you'll
never generate a value that is already in use unless you cycle.
> > Ok so you're looking at what they term is a "general rule". (Not
> > going to
> > define what *that* means.
> >
> > However looking at 9.21,
> >
> > 9.21 defines a mathematical statement. And the solution under 9.21
> > is really
> > a solution set. There a potential of (MAX - CBV)/INC elements (and
> > the empty
> > set) that could be applied where CBV < MAX.
>
> ok.
>
> > Your suggestion violates this because the number of possible values
> > for N
> > will be >= 1 iff (MAX-CBV)/INC >1.
>
> no. What I'm saying is that the sequence generator is defined to
> return all values between the start and max value without skipping
> any. And at the risk of repeating myself, this is behavior of the
> sequence generator not the constraint on the column values.
>
Uhm, no.
9.21 doesn't say that.
Look at the math.
"3) If there exists a non-negative integer N such that SMIN <= CBV + N
    * INC <= SMAX and the value (CBV + N * INC) has not already been
    returned in the current cycle, then let V1 be (CBV + N *
    INC). Otherwise,
"
This is kind of important. It implies that you can not guarantee the order of
V1 being returned.
> > IF you return a sequence value N that meets the criteria, then
> > you're not
> > violating 9.21.
> >
> > Note again that this is a generalization and that implementations
> > of these
> > rules can vary.
> >
> > The first piece of the fix would be to do the following logic:
> >
> > If (row inserted contains a value R that corresponds to IC) {
> > After Insert:
> > If ( R > CBV) Then CBV=R; // Or R+1;
> > } else {
> > CBV += INC; // INC = incremental value
> > }
> >
> > Sorry for the bad pseudo code.
> >
> > The idea is that you'll always be setting CBV to the max value when
> > someone
> > inserts a row with their own identity value that is greater than
> > the current
> > base value.
>
> You still haven't responded to my main point:
> As I read this part of the specification, it refers to the generation
> of the sequence number, and not to the usage. The trick phrase is
> "the value (CBV + N * INC) has not already been returned in the
> current cycle". As I understand "returned" it means returned by the
> sequence generator, and nothing to do with the usage as a column value.
>
Right.
But my point is that N can be any number between CBV and SMAX (Assuming INC is
1).
So you can get jumps in the value of the current cycle.
But yes, to your point, return does mean from the sequence generation.
(This is a gap in the spec. Unless there is more in the sections following
4.21)
One of my earlier points in a post is that there are gaps on how to implement
identity columns and sequences. So error correction is left to the
implementor.
However, this is why I called this a bug/product defect/ design defect.
As I showed in my other post, Informix will not have a problem within the
first cycle. It does however have a problem in additional cycles.
My take is that you have to create an elegant solution that would not have a
negative impact on performance. Again, I believe that if you apply the
solution I outlined earlier, this will help in the first cycle. And its a
relatively quick fix. If you're going to allow for repeated cycles, then
you're going to have to try some additional steps.
> Simply put, the sequence generator must return all legal values of
> SMIN < return-value < SMAX before reusing a return-value.
>
> > This is what Informix appears to be doing with their Serial value.
> >
> > Of course when you cycle over, then you have to worry about the
> > issue what
> > happens if the row already has a value in it. And that's the next
> > issue.
> >
> > Sorry, but this is still a bug. The current version of Derby does
> > violate
> > the specs as stated here.
>
> Sorry, not convinced.
>
Uhm, OK This is why databases will never be a commodity. ;-)
(Its good that not everyone sees things the same way...)
I would suggest you get a mathematician to look at the language used.
If there exists a value N such that you can return V1 that doesn't throw an
exception due to the unique constraint on the identity column, then the
sequence should return that number. Or rather Derby should trap for this and
determine how to find a value N if it exists. If N does not exist, then you
should indicate that the table is "full".
Failing to do so would mean that there is a design defect that needs to be
corrected.
I believe that we both agree that there are gaps in the spec.
The truly sad thing is that as a developer, unless I use auto generated
identity columns, I can not count on Derby to correctly handle an insert.
Also note that I'm trying to be practical in that I'm not looking beyond the
first cycle....
> Craig
>
> > Look at it this way. As long as a value N exists between the bounds
> > of CBV
> > and MAX, you will always have a sequence number.
> >
> > By following this logic, and going through a single cycle, you will
> > always
> > have either a value or an empty set when MAX - CBV < INC.
> >
> > -G
>
> 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!
Re: "generated by default" question
Posted by Michael Segel <ms...@segel.com>.
On Tuesday 13 June 2006 1:19 pm, Craig L Russell wrote:
> Hi,
>
> On Jun 13, 2006, at 11:01 AM, Craig L Russell wrote:
> >>> In fact, you could argue that if the implementation skipped
> >>> returning
> >>> a sequence value just because that value had been inserted by the
> >>> user into a column, it would be a bug.
>
> I withdraw this argument after re-reading the words. It is legal to
> skip over some number of values as long as you remember which values
> were skipped over so that you don't cycle before returning all of them.
>
> Craig
>
Yup.
Thats why if on inserts where the user enters a value, if you check to see
that the value is > CBV and that the row inserted ok, then you reset your CBV
to that value +1. (Assuming that the increment is 1).
That will ensure that for the first cycle, you'll never throw a unique
exception.... ;-) Its a neat little trick and since the sequence Object and
the row entered with still be in memory, it would be a very, fast and
efficient way to handle this problem in the short term. ;-)
Kind of a neat trick. I imagine this is what they did when they developed the
serial column and probably carried it over to their sequences.
(Anyone at IBM care to comment? ;-)
-G
PS. The solution gets murky when you have to have multiple iterations or
cycles. But that doesn't mean you can't find a solution that would still work
and be efficient.
> 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!
Re: "generated by default" question
Posted by Craig L Russell <Cr...@Sun.COM>.
Hi,
On Jun 13, 2006, at 11:01 AM, Craig L Russell wrote:
>>> In fact, you could argue that if the implementation skipped
>>> returning
>>> a sequence value just because that value had been inserted by the
>>> user into a column, it would be a bug.
I withdraw this argument after re-reading the words. It is legal to
skip over some number of values as long as you remember which values
were skipped over so that you don't cycle before returning all of them.
Craig
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!
Re: "generated by default" question
Posted by Craig L Russell <Cr...@Sun.COM>.
Hi Mikey,
On Jun 13, 2006, at 10:47 AM, derby@segel.com wrote:
>
>
> by default" question
>>
>> Hi Mikey,
>>
>> On Jun 12, 2006, at 10:05 PM, derby@segel.com wrote:
>>
>>> [mjs]
>>> I believe the problem is in how you're interpreting clause 3):
>>> "
>>> 3) If there exists a non-negative integer N such that SMIN <=
>>> CBV + N
>>> * INC <= SMAX and the value (CBV + N * INC) has not
>>> already been
>>> returned in the current cycle, then let V1 be (CBV + N *
>>> INC). Otherwise, ...
>>> "
>>> -=-
>>>
>>> It doesn't say what N is. That is to say...
>>> Suppose you have a sequence 0,1,2,3,4 inserted so that the next
>>> number
>>> should be 5. Yet suppose someone inserts a row with 5. Thus when
>>> you try to
>>> use 5, you generate an error. In sub-section 3), N could =6 or any
>>> number >6
>>> but less than the MAX value of an integer.
>>>
>>> It can be interpreted that the sequence should attempt to generate
>>> N such
>>> that it doesn't fail on the insert....
>>>
>>> This is where the idea of selecting the MAX() value in the identity
>>> column
>>> and incrementing it by one for the failed insert.
>>>
>>> And that would be a compliant solution.
>>
>> As I read this part of the specification, it refers to the generation
>> of the sequence number, and not to the usage. The trick phrase is
>> "the value (CBV + N * INC) has not already been returned in the
>> current cycle". As I understand "returned" it means returned by the
>> sequence generator, and nothing to do with the usage as a column
>> value.
>>
>> In fact, you could argue that if the implementation skipped returning
>> a sequence value just because that value had been inserted by the
>> user into a column, it would be a bug.
>>
>> Regards,
>>
>> Craig
>
> [mjs] Hi Craig,
>
> Errr. No.
> In short, the sequence generation is outside of the transaction,
> therefore
> its possible to get a jump in the sequence number due to transactions
> rolling back or individual inserts failing due to additional
> constraints.
Not my point.
>
> Please note that I'm assuming an incremental value of 1 to prove
> the point.
> If the sequence use a different value for its increment, then you
> will have
> to adjust the logic a bit.
>
> Here's the section 4.21 language:"
> 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."
All this defines is the "default" instead of "always" behavior.
>
> Ok so you're looking at what they term is a "general rule". (Not
> going to
> define what *that* means.
>
> However looking at 9.21,
>
> 9.21 defines a mathematical statement. And the solution under 9.21
> is really
> a solution set. There a potential of (MAX - CBV)/INC elements (and
> the empty
> set) that could be applied where CBV < MAX.
ok.
>
> Your suggestion violates this because the number of possible values
> for N
> will be >= 1 iff (MAX-CBV)/INC >1.
no. What I'm saying is that the sequence generator is defined to
return all values between the start and max value without skipping
any. And at the risk of repeating myself, this is behavior of the
sequence generator not the constraint on the column values.
>
> IF you return a sequence value N that meets the criteria, then
> you're not
> violating 9.21.
>
> Note again that this is a generalization and that implementations
> of these
> rules can vary.
>
> The first piece of the fix would be to do the following logic:
>
> If (row inserted contains a value R that corresponds to IC) {
> After Insert:
> If ( R > CBV) Then CBV=R; // Or R+1;
> } else {
> CBV += INC; // INC = incremental value
> }
>
> Sorry for the bad pseudo code.
>
> The idea is that you'll always be setting CBV to the max value when
> someone
> inserts a row with their own identity value that is greater than
> the current
> base value.
You still haven't responded to my main point:
As I read this part of the specification, it refers to the generation
of the sequence number, and not to the usage. The trick phrase is
"the value (CBV + N * INC) has not already been returned in the
current cycle". As I understand "returned" it means returned by the
sequence generator, and nothing to do with the usage as a column value.
Simply put, the sequence generator must return all legal values of
SMIN < return-value < SMAX before reusing a return-value.
>
> This is what Informix appears to be doing with their Serial value.
>
> Of course when you cycle over, then you have to worry about the
> issue what
> happens if the row already has a value in it. And that's the next
> issue.
>
> Sorry, but this is still a bug. The current version of Derby does
> violate
> the specs as stated here.
Sorry, not convinced.
Craig
>
> Look at it this way. As long as a value N exists between the bounds
> of CBV
> and MAX, you will always have a sequence number.
>
> By following this logic, and going through a single cycle, you will
> always
> have either a value or an empty set when MAX - CBV < INC.
>
> -G
>
>
>
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!
RE: "generated by default" question
Posted by de...@segel.com.
by default" question
>
> Hi Mikey,
>
> On Jun 12, 2006, at 10:05 PM, derby@segel.com wrote:
>
> > [mjs]
> > I believe the problem is in how you're interpreting clause 3):
> > "
> > 3) If there exists a non-negative integer N such that SMIN <=
> > CBV + N
> > * INC <= SMAX and the value (CBV + N * INC) has not
> > already been
> > returned in the current cycle, then let V1 be (CBV + N *
> > INC). Otherwise, ...
> > "
> > -=-
> >
> > It doesn't say what N is. That is to say...
> > Suppose you have a sequence 0,1,2,3,4 inserted so that the next number
> > should be 5. Yet suppose someone inserts a row with 5. Thus when
> > you try to
> > use 5, you generate an error. In sub-section 3), N could =6 or any
> > number >6
> > but less than the MAX value of an integer.
> >
> > It can be interpreted that the sequence should attempt to generate
> > N such
> > that it doesn't fail on the insert....
> >
> > This is where the idea of selecting the MAX() value in the identity
> > column
> > and incrementing it by one for the failed insert.
> >
> > And that would be a compliant solution.
>
> As I read this part of the specification, it refers to the generation
> of the sequence number, and not to the usage. The trick phrase is
> "the value (CBV + N * INC) has not already been returned in the
> current cycle". As I understand "returned" it means returned by the
> sequence generator, and nothing to do with the usage as a column value.
>
> In fact, you could argue that if the implementation skipped returning
> a sequence value just because that value had been inserted by the
> user into a column, it would be a bug.
>
> Regards,
>
> Craig
[mjs] Hi Craig,
Errr. No.
In short, the sequence generation is outside of the transaction, therefore
its possible to get a jump in the sequence number due to transactions
rolling back or individual inserts failing due to additional constraints.
Please note that I'm assuming an incremental value of 1 to prove the point.
If the sequence use a different value for its increment, then you will have
to adjust the logic a bit.
Here's the section 4.21 language:"
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."
Ok so you're looking at what they term is a "general rule". (Not going to
define what *that* means.
However looking at 9.21,
9.21 defines a mathematical statement. And the solution under 9.21 is really
a solution set. There a potential of (MAX - CBV)/INC elements (and the empty
set) that could be applied where CBV < MAX.
Your suggestion violates this because the number of possible values for N
will be >= 1 iff (MAX-CBV)/INC >1.
IF you return a sequence value N that meets the criteria, then you're not
violating 9.21.
Note again that this is a generalization and that implementations of these
rules can vary.
The first piece of the fix would be to do the following logic:
If (row inserted contains a value R that corresponds to IC) {
After Insert:
If ( R > CBV) Then CBV=R; // Or R+1;
} else {
CBV += INC; // INC = incremental value
}
Sorry for the bad pseudo code.
The idea is that you'll always be setting CBV to the max value when someone
inserts a row with their own identity value that is greater than the current
base value.
This is what Informix appears to be doing with their Serial value.
Of course when you cycle over, then you have to worry about the issue what
happens if the row already has a value in it. And that's the next issue.
Sorry, but this is still a bug. The current version of Derby does violate
the specs as stated here.
Look at it this way. As long as a value N exists between the bounds of CBV
and MAX, you will always have a sequence number.
By following this logic, and going through a single cycle, you will always
have either a value or an empty set when MAX - CBV < INC.
-G
Re: "generated by default" question
Posted by Craig L Russell <Cr...@Sun.COM>.
Hi Mikey,
On Jun 12, 2006, at 10:05 PM, derby@segel.com wrote:
> [mjs]
> I believe the problem is in how you're interpreting clause 3):
> "
> 3) If there exists a non-negative integer N such that SMIN <=
> CBV + N
> * INC <= SMAX and the value (CBV + N * INC) has not
> already been
> returned in the current cycle, then let V1 be (CBV + N *
> INC). Otherwise, ...
> "
> -=-
>
> It doesn't say what N is. That is to say...
> Suppose you have a sequence 0,1,2,3,4 inserted so that the next number
> should be 5. Yet suppose someone inserts a row with 5. Thus when
> you try to
> use 5, you generate an error. In sub-section 3), N could =6 or any
> number >6
> but less than the MAX value of an integer.
>
> It can be interpreted that the sequence should attempt to generate
> N such
> that it doesn't fail on the insert....
>
> This is where the idea of selecting the MAX() value in the identity
> column
> and incrementing it by one for the failed insert.
>
> And that would be a compliant solution.
As I read this part of the specification, it refers to the generation
of the sequence number, and not to the usage. The trick phrase is
"the value (CBV + N * INC) has not already been returned in the
current cycle". As I understand "returned" it means returned by the
sequence generator, and nothing to do with the usage as a column value.
In fact, you could argue that if the implementation skipped returning
a sequence value just because that value had been inserted by the
user into a column, it would be a bug.
Regards,
Craig
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!
RE: "generated by default" question
Posted by de...@segel.com.
Bernt,
I believe that if you look at the spec, you'll find that the current
implementation within Derby is indeed a bug.
Its your interpretation of 9.21 that I'm calling in to question...
See comments below...
-Mike
> -----Original Message-----
> From: Bernt.Johnsen@Sun.COM [mailto:Bernt.Johnsen@Sun.COM]
> Sent: Monday, June 12, 2006 5:22 AM
> To: Derby Discussion; msegel@segel.com
I would hope to clarify something...
> The identity column as specified in the standard does not guarantee a
> unique value. It gives you a value according to a sequence
> generator. If used properly, then the user may get unique values, but
> he may also generate duplicates or violate constraints and get an
> exception.
>
[mjs]
I should clarify something...
If the user is attempting to write something that would break that would be
one thing. I'm talking about trying something that one should expect to
work....
> You may even write a cyclical identity column like this
>
> GENERATED BY DEFAULT AS IDENTITY (START WITH 0
> INCREMENT BY 1
> MAXVALUE 2
> CYCLE)
>
> Here the generated values for the identity column will be
> 0,1,2,0,1,2,0,1,2,0,1,2....... (better not have a UNIQUE constraint
> on the identity column :-)
>
[mjs]
Ok, That's fine. So after you hit a sequence value of 2, then you can no
longer insert a row in to the table. Depending on your ability to handle the
constraint and the attempt to insert a row, you would throw an exception.
But I'm talking about a situation where if a user inserts a row in to a
table and it has an identity value > than the current sequence, you'll throw
an exception as you increment the sequence and you'll rely on the user to
trap for the exception and to continue to try to insert the row.
That is to say, if the user inserts rows 10 through 10,000 and the sequence
is currently at 9, then the user's application will cycle 9990 times before
a successful insert.
So for every insert in to a table where the column could be a potential
identity column, you'd have a lot of coding to do.
> (Note: Derby does not support MAXVALUE and CYCLE. Derby does also
> require a comma befor INCREMENT).
>
[mjs]
That's fine, however, your example is a good one.
However, that doesn't mean that Derby doesn't have a design defect, and that
there are a couple of solutions that could be implemented in Derby to
attempt to perform the insert and if still impossible, then throw the
exception.
You just don't throw the exception when you first hit a row where the
sequence generates a number that is already in use.... (We'll get to that in
a second...)
> Chapter 4.14.17:
>
> The columns of a base table BT can optionally include not more than
> one identity column. The declared type of an identity column is either
> an exact numeric type with scale 0 (zero), INTEGER for example, or a
> distinct type whose source type is an exact numeric type with scale 0
> (zero). An identity column has a start value, an increment, a maximum
> value, a minimum value, and a cycle option. 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.
> SG.
>
>
> And chapter 9.21:
>
> 9.21 Generation of the next value of a sequence generator
>
> Function
>
> Generate and return the next value of a sequence generator.
>
> Syntax Rules
>
> None.
>
> Access Rules
>
> None.
>
> General Rules
>
> 1) Let SEQ be the SEQUENCE specified in an application of this
> Subclause.
>
> 2) Let DT, CBV, INC, SMAX, and SMIN be the data type, current base
> value, increment, maximum value and minimum value, respectively, of
> SEQ.
>
> 3) If there exists a non-negative integer N such that SMIN <= CBV + N
> * INC <= SMAX and the value (CBV + N * INC) has not already been
> returned in the current cycle, then let V1 be (CBV + N *
> INC). Otherwise,
>
[mjs] BINGO!
> Case:
>
> a) If the cycle option of SEQ is NO CYCLE, then an exception
> condition is raised: data exception - sequence generator limit
> exceeded.
>
> b) Otherwise, a new cycle is initiated.
>
> Case:
>
> i) If SEQ is an ascending sequence generator, then let V1 be
> SMIN.
>
> ii) Otherwise, let V1 be SMAX.
>
> 4) Case:
>
> a) If SEQ is an ascending sequence generator, the current base
> value of SEQ is set to the value of the lowest non-issued value
> in the cycle.
>
> b) Otherwise, the current base value of SEQ is set to the highest
> non-issued value in the cycle.
>
> 5) V1 is returned as the RESULT.
>
> Conformance Rules
>
> None.
>
>
[mjs]
I believe the problem is in how you're interpreting clause 3):
"
3) If there exists a non-negative integer N such that SMIN <= CBV + N
* INC <= SMAX and the value (CBV + N * INC) has not already been
returned in the current cycle, then let V1 be (CBV + N *
INC). Otherwise, ...
"
-=-
It doesn't say what N is. That is to say...
Suppose you have a sequence 0,1,2,3,4 inserted so that the next number
should be 5. Yet suppose someone inserts a row with 5. Thus when you try to
use 5, you generate an error. In sub-section 3), N could =6 or any number >6
but less than the MAX value of an integer.
It can be interpreted that the sequence should attempt to generate N such
that it doesn't fail on the insert....
This is where the idea of selecting the MAX() value in the identity column
and incrementing it by one for the failed insert.
And that would be a compliant solution.
[SNIP]
> --
> Bernt Marius Johnsen, Database Technology Group,
> Staff Engineer, Technical Lead Derby/Java DB
> Sun Microsystems, Trondheim, Norway
[mjs]
-Mikey
PS,
Note: There are a couple of ways of implementing this logic so that you
don't have to trap for an insert error.
Hint: You could check on the insert and if the row contains a value in the
identity column, ICVal and ICVal > CBV, then after insert, CBV= ICVal;
This would ensure that if you ever manually insert a row and it is
successful, then the CBV (Current Base Value) will always be larger than the
max row.
Of course I'm assuming that the insert is atomic, however you could put a
mutex lock on the sequence while you're inserting a row where ICVal is >
than CBV ... that would solve the problem...
This actually would be the most "elegant" solution.
But hey! What do I know?
BTW, am I showing my age when I ask where you were when the Morris Worm hit
the net? ;-)
Re: "generated by default" question
Posted by "Bernt M. Johnsen" <Be...@Sun.COM>.
Well, I'm the one that doesn't accept this as a bug/defect since Derby
behaves according to the SQL standard. My stand is based on the
chapters 4.14.17 and 9.21 in the SQL 2003 (INCITS/ISO/IEC 9075-2-2003
Information technology - Database languages - SQL - Part 2: Foundation
(SQL/Foundation)), see below.
The identity column as specified in the standard does not guarantee a
unique value. It gives you a value according to a sequence
generator. If used properly, then the user may get unique values, but
he may also generate duplicates or violate constraints and get an
exception.
You may even write a cyclical identity column like this
GENERATED BY DEFAULT AS IDENTITY (START WITH 0
INCREMENT BY 1
MAXVALUE 2
CYCLE)
Here the generated values for the identity column will be
0,1,2,0,1,2,0,1,2,0,1,2....... (better not have a UNIQUE constraint
on the identity column :-)
(Note: Derby does not support MAXVALUE and CYCLE. Derby does also
require a comma befor INCREMENT).
Chapter 4.14.17:
The columns of a base table BT can optionally include not more than
one identity column. The declared type of an identity column is either
an exact numeric type with scale 0 (zero), INTEGER for example, or a
distinct type whose source type is an exact numeric type with scale 0
(zero). An identity column has a start value, an increment, a maximum
value, a minimum value, and a cycle option. 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.
SG.
And chapter 9.21:
9.21 Generation of the next value of a sequence generator
Function
Generate and return the next value of a sequence generator.
Syntax Rules
None.
Access Rules
None.
General Rules
1) Let SEQ be the SEQUENCE specified in an application of this
Subclause.
2) Let DT, CBV, INC, SMAX, and SMIN be the data type, current base
value, increment, maximum value and minimum value, respectively, of
SEQ.
3) If there exists a non-negative integer N such that SMIN <= CBV + N
* INC <= SMAX and the value (CBV + N * INC) has not already been
returned in the current cycle, then let V1 be (CBV + N *
INC). Otherwise,
Case:
a) If the cycle option of SEQ is NO CYCLE, then an exception
condition is raised: data exception - sequence generator limit
exceeded.
b) Otherwise, a new cycle is initiated.
Case:
i) If SEQ is an ascending sequence generator, then let V1 be
SMIN.
ii) Otherwise, let V1 be SMAX.
4) Case:
a) If SEQ is an ascending sequence generator, the current base
value of SEQ is set to the value of the lowest non-issued value
in the cycle.
b) Otherwise, the current base value of SEQ is set to the highest
non-issued value in the cycle.
5) V1 is returned as the RESULT.
Conformance Rules
None.
>>>>>>>>>>>> Michael Segel wrote (2006-06-09 10:58:25):
> There is a Jira issue already opened.
> Someone pointed to an earlier thread in which Daniel already recommended a
> solution.
>
> I don't find fault with Craig's comment.
> I was thinking in the debate between myself and Bernt.
>
> The issue is that while you can look towards the standards on sequences,
> when you have an identity column that auto-generates a value, you have to
> account for the fact that someone can insert a row that has an unused
> identity value and that the sequence hasn't generated a value that large
> yet.
>
> It's a bug if you ask for Derby to generate an identity value and then it
> fails because the sequence returns a value that is already in use.
> (I.e. someone inserts a row with the identity value of 10 and the sequence
> is only at 6. So that when the sequence returns a value of 10, the insert
> will fail...)
>
> My point is that there is a recognized problem, and it's a defect. Since
> there are two well established corporations which are profiting from selling
> support for Cloudscape/Derby/JavaDB, then they should step up to the plate
> and fix the problem.
>
> It would be different if we were looking for an enhacement or a feature
> request. Then there would be no obligation to implement the
> enhancement/feature....
>
>
> > -----Original Message-----
> > From: Jean T. Anderson [mailto:jta@bristowhill.com]
> > Sent: Friday, June 09, 2006 10:33 AM
> > To: Derby Discussion
> > Subject: Re: "generated by default" question
> >
> > Michael Segel wrote:
> > <snip>
> > > Having said that, Daniel already made a suggestion which is in use today
> > by
> > > other databases....
> > >
> > > The simplest solution would be to find the MAX value, rest the sequence
> > to
> > > MAX() +1 and re-insert the row.
> > >
> > > There are other possible solutions.
> > >
> > > It's disappointing that certain people don't accept this as a bug
> >
> > Craig Russell wrote earlier in this thread [1]:
> >
> > > 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.
> >
> > Anyone is welcome to open a Jira issue. Jira assistance is at
> > http://db.apache.org/derby/DerbyBugGuidelines.html .
> >
> > -jean
> >
> > [1]
> > http://mail-archives.apache.org/mod_mbox/db-derby-
> > user/200605.mbox/%3cDD522070-780A-4C31-B095-0009E49EFEA2@SUN.com%3e
>
>
--
Bernt Marius Johnsen, Database Technology Group,
Staff Engineer, Technical Lead Derby/Java DB
Sun Microsystems, Trondheim, Norway
RE: "generated by default" question
Posted by Michael Segel <ms...@segel.com>.
There is a Jira issue already opened.
Someone pointed to an earlier thread in which Daniel already recommended a
solution.
I don't find fault with Craig's comment.
I was thinking in the debate between myself and Bernt.
The issue is that while you can look towards the standards on sequences,
when you have an identity column that auto-generates a value, you have to
account for the fact that someone can insert a row that has an unused
identity value and that the sequence hasn't generated a value that large
yet.
It's a bug if you ask for Derby to generate an identity value and then it
fails because the sequence returns a value that is already in use.
(I.e. someone inserts a row with the identity value of 10 and the sequence
is only at 6. So that when the sequence returns a value of 10, the insert
will fail...)
My point is that there is a recognized problem, and it's a defect. Since
there are two well established corporations which are profiting from selling
support for Cloudscape/Derby/JavaDB, then they should step up to the plate
and fix the problem.
It would be different if we were looking for an enhacement or a feature
request. Then there would be no obligation to implement the
enhancement/feature....
> -----Original Message-----
> From: Jean T. Anderson [mailto:jta@bristowhill.com]
> Sent: Friday, June 09, 2006 10:33 AM
> To: Derby Discussion
> Subject: Re: "generated by default" question
>
> Michael Segel wrote:
> <snip>
> > Having said that, Daniel already made a suggestion which is in use today
> by
> > other databases....
> >
> > The simplest solution would be to find the MAX value, rest the sequence
> to
> > MAX() +1 and re-insert the row.
> >
> > There are other possible solutions.
> >
> > It's disappointing that certain people don't accept this as a bug
>
> Craig Russell wrote earlier in this thread [1]:
>
> > 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.
>
> Anyone is welcome to open a Jira issue. Jira assistance is at
> http://db.apache.org/derby/DerbyBugGuidelines.html .
>
> -jean
>
> [1]
> http://mail-archives.apache.org/mod_mbox/db-derby-
> user/200605.mbox/%3cDD522070-780A-4C31-B095-0009E49EFEA2@SUN.com%3e
Re: "generated by default" question
Posted by "Jean T. Anderson" <jt...@bristowhill.com>.
Michael Segel wrote:
<snip>
> Having said that, Daniel already made a suggestion which is in use today by
> other databases....
>
> The simplest solution would be to find the MAX value, rest the sequence to
> MAX() +1 and re-insert the row.
>
> There are other possible solutions.
>
> It's disappointing that certain people don't accept this as a bug
Craig Russell wrote earlier in this thread [1]:
> 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.
Anyone is welcome to open a Jira issue. Jira assistance is at
http://db.apache.org/derby/DerbyBugGuidelines.html .
-jean
[1]
http://mail-archives.apache.org/mod_mbox/db-derby-user/200605.mbox/%3cDD522070-780A-4C31-B095-0009E49EFEA2@SUN.com%3e
RE: "generated by default" question
Posted by Michael Segel <ms...@segel.com>.
> -----Original Message-----
> From: news [mailto:news@sea.gmane.org] On Behalf Of hilz
> Sent: Thursday, June 08, 2006 11:28 PM
> To: derby-user@db.apache.org
> Subject: Re: "generated by default" question
>
> Well...
> A good solution would probably be if someone would look at the HSQLDB
> code and see how they do it because it works like a charm over there!
>
[mjs]
It would be best if you wrote your own code.
As a contributor to Apache, you agree to indemnify Apache against any
lawsuit that may occur due to your contribution.
Please remember that Open Source is not a license to steal.
(I don't think that you were suggesting this, however, it is too easy and
tempting to cut and paste aka "borrow" code.)
Please note that the lawsuit between Caldera and IBM is based on the
"borrowing" of code. Whether the suit has any merit, it will still cost you
to defend yourself.
Having said that, Daniel already made a suggestion which is in use today by
other databases....
The simplest solution would be to find the MAX value, rest the sequence to
MAX() +1 and re-insert the row.
There are other possible solutions.
It's disappointing that certain people don't accept this as a bug and that
neither Sun nor IBM, which sell support for Derby, are not stepping forward
to fix this issue.
Just my .02 cents.
-Mike
>
>
> Craig L Russell 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!
> >
Re: "generated by default" question
Posted by hilz <hs...@hotmail.com>.
Well...
A good solution would probably be if someone would look at the HSQLDB
code and see how they do it because it works like a charm over there!
Craig L Russell 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!
>
Re: "generated by default" question
Posted by Michael Segel <de...@segel.com>.
On Tuesday 30 May 2006 1:22 pm, Mamta Satoor wrote:
> On 5/30/06, Michael Segel <de...@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+GE
>NERATED+BY+DEFAULT+identity+columns....-t735573.html#a1944069
> <http://www.nabble.com/-Derby-359-Skipping+over+user+inserted+values+into+G
>ENERATED+BY+DEFAULT+identity+columns....-t735573.html#a1944069> Section
> 4.21 had information on how sequence generation happens in it's own
> transactions.
>
So why didn't you include 9.21?
Again 4.21 has nothing to do with the issue of what happens when an Identity
column hits a value that is already in use.
(I suggest you re-read what you posted. It deals with the fact that identity
sequences need to be outside of 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.
>
Ah then thats the crux of the problem.
4.14 started to talk about this and then identified more issues in 9.21 which
you didn't post.
What people have seen, along with the existing JIRA entry is that Derby does
not comply with what should be expected behavior.
Thus its a bug.
Here's another way to look at it.
Derby fails to insert a row that complies with the criteria for entering a row
without a value in the identity column.
> 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:
[SNIP]
--
--
Michael Segel
Principal
Michael Segel Consulting Corp.
derby@segel.com
(312) 952-8175 [mobile]
Re: "generated by default" question
Posted by Mamta Satoor <ms...@gmail.com>.
On 5/30/06, Michael Segel <de...@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 <Cr...@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]
>
Re: "generated by default" question
Posted by "Bernt M. Johnsen" <Be...@Sun.COM>.
>>>>>>>>>>>> Bernt M. Johnsen wrote (2006-05-30 20:20:48):
> I have not studied this well enough to conclude wether Derby's current
> behaviour is compliant with the SQL 2000 spec or not.
>
> But there is nothing in the Derby charter that requires Derby to be
> SQL 2000 compliant, so if you're right, Derby is not compliant, but
> that's no bug, that's a feature, especially if it's documented.
Hmmm... thought "SQL 2000 spec" referred to MS SQL 2000 Server (As far
as I know, there is no SQL 2000 standard).
But as far as I read the SQL 2003 *Standard*, Derby behaves according
to the standard.
In the following example:
ij> create table t (i integer generated by default as identity primary key, n varchar(10));
0 rows inserted/updated/deleted
ij> insert into t (n) values ('insert-1');
1 row inserted/updated/deleted
ij> insert into t (i,n) values (2,'insert-2');
1 row inserted/updated/deleted
ij> insert into t (n) values ('insert-3');
ERROR 23505: 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 'SQL060530093932950' defined on 'T'.
ij> insert into t (n) values ('insert-4');
1 row inserted/updated/deleted
ij> select * from t;
I |N
----------------------
1 |insert-1
2 |insert-2
3 |insert-4
3 rows selected
ij>
The third insert here should fail since the internal sequence
generator will generate a value which violates a constraint. The
transaction rolls back, but not the internal sequence generator. Just
as specified. The fourth insert will then succeed.
--
Bernt Marius Johnsen, Database Technology Group,
Staff Engineer, Technical Lead Derby/Java DB
Sun Microsystems, Trondheim, Norway
Re: "generated by default" question
Posted by "Bernt M. Johnsen" <Be...@Sun.COM>.
>>>>>>>>>>>> Michael Segel wrote (2006-05-30 13:09:19):
> 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.
>
> 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.
I have not studied this well enough to conclude wether Derby's current
behaviour is compliant with the SQL 2000 spec or not.
But there is nothing in the Derby charter that requires Derby to be
SQL 2000 compliant, so if you're right, Derby is not compliant, but
that's no bug, that's a feature, especially if it's documented.
> So, when will either Sun or IBM fix this? ;-)
--
Bernt Marius Johnsen, Database Technology Group,
Staff Engineer, Technical Lead Derby/Java DB
Sun Microsystems, Trondheim, Norway
Re: "generated by default" question
Posted by Michael Segel <de...@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 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.
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.
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 <Cr...@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]
Re: "generated by default" question
Posted by Mamta Satoor <ms...@gmail.com>.
Based on the thread
http://www.nabble.com/-Derby-359-Skipping+over+user+inserted+values+into+GENERATED+BY+DEFAULT+identity+columns....-t735573.html#a1944069
Derby
behavior is SQL 2000 compatible.
Mamta
On 5/30/06, Craig L Russell <Cr...@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!
>
>
>
>
RE: "generated by default" question
Posted by 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!
Re: "generated by default" question
Posted by Craig L Russell <Cr...@Sun.COM>.
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!
RE: "generated by default" question
Posted by de...@segel.com.
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.
> >
Re: "generated by default" question
Posted by hilz <hs...@hotmail.com>.
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.
>
Re: "generated by default" question
Posted by Michael Segel <de...@segel.com>.
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.
--
--
Michael Segel
Principal
Michael Segel Consulting Corp.
derby@segel.com
(312) 952-8175 [mobile]