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 írta:

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;

Did you already delete old records? Otherwise it will create duplicate
IDs...
Alternatively you can alter the field to be BIGINT.

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...

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.

--
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án Böszörményi

--
----------------------------------
Zoltán Böszörményi
Cybertec Geschwinde & Schönig GmbH
http://www.postgresql.at/

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