serial field approaching limit

Posted in 2000

Topics: Triggers, Constraints & Referential Integrity

austin castro — Tue, 29 Feb 2000 10:00:49 -0600 — source: Informix-list mailing list archive (1991-1998)

This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. ------_=_NextPart_001_01BF82CE.25DA0290 Content-Type: text/plain; charset="iso-8859-1" Hi Guys(and Gals), A collegue of mine is having the following problem: The highest number that can be assigned to a serial datatype in Informix7.24 is 2147483647. He has a table that is frequently updated with both adds and deletes. As items are added, the serial datatype is approaching this maximum number. There are many gaps in between that could be recycled and he needs to find a way to do this. The only option he can find is to reload the table, but this would cause problems with other tables that use this serial column as a foreign key. Is there any way to recycle old numbers in a serial datatype column? TIA Austin ------_=_NextPart_001_01BF82CE.25DA0290 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN"> <HTML> <HEAD> <META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; = charset=3Diso-8859-1"> <META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version = 5.5.2448.0"> <TITLE>serial field approaching limit</TITLE> </HEAD> <BODY> Hi Guys(and Gals),         A collegue = of mine is having the following problem:   The highest number that can be assigned to a = serial datatype in Informix7.24 is 2147483647.  He has a table = that is frequently updated with both adds and deletes.  As items = are added, the serial datatype is approaching this maximum = number.  There are many gaps in between that could be recycled and = he needs to find a way to do this.  The only option he can find is = to reload the table, but this would cause problems with other tables = that use this serial column as a foreign key. Is there any way to recycle old numbers in a serial = datatype column? TIA Austin </BODY> </HTML> ------_=_NextPart_001_01BF82CE.25DA0290--

↪ replying to austin castro

Francois Baligant — Fri, 03 Mar 2000 12:48:36 +0100 — source: Informix-list mailing list archive (1991-1998)

Informix 2000 got a new type called SERIAL8 which got much much higher limit. Francois austin castro wrote: > > This message is in MIME format. Since your mail reader does not understand > this format, some or all of this message may not be legible. > > ------_=_NextPart_001_01BF82CE.25DA0290 > Content-Type: text/plain; > charset="iso-8859-1" > > Hi Guys(and Gals), > > A collegue of mine is having the following problem: > > The highest number that can be assigned to a serial datatype in > Informix7.24 is 2147483647. He has a table that is frequently updated with > both adds and deletes. As items are added, the serial datatype is > approaching this maximum number. There are many gaps in between that could > be recycled and he needs to find a way to do this. The only option he can > find is to reload the table, but this would cause problems with other tables > that use this serial column as a foreign key. > > Is there any way to recycle old numbers in a serial datatype column? > > TIA > Austin > > ------_=_NextPart_001_01BF82CE.25DA0290 > Content-Type: text/html; > charset="iso-8859-1" > Content-Transfer-Encoding: quoted-printable > > <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN"> > <HTML> > <HEAD> > <META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; = > charset=3Diso-8859-1"> > <META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version = > 5.5.2448.0"> > <TITLE>serial field approaching limit</TITLE> > </HEAD> > <BODY> > > Hi Guys(and Gals), > > >         A collegue = > of mine is having the following problem: > > >   The highest number that can be assigned to a = > serial datatype in Informix7.24 is 2147483647.  He has a table = > that is frequently updated with both adds and deletes.  As items = > are added, the serial datatype is approaching this maximum = > number.  There are many gaps in between that could be recycled and = > he needs to find a way to do this.  The only option he can find is = > to reload the table, but this would cause problems with other tables = > that use this serial column as a foreign key. > > Is there any way to recycle old numbers in a serial = > datatype column? > > > > > TIA > Austin > > > </BODY> > </HTML> > ------_=_NextPart_001_01BF82CE.25DA0290--

↪ replying to austin castro

Art S. Kagel & Family — Mon, 27 Mar 2000 10:46:37 -0500 — source: Informix-list mailing list archive (1991-1998)

austin castro wrote: > > This message is in MIME format. Since your mail reader does not understand > this format, some or all of this message may not be legible. > > ------_=_NextPart_001_01BF82CE.25DA0290 > Content-Type: text/plain; > charset="iso-8859-1" > > Hi Guys(and Gals), > > A collegue of mine is having the following problem: > > The highest number that can be assigned to a serial datatype in > Informix7.24 is -2147483647. He has a table that is frequently updated with > both adds and deletes. As items are added, the serial datatype is > approaching this maximum number. There are many gaps in between that could > be recycled and he needs to find a way to do this. The only option he can > find is to reload the table, but this would cause problems with other tables > that use this serial column as a foreign key. > > Is there any way to recycle old numbers in a serial datatype column? If you set the serial to -2147483647 (ALTER TABLE tabmn MODIFY colnm SERIAL (-2147483647)) the next insert will recycle the next serial number to 1. As long as you have a UNIQUE index, UNIQUE constraint, or PRIMARY KEY constraint on that column alone it will skip to the next unused serial value. This will be slowER than just appending the next sequential number so if you can ALTER the column to SERIAL8 do so. -- Art S. Kagel & Family kagel@erols.com

