Re: Column with recycled sequence value

Andrew Sullivan <ajs(at)crankycanuck(dot)ca> writes:

> 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.

Alternatively you can go through the database and make sure all the foreign
keys are declared and marked ON UPDATE CASCADE. Then go through and renumber
all your entries sequentially starting at 1 and reset your sequence.

I'm not sure this is such a hot idea really. But I don't really like the idea
of letting the sequence wrap around much either. You'll have to consider the
pros and cons of each approach (and of just moving to bigserial too).

If you're going to do this you'll want an index on all the foreign key
columns. That is, the columns referring to this value from other tables.
Otherwise the automatic updates would be very slow.

And will probably want to schedule down-time for this. Otherwise application
code that holds values in local state might get very confused.

I think I would do it with a program that connects and updates each record
individually and commits periodically rather than with a single big update.
Just because I like having control and having things that give me progress
information and can be interrupted without losing work.

Oh, and this won't work if you have any external references to these values
from outside your database. Say if the value is something like a customer
account number that you've previously sent to customers...

--
greg