pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Numbering 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>
  2001-12-16 19:42 ` Re: Numbering Rows (SEQUENCE, OID) questions Stephan Szabo <sszabo@megazone23.bigpanda.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 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   ` Re: Numbering Rows (SEQUENCE, OID) questions 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-16 11:56 Numbering Rows (SEQUENCE, OID) questions Terrence Brannon <metaperl@mac.com>
  2001-12-16 19:42 ` Re: Numbering Rows (SEQUENCE, OID) questions Stephan Szabo <sszabo@megazone23.bigpanda.com>
@ 2001-12-17 10:41   ` Terrence Brannon <metaperl@mac.com>
  2001-12-17 17:04     ` Re: Numbering Rows (SEQUENCE, OID) questions Bruce Momjian <pgman@candle.pha.pa.us>
  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-16 11:56 Numbering Rows (SEQUENCE, OID) questions Terrence Brannon <metaperl@mac.com>
  2001-12-16 19:42 ` Re: Numbering Rows (SEQUENCE, OID) questions Stephan Szabo <sszabo@megazone23.bigpanda.com>
  2001-12-17 10:41   ` Re: Numbering Rows (SEQUENCE, OID) questions Terrence Brannon <metaperl@mac.com>
@ 2001-12-17 17:04     ` Bruce Momjian <pgman@candle.pha.pa.us>
  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