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?
Do 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
Please remove "on.maps" from address to reply.