Re: Q: Old version of INFOMIX, buggy-auto increment?

On Sun, 16 Nov 1997, Michael Segel wrote: > Brian Middlebro' wrote: > > > > Did old versions of INformix have a bug that would skip > > serialized values, that the database auto-increments? > > Well, its not really a bug. Its a feature. > > If you try entering a row with a non-zero value, if the row doesn't > exist, and is > current last serial value, the new serial value > is set to that row number +1. > > Does this help answer your question? There is at least one other possible cause of problems. BEGIN WORK; INSERT INTO SomeTable(SerialColumn) VALUES(0);ROLLBACK WORK; The serial number is allocated, but after the row is rolled back, there is no row with that serial number. But the increment is not rolled back! If the row inserted was allocated serial number 765432, the next insert will be allocated 765433. Again, this is not a bug; it is the way that SERIAL numbers work, and always has been. There is no guarantee that SERIAL numbers will always form a contiguous sequence; the guarantee is that the newly generated serial number will be distinct from any previous serial number, whether inserted as a literal (eg 123456) or as a dummy (0). And even that guarantee isn't maintained if you've wrapped the serial counter around the 2**31-1 limit; it will attempt to generate a new serial number, but may fail because it already exists (or it may insert a duplicate row if you don't have a unique constraint on the serial column). Yours, Jonathan Leffler (johnl@informix.com) #include <witticism.h>