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:[EMAIL PROTECTED] > 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 > [EMAIL PROTECTED] > ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
