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 [EMAIL PROTECTED]
