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)