Thread: 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@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.

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

On Thu, Nov 08, 2001 at 07:52:27AM -0700, Jason Earl wrote:
>
> Short answer, NO.

I agree. See below for my comments.

[snip]
>
> > 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.

In my case, when there's an operation that I do very often, I write a
function that takes as arguments the values I would put in an insert,
and that function does the insert and returns the currval(nnn_seq) value.

This means you still run one query, but instead of getting an OID back,
you get the sequence value. BIG downside is you have to make one function for
each table you do inserts on this way. You obviously do that for the tables
you do inserts the most often on, or in loops.

[snip]

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

The main problem I can see is if you want to move databases between
different machines, or you want to load a backup of a database under a
different name, etc. You can/will end up with OID conflicts at some point,
while using sequences will never conflict between 2 databases on the same
machine...
>
> Jason
>

just my 1/50$
Antoine