Re: 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
>