Re: OID's as Primary Keys

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(at)svana(dot)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.