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(at)commandprompt(dot)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
------------
2147483646
2147483647
1
2
3
(5 lignes)

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