Thread: Re: [GENERAL] Should SERIAL column have MAXVAL set on sequence
Moving to -bugs.
On Jul 23, 2007, at 9:02 AM, Woody Woodring wrote:
> I have a table in our DB that functions as a queue with a SERIAL
> column for
> its primary key. At 4am this weekend I started getting the error:
>
> ERROR: integer out of range
>
> Which was attributed to the sequence incrementing past the size of
> the int4
> serial column after several years of operation.
>
> I was able to set the sequence back to 1 and everything was happy.
>
> I was wondering if the SERIAL column should set the
> MAXVAL=2147483647 when
> it creates the sequence?
>
> 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;
I can confirm this is still the case in HEAD:
decibel=# select max_value from s_s_seq ;
max_value
---------------------
9223372036854775807
(1 row)
This does seem like a bug...
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
Jim Nasby <decibel@decibel.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
Jim Nasby =EDrta: > Moving to -bugs. > > On Jul 23, 2007, at 9:02 AM, Woody Woodring wrote: >> I have a table in our DB that functions as a queue with a SERIAL=20 >> column for >> its primary key. At 4am this weekend I started getting the error: >> >> ERROR: integer out of range >> >> Which was attributed to the sequence incrementing past the size of=20 >> the int4 >> serial column after several years of operation. >> >> I was able to set the sequence back to 1 and everything was happy. >> >> I was wondering if the SERIAL column should set the MAXVAL=3D2147483647= =20 >> when >> it creates the sequence? >> >> I ended up fixing my queue table with the following to avoid the=20 >> issue in >> the future: >> >> ALTER SEQUENCE transfer_transferid_seq MAXVALUE 2147483647 CYCLE; Did you already delete old records? Otherwise it will create duplicate=20 IDs... Alternatively you can alter the field to be BIGINT. > I can confirm this is still the case in HEAD: > > decibel=3D# select max_value from s_s_seq ; > max_value > --------------------- > 9223372036854775807 > (1 row) > > This does seem like a bug... No, it is by design. Nothing is stopping you from altering your sequence after creating your table with SERIALs. Anyway, [BIG]SERIAL is just a "macro" in PostgreSQL. BTW sequences were modified to produce BIGINT values some releases back. > --=20 > Jim Nasby jim@nasby.net > EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) > > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend > Best regards, Zolt=E1n B=F6sz=F6rm=E9nyi --=20 ---------------------------------- Zolt=E1n B=F6sz=F6rm=E9nyi Cybertec Geschwinde & Sch=F6nig GmbH http://www.postgresql.at/