alter next serial value
Frank asked why ALTER TABLE ... MODIFY (id SERIAL(10)) reported success but new inserts kept continuing from the current high value. Art Kagel explained a SERIAL counter can only be raised, not lowered; the workaround is to set it to the maximum 2147483647, insert a row with 0 so the counter wraps to 1, then raise it to the value you want. Jonathan Leffler detailed how the stored high-water value behaves (explicit values raise it, failed inserts still increment it) and warned that resetting it low causes duplicate-key failures, so chasing gap-free serials is usually not worth it. The thread ends with discussion of unload/reload gaps and wish-list features.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Folks,
IDS11.50 FC8, Linux
I was testing to reset a serial column to lower value ( smaller than the
current highest serial value).
When I run ,
alter table abc_test modify (id serial (10));
It said: table altered .
But the next serial number still keeps going up from the current highest (
tested by another insert).
Comments?
Thanks,
Frank
--e89a8f234c3dd22c8104c2ff0ddc
You can only increase the SERIAL value. However, if you set it to 2^31-1
(2147483647), the highest value it can hold, then insert a row with a zero
in the SERIAL column, it will wrap around to '1', then you can increase it
to whatever value you want to.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Thu, Jun 21, 2012 at 1:43 PM, FRANK <yunyaoqu@gmail.com> wrote:
> Folks,
>
> IDS11.50 FC8, Linux
>
> I was testing to reset a serial column to lower value ( smaller than the
> current highest serial value).
>
> When I run ,
>
> alter table abc_test modify (id serial (10));>
> It said: table altered .
>
> But the next serial number still keeps going up from the current highest (
> tested by another insert).
>
> Comments?
>
> Thanks,
> Frank
>
> --e89a8f234c3dd22c8104c2ff0ddc
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--14dae9340563091d3f04c2ffc0e5
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?
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
It's my understanding that the value of a serial column cannot be altered when inserting or updating a row. I've also noticed that if I delete several rows, unload all rows, delete/re-create the table and load those rows back in, it will accept gaps in the serial column as a result of the missing rows which were deleted.. It would be nice if serial datatypes provided more conditional functionality.
If yo unload, recreate the table, and reload, then you are loading explicit serial values from the original table back into the new table so of course the deleted serial values are skipped, in this version of the table they never existed at all! If you unload by selecting zero for the serial column then reload the data there will be no gaps. What conditional functionality would you want? Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Sun, Jun 24, 2012 at 4:38 PM, FRANK J. COMPUTER <frank_in_pr@hotmail.com>wrote: > It's my understanding that the value of a serial column cannot be altered > when > inserting or updating a row. I've also noticed that if I delete several > rows, > unload all rows, delete/re-create the table and load those rows back in, it > will accept gaps in the serial column as a result of the missing rows which > were deleted.. It would be nice if serial datatypes provided more > conditional > functionality. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae9340cadfa76ce04c33fc963
Oh, while you cannot update an existing row's serial/serialbigserial column value, you CAN insert an explicit value on any of those column types and the value will be accepted and saved with the row. Only if you insert zero or NULL is the next serial value inserted instead of the zero/null. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Sun, Jun 24, 2012 at 6:57 PM, Art Kagel <art.kagel@gmail.com> wrote: > If yo unload, recreate the table, and reload, then you are loading explicit > serial values from the original table back into the new table so of course > the deleted serial values are skipped, in this version of the table they > never existed at all! If you unload by selecting zero for the serial > column then reload the data there will be no gaps. > > What conditional functionality would you want? > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and do not reflect on my employer, Advanced DataTools, the IIUG, nor any > other organization with which I am associated either explicitly, > implicitly, or by inference. Neither do those opinions reflect those of > other individuals affiliated with any entity with which I am affiliated nor > those of the entities themselves. > > On Sun, Jun 24, 2012 at 4:38 PM, FRANK J. COMPUTER > <frank_in_pr@hotmail.com>wrote: > > > It's my understanding that the value of a serial column cannot be altered > > when > > inserting or updating a row. I've also noticed that if I delete several > > rows, > > unload all rows, delete/re-create the table and load those rows back in, > it > > will accept gaps in the serial column as a result of the missing rows > which > > were deleted.. It would be nice if serial datatypes provided more > > conditional > > functionality. > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --14dae9340cadfa76ce04c33fc963 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae93405635f675e04c33fd788
Understood!.. I once had a customer who blew up his parent table and had no recent backup! The child table was joined to parent via fk_id. We had a tough time reconstructing the parent table because some rows had been deleted and the pk_id was gone. We had to enter parent table customer master info so that the pk_id would match up with the appropiate fk_id in the child table.. Additional functionality?: 1. SERIAL for CHAR datatypes (ascending in collatind seq). 2. SERIAL for INT (descending order). 3. Option for SERIAL to reuse deleted rows so as to avoid gaps. 4. etc.