Re: Numbering Rows (SEQUENCE, OID) questions

On Sun, 16 Dec 2001, Terrence Brannon wrote:

> The Momjian book is excellent (in spite of some of the bonehead
> reviews on amazon.com). I just finished the "Numbering Rows"
> section and have a few questions. None of these were in the FAQ,
> BTW.
>
> 1 - are OIDs ever re-used if rows are deleted?

OIDs wraparound, but they don't just fill holes, so uniqueness
isn't guaranteed unless you have something like a unique index
on oid.

> 2 - for both SEQUENCE and OID, when might we expect them to
> rollover? Ie, what is the largest integer we can use for either
> of these?

oids are int4. In 7.1 and earlier sequences are int4, for 7.2 I
believe they're int8.

> 3 - What does one do once the rollover limit is reached if one
> wants to continue to add new row numbers?

If you're using oids, and you have a unique index on oid, you'll
get random failures insert if you happen to hit an already used oid.
If you don't have the unique index you'll get dups.

If you're using a sequence, you should be able to compress down
holes in the sequence and change rows that refer to rows of this one
and then point the sequence to give you the next available number.
A not terribly efficient way would be to make a new sequence, lock the
table you're compressing and any dependents, generate a table with the old
key value and a nextval from the sequence and then update both the
original table and dependents based on the mapping table and then set the
original table's sequence to get the following value and drop the new
sequence and table all in one transaction.