On Wed, Nov 07, 2001 at 03:36:03PM -0700, Richard Teviotdale wrote:
>
> Is an Object Identifier (OID) is a good choice for a primary key within a database table?
IMHO, no.
> -----------------------------------------------------------------------------------------------------------
> 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.
In newer versions, OIDs are optional on tables.
> I would need to specify the -o option to the pg_dump command, when
> performing backups. Otherwise you would loose all your PK's.
Yep
> 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).
And there are likely to be gaps, possibly quite large.
> 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.
Well, you don't need to execute an additional query to get the PK. You can
simply refer to currval('sequencename') in your other queries.
> -----------------------------------------------------------------------------------------------------------
> I hope this spurs some discussion about...
>
> Other than lacking sequental PK's, are there other disadvantages with the OID PK choice?
One pg_dump without -o and your database is screwed. You have some control
over sequence values, since you can reset sequences to any value. Copying
tables into other databases is a problem if you are relying on oids.
> Does the use of OID PK's limit the maximum number of records allowed throughout the entire database?
Makes no difference.
> Are there any issues with restoring a database from a pg_dump file?
No idea.
> Thank you
HTH,
--
Martijn van Oosterhout <kleptog@svana.org>
http://svana.org/kleptog/
> Magnetism, electricity and motion are like a three-for-two special offer:
> if you have two of them, the third one comes free.