Обсуждение: Serial Unique question
Hi all, This might seem like an odd question but I couldn't find the answer in the docs (did I miss the obvious?). 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. I suppose one answer is to periodically drop and recreat the table (no long-term important data will be in there. Data will last usually less that 1sec before being deleted). Thanks all! Madison
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