↪ replying to Art S. Kagel & Family

Jonathan Leffler — Tue, 28 Mar 2000 05:53:04 GMT — source: Informix-list mailing list archive (1991-1998)

"Art S. Kagel & Family" wrote: > austin castro wrote: > > > > This message is in MIME format. Since your mail reader does not understand > > this format, some or all of this message may not be legible. > > > > ------_=_NextPart_001_01BF82CE.25DA0290 > > Content-Type: text/plain; > > charset="iso-8859-1" > > > > Hi Guys(and Gals), > > > > A collegue of mine is having the following problem: > > > > The highest number that can be assigned to a serial datatype in > > Informix7.24 is -2147483647. He has a table that is frequently updated with > > both adds and deletes. As items are added, the serial datatype is > > approaching this maximum number. There are many gaps in between that could > > be recycled and he needs to find a way to do this. The only option he can > > find is to reload the table, but this would cause problems with other tables > > that use this serial column as a foreign key. > > > > Is there any way to recycle old numbers in a serial datatype column? > > If you set the serial to -2147483647 (ALTER TABLE tabmn MODIFY colnm > SERIAL (-2147483647)) the next insert will recycle the next serial > number to 1. As long as you have a UNIQUE index, UNIQUE constraint, > or PRIMARY KEY constraint on that column alone it will skip to the > next unused serial value. This will be slowER than just appending > the next sequential number so if you can ALTER the column to SERIAL8 > do so. No, it doesn't skip to the next unused value; it skips to the next value and errors if that value is still in use and there is a unique constraint on the serial column. If you removed the unique constraint, or never added it in the first place, then it simply ends up with duplicate entries. ...unless someone changed the rules without telling me, which would be dashed unsporting, old chap! If it is an option (meaning you use 9.x servers and can use INT8 and SERIAL8 types in your applications), then going to SERIAL8 is the simple way of dealing with the problem. Otherwise, consider a wholesale renumbering (apt to be very hard, but very effective). If neither of those is an option, you are in for a tough time. -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v1.00.PC1 -- see http://www.perl.com/CPAN #include <disclaimer.h>

↪ replying to Jonathan Leffler

kagel@bloomberg.net — Fri, 07 Apr 2000 11:49:31 -0400 — source: Informix-list mailing list archive (1991-1998)

Jonathan Leffler wrote: > "Art S. Kagel & Family" wrote: > > > austin castro wrote: > > > > > > Hi Guys(and Gals), > > > > > > A collegue of mine is having the following problem: > > > > > > The highest number that can be assigned to a serial datatype in > > > Informix7.24 is -2147483647. He has a table that is frequently updated with > > > both adds and deletes. As items are added, the serial datatype is > > > approaching this maximum number. There are many gaps in between that could > > > be recycled and he needs to find a way to do this. The only option he can > > > find is to reload the table, but this would cause problems with other tables > > > that use this serial column as a foreign key. > > > > > > Is there any way to recycle old numbers in a serial datatype column? > > > > If you set the serial to -2147483647 (ALTER TABLE tabmn MODIFY colnm > > SERIAL (-2147483647)) the next insert will recycle the next serial > > number to 1. As long as you have a UNIQUE index, UNIQUE constraint, > > or PRIMARY KEY constraint on that column alone it will skip to the > > next unused serial value. This will be slowER than just appending > > the next sequential number so if you can ALTER the column to SERIAL8 > > do so. > > No, it doesn't skip to the next unused value; it skips to the > next value and errors if that value is still in use and there > is a unique constraint on the serial column. If you removed > the unique constraint, or never added it in the first place, > then it simply ends up with duplicate entries. > > ...unless someone changed the rules without telling me, which > would be dashed unsporting, old chap! OK I just tried it again. After the ALTER trying to insert zero into the SERIAL column gets a duplicate key error for each key that already exists but increments the 'next serial' value in the TABLESPACE TABLESPACE so that eventually, if you keep trying, the missing or deleted keys do indeed get replaced by new rows. OK it is not practical, but it does work, more or less (less than more, I know). > > > If it is an option (meaning you use 9.x servers and can use INT8 > and SERIAL8 types in your applications), then going to SERIAL8 is > the simple way of dealing with the problem. Otherwise, consider a > wholesale renumbering (apt to be very hard, but very effective). > If neither of those is an option, you are in for a tough time. > > -- > Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) > Guardian of DBD::Informix v1.00.PC1 -- see http://www.perl.com/CPAN > #include <disclaimer.h> Art S. Kagel

serial field approaching limit

Related threads

Your privacy choices