Skip site navigation (1) Skip section navigation (2)

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

From: Zoltan Boszormenyi <zb(at)cybertec(dot)at>
To: Woody Woodring <george(dot)woodring(at)iglass(dot)net>
Cc: Jim Nasby <decibel(at)decibel(dot)org>, pgsql-bugs(at)postgresql(dot)org
Subject: Re: [GENERAL] Should SERIAL column have MAXVAL set on sequence
Date: 2007-07-24 19:19:55
Message-ID: 46A650DB.5020706@cybertec.at (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-general
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(at)nasby(dot)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/



In response to

pgsql-bugs by date

Next:From: Tom LaneDate: 2007-07-24 19:35:34
Subject: Re: [GENERAL] Should SERIAL column have MAXVAL set on sequence
Previous:From: Jim NasbyDate: 2007-07-24 18:52:12
Subject: Re: [GENERAL] Should SERIAL column have MAXVAL set on sequence

pgsql-general by date

Next:From: Tom LaneDate: 2007-07-24 19:35:34
Subject: Re: [GENERAL] Should SERIAL column have MAXVAL set on sequence
Previous:From: Robert LandrumDate: 2007-07-24 19:15:29
Subject: GiST Support in 8.1

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group