Skip site navigation (1) Skip section navigation (2)

Re: OIDs

From: "Josh Berkus" <josh(at)agliodbs(dot)com>
To: "Guthrie, John" <JGuthrie(at)air(dot)org>,"'pgsql-novice(at)postgresql(dot)org'" <pgsql-novice(at)postgresql(dot)org>
Subject: Re: OIDs
Date: 2002-06-03 16:27:50
Message-ID: web-1497453@davinci.ethosmedia.com (view raw or flat)
Thread:
Lists: pgsql-novice
John,

> First, why would one choose to not use an OID as the
> primary-key/foreign-key-reference for a table? It is unique (good
> thing) and
> lacks business meaning (another good thing), so it seems to me to be
> just
> the thing, a nice surrogate key. Is it just because of the name? I
> would
> agree that as a foreign key reference a la:

I really, really need to write a FAQ about this.  

1. OIDs do not back-up and restore easily.
2. OIDs become non-unique in high-transaction databases outside of the
system tables (the "OID wraparound" issue).
3. You have little administrative control over OIDs: you cannot roll
them back, set the current OID, or even check it accurately (i.e. if
you add a row to a table with the OID as its primary column, you will
have difficulty finding the id of the new record).
4. Given the above issues, I've seen it suggested on HACKERS  that the
user-accessable OID column will be going away by PostgreSQL 8.0

The information that you have probably seen about using the OID as the
primary key dates back to PostgreSQL 7.0.3 and is no longer accurate.

-Josh Berkus

In response to

  • OIDs at 2002-06-03 14:42:05 from Guthrie, John

pgsql-novice by date

Next:From: Chris PizzoDate: 2002-06-03 16:50:53
Subject: Question on joining tables
Previous:From: Guthrie, JohnDate: 2002-06-03 14:42:05
Subject: OIDs

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group