From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: Terrence Brannon <metaperl@mac.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Numbering Rows (SEQUENCE, OID) questions
Date: Sun, 16 Dec 2001 11:42:41 -0800 (PST)
Message-ID: <20011216112020.D42764-100000@megazone23.bigpanda.com> (raw)
In-Reply-To: <EBA7F074-F21B-11D5-92F7-003065C2A10C@mac.com>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: sszabo@megazone23.bigpanda.com, metaperl@mac.com
Subject: Re: Numbering Rows (SEQUENCE, OID) questions
In-Reply-To: <20011216112020.D42764-100000@megazone23.bigpanda.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox