Serial Unique question

Madison Kelly <linux@alteeve.com> writes:

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? This isn't likely to be a
problem any time soon, but over the course of a year or more it
might be.

Use a "bigserial" instead?

-Doug

Douglas McNaught wrote:

Madison Kelly <linux@alteeve.com> writes:

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? This isn't likely to be a
problem any time soon, but over the course of a year or more it
might be.

Use a "bigserial" instead?

-Doug

Same issue, delaying a potential problem, not addressing it. :)

Madison

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

Madison Kelly <linux@alteeve.com> writes:

Douglas McNaught wrote:

Madison Kelly <linux@alteeve.com> writes:

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? This isn't likely to be a
problem any time soon, but over the course of a year or more it
might be.

Use a "bigserial" instead?
-Doug

Same issue, delaying a potential problem, not addressing it. :)

Do the math. :)

-Doug

Michael Fuhr wrote:

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.

Well, I should have done my math. :p I think that is a tolerable range
the (and no were near what my program will ever hit!)

Thanks!

Madison

On Mon, 2005-08-15 at 11:48 -0400, Madison Kelly wrote:

Douglas McNaught wrote:

Madison Kelly <linux@alteeve.com> writes:

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? This isn't likely to be a
problem any time soon, but over the course of a year or more it
might be.

Use a "bigserial" instead?

-Doug

Same issue, delaying a potential problem, not addressing it. :)

Madison

\h CREATE SEQUENCE
Command: CREATE SEQUENCE
Description: define a new sequence generator
Syntax:
CREATE [ TEMPORARY | TEMP ] SEQUENCE name [ INCREMENT [ BY ] increment ]
[ MINVALUE minvalue | NO MINVALUE ] [ MAXVALUE maxvalue | NO
MAXVALUE ]
[ START [ WITH ] start ] [ CACHE cache ] [ [ NO ] CYCLE ]

Based on the last argument, a sequence can be created to either cycle or
not; it would appear as though the default, when created automagically
by invoking the [big]serial "data type", is to not cycle; my guess in
that case would be that an error would be raised if you have reached
2^31 in your serial field.

Sven

Sven