Thank you all for your thinkings.
It seems like there's no realy good way to solve that kind of problem (maybe
used IDs). Thus I made a design change happen. We went to 64-Bit which
should solve the problem until the computers already fellt into dust.
> -----Original Message-----
> From: PFC [mailto:lists@boutiquenumerique.com]
> Sent: Freitag, 14. Jänner 2005 02:27
> To: Andrew Sullivan; 'pgsql-sql@postgresql.org'
> Subject: Re: [SQL] Column with recycled sequence value
>
>
>
> You could update all the fields which use this sequence
> number. You say
> you have a lot of activity so you must have mahy holes in
> your sequence,
> probably of the possible 2^32 values, only a fes millions are used.
>
> You can do the following :
>
> - Take down the database, back it up, and restart it
> with a single user,
> so only you can connect, using psql.
> - Create a table :
> CREATE TABLE translate ( new_id SERIAL PRIMARY KEY, old_id INTEGER,
> UNIQUE(old_id) ) WITHOUT OIDS;
>
> - Insert into this table all the used sequence values
> you have in your
> database. If you have all the proper constraints, these
> should come from
> only one table, so it should be straightformard :
>
> INSERT INTO translate (old_id) SELECT id FROM your_table;
>
> Thus the "translate" table maps old id's to a new
> sequence that you just
> started, and that means your new id's will be compactly
> arranged, starting
> at 1.
>
> - Update your existing table, joining it to the
> translate table, to
> replace the old id by the new id.
>
>
> > On Thu, Jan 13, 2005 at 06:08:20PM +0100, KÖPFERL Robert wrote:
> >> Hi,
> >>
> >> suppose I have a let's say heavy used table. There's a
> column containing
> >> UNIQUE in4
> >> values. The data type musn't exceed 32-Bit. Since however
> the table is
> >> heavy
> >> used 2^32 will be reached soon and then? There are far less than
> >> 4G-records
> >> saved thus these values may be reused. How can this be
> accomplished?
> >
> > You can set the sequence up to cycle (so once it gets to the end, it
> > wraps around to the beginning again). The keyword is CYCLE
> at CREATE
> > SEQUENCE time. It defaults to NO CYCLE.
> >
> > One potential problem, of course, are collisions on the table,
> > because some value wasn't cleared out. It sounds like you
> don't have
> > that problem though.
> >
> > A
> >
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to
> majordomo@postgresql.org
>
