Re: OID's as Primary Keys

Short answer, NO.

"Richard Teviotdale" <no.maps.richard@satcomresources.com> writes:

>
> Is an Object Identifier (OID) is a good choice for a primary key within
> a database table?
> ------------------------------------------------------------------------
> -----------------------------------
> Lets explore this question...
>
> OID's are created automatically, so even if I create a specific INTEGER
> field within my table as a primary key (PK), I will end up with an OID
> anyway. Thats got to be an waste of some space.

oids are created automatically in every release prior to the imminent
7.2 release.  That alone should raise warning flags about their use.
You can still create tables with oids in 7.2 (and oids may even be the
default, I can't remember), but the developers have sent a pretty
clear message that depending on oids is a bad idea.

> I would need to specify the -o option to the pg_dump command, when
> performing backups. Otherwise you would loose all your PK's.

A minor inconvenience mostly.

> Because you cannot SERIALIZE OID's, Invoice numbers that run
> successively would not be possible (Although OID's are successively
> derived by the database, they are assigned first come, first served to
> whichever table an INSERT specifies).

Sequences can also leave "gaps" if you have transactions that don't
commit, so that's not a huge win for sequences.  On the other hand you
can use a single sequence for various tables and get a key that is
unique across several tables.  Basically sequences are both more
flexible, and more user friendly than using oids.

> After an INSERT the OID property is available imediately, allowing
> code efficiency. Ironically, this same OID propery is used to get
> the new record's PK, using an additional SQL statement, if you don't
> use the OID as the PK.

I think that depends on the language you are using to do development.
For example, I happen to know that PyGreSQL (the Python interface I
use) returns the oid on when you use it's insert() method, and I think
that PHP can do this as well (it's been a while since I have done any
PHP work).  However, PostgreSQL allows you to use functions like
nextval() and currval() to return the primary key directly (and
inexpensively).  This is what I generally do.

> I hope this spurs some discussion about...
>
> Other than lacking sequental PK's, are there other disadvantages with
> the OID PK choice?
>
> Does the use of OID PK's limit the maximum number of records allowed
> throughout the entire database?

Yes it does.

> Are there any issues with restoring a database from a pg_dump file?

I don't have any first hand experience with this.  Perhaps someone
else that is using oids as a primary key could chime in.

> Thank you

No, thank YOU.

Jason