Using oids

From: Malcolm Warren <malcolm(at)villeinitalia(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Using oids
Date: 2003-09-03 08:27:34
Message-ID: opruw0f8o1oa2ek7@10.0.0.1
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers

While updating to 7.3.4 I note with some alarm the following passage in
README.Debian.migration.gz written by Oliver Elphick:

"Some schema designs rely on the use of oids as row identifiers. This is
definitely not recommended, not least because oids are not guaranteed to
exist in all future versions of PostgreSQL. Oids are an internal feature
only. They are not suitable as candidate keys, since they are not
guaranteed to be unique; furthermore, the starting point for oids is likely
to change whenever a change to the database structure occurs."

While I have not used oids to join tables, I have used them extensively in
programming, because if Postgres has supplied a unique number for each row,
why on earth should I bother supplying another one of my own?

Like many people starting with Postgres, three or four years ago I
carefully read Bruce Momjian's excellent introductory book on Postgres,
which explained many initially difficult concepts with such clarity.

The book states "Every row in POSTGRESQL is assigned a unique, normally
invisible number called an object identification number (OID). When the
software is initialized with initdb, a counter is created and set to
approximately seventeen-thousand. 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."

Further down we read:
"Object identification numbers can be used as primary and foreign key
values in joins. Since every row has a unique object ID, a separate column
is not needed to hold the row's unique number."

On the next page are listed the limitations of oids, for example they are
nonsequential, nonmodifiable, and not backed up by default, but for my uses
these were not problems at all. I have merely used the oid number as a
temporary unique identifier before assigning a permanent booking number to
it, which takes about a nanosecond, and in other similar cases.

To sum up: The Debian migration gzip file declares that oids are not
guaranteed to be unique, issues dire warnings about using them as keys and
worst of all states that they may be phased out in the future.

The book states that they are unique, tells you how to use them, actually
gives an example of using them as primary and foreign keys (which
fortunately I decided was not very wise) and certainly doesn't say anything
about phasing them out in the future.

Can anybody shed any light on this?
Malcolm Warren

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Varun Kacholia 2003-09-03 08:52:28 Recursive Selects
Previous Message Ian Harding 2003-09-03 08:10:14 Re: how to call a TCL function from within a plpgsql trigger?

Browse pgsql-hackers by date

  From Date Subject
Next Message Shridhar Daithankar 2003-09-03 09:10:38 Re: Using oids
Previous Message Gavin Sherry 2003-09-03 08:18:40 Re: Regarding PostgreSQL Doubt