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

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