Re: serial = autoincrement = without gaps ??

#1

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:

Quote:

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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: serial = autoincrement = without gaps ??

comp.databases.postgresql.novice comp.databases.postgresql.novice

Re: serial = autoincrement = without gaps ?? - 10-28-2004 , 03:25 AM