How can we cycle the serial number in Informix for column of type SERIAL?

Question

We are having an issue wherein our Informix DB serial number column, which is of type SERIAL is reaching the max limit. We are re-setting it manually to "1" every time when we have this issue.

Is there a way to fix this using sequence/cycle without making any application changes?

We insert the data by giving the value as "0" and Informix automatically takes care of increment it.

Answer 1

Change the column type to bigserial, which is a 64-bit auto-increment type that will fit in the long datatype of your application without having to make changes.

As pointed out by @JonathanLeffler, if your applications retrieve the last serial generated, you'll have to change that code because the last added bigserial is retrieved in a different way.

Answer 2

You can recycle a SERIAL column by inserting a row with an explicit number that is 2,147,483,647 (2³¹-1), and then inserting another row with the number 0. This will reset the counter back to 1, but subsequent attempts to insert a new serial number (with 0 as the inserted value) may collide with a pre-existing row, and the INSERT will then fail (but the next attempt will use the next serial number).

Example:

SQL[2416]: CREATE TABLE serial_wraparound
         > (
         >     number SERIAL NOT NULL PRIMARY KEY,
         >     name VARCHAR(20) NOT NULL UNIQUE
         > );
SQL[2417]: INSERT INTO serial_wraparound(number, name) VALUES(0, "The first");
SQL[2418]: INSERT INTO serial_wraparound(number, name) VALUES(0, "The second");
SQL[2419]: SELECT * FROM serial_wraparound;
1|The first
2|The second
SQL[2420]: INSERT INTO serial_wraparound(number, name) VALUES(2147483647, "The last");
SQL[2421]: SELECT * FROM serial_wraparound;
1|The first
2|The second
2147483647|The last
SQL[2422]: INSERT INTO serial_wraparound(number, name) VALUES(0, "Recycling");
SQL -268: Unique constraint (jleffler.u162_426) violated.
ISAM -100: ISAM error:  duplicate value for a record with unique key.
SQLSTATE: 23000 at /dev/stdin:8
SQL[2423]: INSERT INTO serial_wraparound(number, name) VALUES(0, "Recycling");
SQL -268: Unique constraint (jleffler.u162_426) violated.
ISAM -100: ISAM error:  duplicate value for a record with unique key.
SQLSTATE: 23000 at /dev/stdin:9
SQL[2424]: INSERT INTO serial_wraparound(number, name) VALUES(0, "Recycling");
SQL[2425]: SELECT * FROM serial_wraparound;
1|The first
2|The second
2147483647|The last
3|Recycling
SQL[2426]: 

Similar behaviour applies to SERIAL8 and BIGSERIAL columns, except that the wraparound value is 9,223,372,036,854,775,807 (2⁶³-1). If you're repeatedly running out of your 2 billion serial numbers, maybe you should consider updating your application to use BIGSERIAL so you can use numbers up to 9 quintillion. It's probably simpler in the long run. (Do not use SERIAL8 (or INT8); I mentioned it for completeness. New code should use BIGSERIAL and BIGINT. Yes, there are reasons.)

(The command interpreter is my SQLCMD — available from the ESQL/C section of the Software Repository at the International Informix Users Group, the IIUG.)

You can also use ALTER TABLE to change the value in the column, but be aware that the primary key attribute gets lost if you don't respecify it in the ALTER TABLE. This is a single sequence of statements (number 2455 if you're really curious):

+ DROP TABLE IF EXISTS serial_wraparound;
+ CREATE TABLE serial_wraparound (number SERIAL NOT NULL PRIMARY KEY, NAME VARCHAR(20) NOT NULL UNIQUE);
+ trace off;
+ INFO INDEXES FOR serial_wraparound 
P|jleffler|u171_494|jleffler| 171_494|number|||||||||||||||
U|jleffler|u171_495|jleffler| 171_495|name|||||||||||||||
+ INSERT INTO serial_wraparound(number, NAME) VALUES(0, "The first");
+ INSERT INTO serial_wraparound(number, NAME) VALUES(0, "The second");
+ SELECT * FROM serial_wraparound;
1|The first
2|The second
+ ALTER TABLE serial_wraparound MODIFY(number SERIAL(248761) NOT NULL PRIMARY KEY);
+ trace off;
+ INFO INDEXES FOR serial_wraparound 
P|jleffler|u171_498|jleffler| 171_494|number|||||||||||||||
U|jleffler|u171_495|jleffler| 171_495|name|||||||||||||||
+ continue on;
+ INSERT INTO serial_wraparound VALUES(0, "The modified");
+ INSERT INTO serial_wraparound VALUES(0, "The petrified");
+ SELECT * FROM serial_wraparound;
1|The first
2|The second
248761|The modified
248762|The petrified
+ ALTER TABLE serial_wraparound MODIFY(number SERIAL(2147483647) NOT NULL PRIMARY KEY);
+ trace off;
+ INFO INDEXES FOR serial_wraparound 
P|jleffler|u171_500|jleffler| 171_494|number|||||||||||||||
U|jleffler|u171_495|jleffler| 171_495|name|||||||||||||||
+ INSERT INTO serial_wraparound VALUES(0, "The inconsequential");
+ INSERT INTO serial_wraparound VALUES(0, "The ungainly");
SQL -268: Unique constraint (jleffler.u171_500) violated.
ISAM -100: ISAM error:  duplicate value for a record with unique key.
SQLSTATE: 23000 at <<temp>>:27
+ SELECT * FROM serial_wraparound;
1|The first
2|The second
248761|The modified
248762|The petrified
2147483647|The inconsequential
+ INSERT INTO serial_wraparound VALUES(0, "The insubordinate");
SQL -268: Unique constraint (jleffler.u171_500) violated.
ISAM -100: ISAM error:  duplicate value for a record with unique key.
SQLSTATE: 23000 at <<temp>>:29
+ SELECT * FROM serial_wraparound;
1|The first
2|The second
248761|The modified
248762|The petrified
2147483647|The inconsequential
+ INSERT INTO serial_wraparound VALUES(0, "The piscatorial");
+ SELECT * FROM serial_wraparound;
1|The first
2|The second
248761|The modified
248762|The petrified
2147483647|The inconsequential
3|The piscatorial
+ INSERT INTO serial_wraparound VALUES(0, "The equatorial");
+ SELECT * FROM serial_wraparound;
1|The first
2|The second
248761|The modified
248762|The petrified
2147483647|The inconsequential
3|The piscatorial
4|The equatorial

This shows that the ALTER TABLE can be used to set the next value — first using 248,761 (arbitrary, even if not random) and then 2,147,483,647 again. You can't decrease the number, which is why you have to use the 2³¹-1 trick still.

The INFO INDEXES statements are there to ensure that the primary key constraint was preserved — some of the intermediate versions lost the primary key (uniqueness) constraint, giving different results from what I wanted. The trace on (and off) commands turn command echoing off; you don't want to know the convolutions that INFO INDEXES goes through (or you can find out by getting SQLCMD for yourself if you do). It is a statement managed by SQLCMD (and DB-Access); it is not understood by the Informix server per se. It is similar to OUTPUT and LOAD and UNLOAD, etc, in this respect.