IDS Forum

Re: alter next serial value

Posted By: Jonathan Leffler
Date: Sunday, 24 June 2012, at 4:09 p.m.

In Response To: Re: alter next serial value (FRANK J. COMPUTER)

On Sun, Jun 24, 2012 at 11:46 AM, FRANK J. COMPUTER <frank_in_pr@hotmail.com
> wrote:

> Are you attempting to re-use serial integers from rows which have been
> deleted?.. Does the SERIAL datatype keeps track of the row with the highest
> value, or does it have its own internal tracking, regardless whether the
> row
> with MAX(serial value) has been deleted or not?
>

There is a mixture of activities.

There is a storage location for the largest value currently recorded by the
SERIAL column. For definiteness, it contains the largest number already
inserted rather than the next number to be inserted; which of the two is
stored is immaterial to the overall effect. I'm also assuming there is a
unique constraint on the SERIAL column; if duplicates are allowed, then
duplicates don't cause any trouble during insertion (for all the havoc they
wreak on the rest of the logic that assumes serial numbers are unique).

When a new row is inserted without an explicit value for the SERIAL column
(0 or no value supplied), the appropriate number is used for the new row
and the storage location value is incremented. It does not matter if the
insertion fails because the value was already in use; the increment
occurs. If a new row is inserted with an explicit value, then if the
explicit value is larger than the storage location value, the storage
location value is set to the explicit value.

This means that if you have values 20-100 in a table, and then reset the
SERIAL value back to 1, then your first 19 inserts will succeed, but the
next 81 will fail (assuming that you only insert zeroes in the SERIAL
column). The moment you insert a row with an explicit value such as 200,
then you (a) leave a gap in your serial values, and (b) ensure that further
insertions will succeed. Note that inserting explicit negative values
never affects the storage location value; negative values are never
generated automatically, either.

Generally speaking, the nuisance value of dealing with failed insertions
due to repeated values is far greater than the nuisance value of dealing
with gaps in the sequence of serial numbers. To want to guarantee
continuous serial numbers is typically an exercise in futility, or of
wasted energy. If you must do it, you would be best off running a query to
find a value in the hole and then trying to insert a record with that
explicit value (remembering it might fail because someone else also chose
that value at the same time, so you'd have to repeat again--beware the
uncommitted insertion) than simply resetting and hoping for the best.

--
Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h>
Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org
"Blessed are we who can laugh at ourselves, for we shall never cease to be
amused."

--e89a8f2353bbaed4ca04c33d710f

Messages In This Thread

• alter next serial value
  FRANK -- Thursday, 21 June 2012, at 1:43 p.m.
  • Re: alter next serial value
    Art Kagel -- Thursday, 21 June 2012, at 2:33 p.m.
    • Re: alter next serial value
      FRANK J. COMPUTER -- Sunday, 24 June 2012, at 2:46 p.m.
      • Re: alter next serial value
        Jonathan Leffler -- Sunday, 24 June 2012, at 4:09 p.m.
        • Re: alter next serial value
          FRANK J. COMPUTER -- Sunday, 24 June 2012, at 4:38 p.m.
          • Re: alter next serial value
            Art Kagel -- Sunday, 24 June 2012, at 6:57 p.m.
            • Re: alter next serial value
              Art Kagel -- Sunday, 24 June 2012, at 7:01 p.m.
              • Re: alter next serial value
                FRANK J. COMPUTER -- Monday, 25 June 2012, at 3:56 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.