Sequence Roll Over

Mat <psql-mail@freeuk.com> writes:

What happens when a sequence for auto-numbered records gets to its
maximum value and another record is added?

You can set the sequence to wrap around, or to report errors on
subsequent nextval attempts; I believe the latter is the default
behavior.

Does using a sequence for this limit the amount of records the table can
hold?

Surely you can work out the implications for yourself. But I find it
hard to believe that this is a practical concern if you're using bigint
serial fields.

If records are deleted, is there a standard way of renumbering all
records so their ID's are consecutive starting from zero and then
reseting the sequence to the highest ID value?

You could probably gin up something involving resetting the sequence
and then doing
UPDATE tab SET col = nextval('seq');
However, I can't imagine that this is a good idea. What's the point of
assigning a made-up primary key if you're going to change it later?
Everything I've read on database design says that you don't change
a record's primary key, ever.

regards, tom lane