OID's as Primary Keys

From: "Richard Teviotdale" <no(dot)maps(dot)richard(at)satcomresources(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: OID's as Primary Keys
Date: 2001-11-07 22:36:03
Message-ID: 00cc01c167dc$9858b160$0100007f@satcomresources.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


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.

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

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).

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 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?

Are there any issues with restoring a database from a pg_dump file?
-----------------------------------------------------------------------------------------------------------
Thank you

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Richard Teviotdale 2001-11-07 22:50:15 OID's as Primary Keys
Previous Message Richard Teviotdale 2001-11-07 20:52:41 Re: OID's as Primary Keys