Holes in SERIAL series

Posted in 1997

Peter Wiley — Tue, 15 Apr 1997 01:45:03 GMT — source: Informix-list mailing list archive (1991-1998)

I'm building a new system and making heavy use of SERIAL columns. I had assumed that transaction logging prevented the incrementing of SERIAL numbers if the transaction was rolled out. It appears not. If you have an INSERT INTO <sometable> fail, even if it is inside a transaction, the SERIAL counter goes up by one. This is most annoying, as I wanted to use the SERIAL values to check for holes (ie deleted records) in my tables. More a last resort audit feature which I can do via triggers and SPL, but still... Is this normal behaviour? Because I don't like it :-) Engine is SE 5.07.UC1 on Solaris 2.5/SPARC. Tool is R4GL 4.16.UC1 on Solaris 2.5/SPARC. Thanks, Peter Wiley