Re: Re: Best practice for long-lived journal tables: bigint or recycling IDs?

Mark Stosberg wrote:
> On Tue, 8 Jul 2008 17:20:13 -0400
> Alvaro Herrera <alvherre@commandprompt.com> wrote:
> 
> > 3. Deal with wraparound by ensuring that the applications behave sanely
> 
> Wrap-around?  
> 
> Exceeding the max size of "int" looks more like a brick wall than wrap-around to me:
> 
>  insert into t values (2147483648);
>  ERROR:  integer out of range

Hmm, you can alter the sequence so that it wraps around at the point it
reaches INT_MAX.  So inserting this number would never actually happen.

alvherre=# create table t (a serial);
NOTICE:  CREATE TABLE créera des séquences implicites « t_a_seq » pour la colonne serial « t.a »
CREATE TABLE
alvherre=# alter sequence t_a_seq maxvalue 2147483647;
ALTER SEQUENCE
alvherre=# alter sequence t_a_seq cycle;
ALTER SEQUENCE
alvherre=# select setval('t_a_seq', 2147483645);  setval   
------------2147483645
(1 ligne)

alvherre=# insert into t default values;
INSERT 0 1
alvherre=# insert into t default values;
INSERT 0 1
alvherre=# insert into t default values;
INSERT 0 1
alvherre=# insert into t default values;
INSERT 0 1
alvherre=# insert into t default values;
INSERT 0 1
alvherre=# select * from t;    a      
------------21474836462147483647         1         2         3
(5 lignes)


-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.