db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From <de...@segel.com>
Subject RE: "generated by default" question
Date Tue, 13 Jun 2006 17:47:47 GMT

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.


View raw message