Thread: OIDs

I have a couple of questions about OIDs....

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:

 create table employee_child (
   parent oid references employee(oid),
   name  varchar(4) ....

looks kind of bad, but that is the worst drawback my
non-postgresql-experienced brain can conjure up.

Second, what is the scale of an OID? Does it map to an INT4 or an INT8? I
will be accessing the data via JDBC so I need to know what java type to use.

Third, is there any bottleneck or other problem associated with using OIDs
versus turning them off and using a self-defined SERIAL field? Or is the
opposite true?

Thanks!
John

-+-+-+-+-+-+-+-+-
john guthrie
american institutes for research
jguthrie@air.org
202-298-2716

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