Re: [GENERAL] Should SERIAL column have MAXVAL set on sequence

[Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>]

Jim Nasby <decibel(at)decibel(dot)org> writes:
> On Jul 23, 2007, at 9:02 AM, Woody Woodring wrote:
>> I ended up fixing my queue table with the following to avoid the  
>> issue in the future:
>> 
>> ALTER SEQUENCE transfer_transferid_seq MAXVALUE 2147483647 CYCLE;

> This does seem like a bug...

I see no bug here.  Woody's proposal of making CYCLE be the default
behavior is absolutely, totally unacceptable for most applications:
serial columns are supposed to be unique, not wrap around and re-use old
ID values after awhile.  That means we have to fail when the sequence
passes INT_MAX.  I don't see a lot of reason to prefer failing with
"reached maximum value of sequence" to "integer out of range".

Furthermore, if we did stick a different MAXVALUE on the sequence for an
int4 column, we'd be buying into a bunch of other corner cases:

* do we change the MAXVALUE if you use ALTER COLUMN TYPE to switch
from int4 to int8 or vice versa?

* what if the same sequence is feeding multiple columns?

Right now, SERIAL just creates a sequence, and the user can adjust the
sequence parameters afterwards if he wants to.  I think that behavior
is fine.

			regards, tom lane