Re: Holes in SERIAL series

Peter Wiley wrote: > > 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 :-) Yes it is. SERIAL values are designed to be allocated once only. The problem with your example is that other users may also allocate numbers after yours. Any number of transactions could ROLLBACK leaving any number of "holes" in the number sequence, which would mean any number of available numbers requiring storage. x-) > Engine is SE 5.07.UC1 on Solaris 2.5/SPARC. > Tool is R4GL 4.16.UC1 on Solaris 2.5/SPARC. -- Mark. +------------------------------------------------------------------+ |Mark D. Stock - The West Solutions Group http://www.west.co.za | | The Informix FAQ is at http://www.iiug.org | |mailto:marks@west.co.za +-----------------------------------------+ |Tel: +27 11 803 2151 |If it doesn't work, force it! | |Fax: +27 11 803 2189 |If it breaks, it needed replacing anyway!| |Cell: +27 83 250 2325 |Well, that's how I code anyway! | +------------------------+-----------------------------------------+