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

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

Ah, that does look like the best solution. I'll confirm that will work for our cases.

Thanks!
   Mark


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