Обсуждение: Should SERIAL column have MAXVAL set on sequence

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;

Thanks,
Woody

----------------------------------------
iGLASS Networks
211-A S. Salem St
Apex NC 27502
(919) 387-3550 x813
www.iglass.net

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)