Received: from candle.pha.pa.us (216-55-132-35.dialup.tnt01.san-diego.abac.net [216.55.132.35]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fBHH4bm75649 for ; Mon, 17 Dec 2001 12:04:37 -0500 (EST) (envelope-from pgman@candle.pha.pa.us) Received: (from pgman@localhost) by candle.pha.pa.us (8.11.6/8.10.1) id fBHH47405974; Mon, 17 Dec 2001 12:04:07 -0500 (EST) From: Bruce Momjian Message-Id: <200112171704.fBHH47405974@candle.pha.pa.us> Subject: Re: Numbering Rows (SEQUENCE, OID) questions In-Reply-To: <94F668C2-F2DA-11D5-92F7-003065C2A10C@mac.com> "from Terrence Brannon at Dec 17, 2001 02:41:09 am" To: Terrence Brannon Date: Mon, 17 Dec 2001 12:04:07 -0500 (EST) CC: Stephan Szabo , pgsql-sql@postgresql.org X-Mailer: ELM [version 2.4ME+ PL90 (25)] MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII X-Archive-Number: 200112/183 X-Sequence-Number: 6005 > > 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