Re: Serials.

Thus spake Grant
> Please see below for my table schema. I have two questions.
> 
> (1) Why is a sequence limited to 2147483647, it seems very small?

Yikes!  What are you counting?  :-)

The value 2147483647 is the largest value that can fit into an int.  It is
equal to 0x7fffffff in hex.  If you add one to that you get -2147483648.
I suppose the number could be doubled by using an unsigned int for the
serial type.  It should never be negative anyway.

> (2) If I reset the sequence, then try another insert. It will not insert
> anything until it cycles through all sequences and finds an unused one. It
> will give the following error each time it tries to insert a row with a
> used sequence:
> 
> PostgreSQL query failed: ERROR: Cannot insert a duplicate key into unique
> index releases_pkey 
> 
> How can I possibly get around this issue so that I can be sure to always
> have a free id without getting the error above?

Well, there are a lot of questions to ask before answering that one.  What
is the reason for resetting the sequence?  Do you actually have values
in that range and are hitting a ceiling.  Are there a lot of holes in
your sequence?

The short ansqwer is to use max and setval to reset your index above the
highest number but if holes are the problem then that won't help.  You
may need to run a script that renumbers down but don't forget to renumber
any tables that reference it.  Referential integrity constraints may
also get in your way.

Hey, this is PostgreSQL.  If you don't like the way that nextval works
then just create your own function with the behaviour that you need.  You
can keep your own list of numbers and fill holes and all sorts of things.

-- 
D'Arcy J.M. Cain <darcy@{druid|vex}.net>   |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 425 1212     (DoD#0082)    (eNTP)   |  what's for dinner.