Re: Serial Unique question

On Mon, Aug 15, 2005 at 11:07:31AM -0400, Madison Kelly wrote:
> This might seem like an odd question but I couldn't find the answer
> in the docs (did I miss the obvious?).

The serial type is a just convenient way to define an integer column
that takes its default value from a sequence, so look for documentation
on sequences. Here are some links for the latest release:

http://www.postgresql.org/docs/8.0/static/datatype.html#DATATYPE-SERIAL
http://www.postgresql.org/docs/8.0/static/sql-createsequence.html
http://www.postgresql.org/docs/8.0/static/functions-sequence.html

> I want to use a 'serial uniue' column in a table but there is likely
> to be many, many inserts and deletes from this column. I was wondering,
> what happens when the serial value reaches '2,147,483,647'? Does it roll
> back over to '1' and keep going or will the database start erroring out?

Sequences are 64 bits, so if you have a 32-bit serial column then
you'll probably get an "integer out of range" error when nextval()
returns a value higher than 2^31-1 (2,147,483,647). To learn about
what happens when all 64 bits are exhausted, see the CYCLE and NO
CYCLE options of the CREATE SEQUENCE command.

> This isn't likely to be a problem any time soon, but over the course of
> a year or more it might be.

Consider using bigserial instead of serial -- you'll get 2^63-1
values instead of 2^31-1. If you consume one million values per
second, it'll take about 300,000 years for the sequence to cycle.

--
Michael Fuhr