pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedNumbering Rows (SEQUENCE, OID) questions
4+ messages / 3 participants
[nested] [flat]
* Numbering Rows (SEQUENCE, OID) questions
@ 2001-12-16 11:56 Terrence Brannon <metaperl@mac.com>
0 siblings, 1 reply; 4+ messages in thread
From: Terrence Brannon @ 2001-12-16 11:56 UTC (permalink / raw)
To: pgsql-sql
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?
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?
3 - What does one do once the rollover limit is reached if one
wants to continue to add new row numbers?
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Numbering Rows (SEQUENCE, OID) questions
@ 2001-12-16 19:42 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Terrence Brannon <metaperl@mac.com>
0 siblings, 1 reply; 4+ messages in thread
From: Stephan Szabo @ 2001-12-16 19:42 UTC (permalink / raw)
To: Terrence Brannon <metaperl@mac.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Numbering Rows (SEQUENCE, OID) questions
@ 2001-12-17 10:41 Terrence Brannon <metaperl@mac.com>
parent: Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 1 reply; 4+ messages in thread
From: Terrence Brannon @ 2001-12-17 10:41 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: pgsql-sql
On Sunday, December 16, 2001, at 11:42 AM, Stephan Szabo wrote:
> 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.
Where do you get this information? If I am reading the Momjian
book correctly, it disagrees with you:
Every row in POSTGRESQL is assigned a unique, normally invisible
number called an object identification number (OID). When the
software is initialized with initdb , 12.1 a counter is created
and set to approximately seventeen-thousand. 12.2 The counter is
used to uniquely number every row. Although databases may be
created and destroyed, the counter continues to increase. It is
used by all databases, so identification numbers are always
unique. No two rows in any table or in any database will ever
have the same object ID.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Numbering Rows (SEQUENCE, OID) questions
@ 2001-12-17 17:04 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Terrence Brannon <metaperl@mac.com>
0 siblings, 0 replies; 4+ messages in thread
From: Bruce Momjian @ 2001-12-17 17:04 UTC (permalink / raw)
To: Terrence Brannon <metaperl@mac.com>; +Cc: Stephan Szabo <sszabo@megazone23.bigpanda.com>; pgsql-sql
>
> On Sunday, December 16, 2001, at 11:42 AM, Stephan Szabo wrote:
>
> > 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.
>
> Where do you get this information? If I am reading the Momjian
> book correctly, it disagrees with you:
>
> Every row in POSTGRESQL is assigned a unique, normally invisible
> number called an object identification number (OID). When the
> software is initialized with initdb?, 12.1 a counter is created
> and set to approximately seventeen-thousand. 12.2 The counter is
> used to uniquely number every row. Although databases may be
> created and destroyed, the counter continues to increase. It is
> used by all databases, so identification numbers are always
> unique. No two rows in any table or in any database will ever
> have the same object ID.
The book asssume you are not going to roll over the counter. Very large
installations have been concerned about such rollover after inserting >4
billion rows. We have not gotten any actual report of it happening, but
it could theoretically happen.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2001-12-17 17:04 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-12-16 11:56 Numbering Rows (SEQUENCE, OID) questions Terrence Brannon <metaperl@mac.com>
2001-12-16 19:42 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-12-17 10:41 ` Terrence Brannon <metaperl@mac.com>
2001-12-17 17:04 ` Bruce Momjian <pgman@candle.pha.pa.us>
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