Re: serial = autoincrement = without gaps ??

Hi,

(A small note first: Please don't post in HTML. It makes
reading your message more difficult, and might prevent some
people from providing valuable answers.)

go_speedy(at)freenet(dot)de wrote:
> I want to use IDs in my tables which are autoincremented (by inserting new
> data), unique and without gaps. I figured out that the datatype serial shou
> ld provide this besides the last feature. <br>
> Moreover I figured out that if I use serial as datatype for my IDs it is al
> lways increment if I insert sth., if I use nextval(...) and also if a trans
> action is aborted.<br>
> Now if I do some inserts and deletes in my database gaps are generated beca
> use the serial is allways incremented. Am I right that there is no possibil
> ity to reset the serial-datatype or to avoid gaps without gaining problems?
> My problem at this is that I expect to run the database some years and I
> imagine that some day the serial-datatype evokes an overrun which corrupts
> the database.<br>

If the latter is the only reason why you want to avoid
gaps, then I suggest you use BIGSERIAL instead of SERIAL.
BIGSERIAL uses a 64bit integer. Even if you have one
million (!) inserts per second -- which I doubt -- it will
overflow in about 300,000 years. Should be enough.

Best regards
Oliver

--
Oliver Fromme, secnetix GmbH & Co KG, Oettingenstr. 2, 80538 München
Any opinions expressed in this message may be personal to the author
and may not necessarily reflect the opinions of secnetix in any way.

"Unix gives you just enough rope to hang yourself --
and then a couple of more feet, just to be sure."
-- Eric Allman