Thread: OID as Primary Key

Hello,
I have problems with using OID as PK. I have created a
UNIQUE INDEX on the oid on a relation called CourseEvents.
The relation that is supposed to have a FK to CourseEvents
is CourseEventsForums and it has a field called ceid which
has oid as dtatype.
But when I try to create a FK to that table I get following
error:
---
PostgreSQL said: ERROR: UNIQUE constraint matching given
keys for referenced table "courseevents" not found
Your query:
ALTER TABLE CourseEventForums ADD CONSTRAINT
RefCourseEvents43
FOREIGN KEY (ceid)
REFERENCES CourseEvents(oid)
---
What is wrong?

Is it to prefer to use oid as PK? Or should I use SERIAL
instead? I thought it might be more efficient to use oid
and a waste of space to add another integer as PK.


Thanks in advance,
Jonas Bengtsson

Jonas Bengtsson <jonas.b@home.se> writes:
> I have problems with using OID as PK.

This strikes me as a fairly bad idea in any case.  What happens if you
must dump and reload your database?  Better to use a SERIAL column as
primary key.

            regards, tom lane

Can't you do a dump with the oid's?

But when I want to know the primary key of the inserted row
I have to do an extra select query. If I use oid I just use
pg_getlastoid() in php.
And it is redundant data to store another integer.

Comments?

/Jonas B

> Jonas Bengtsson <jonas.b@home.se> writes:
> > I have problems with using OID as PK.
>
> This strikes me as a fairly bad idea in any case.  What
happens if you
> must dump and reload your database?  Better to use a
SERIAL column as
> primary key.
>
>             regards, tom lane
>



Mvh,
Jonas Bengtsson

--- Jonas Bengtsson <jonas.b@home.se> wrote:
> Can't you do a dump with the oid's?

Yes, but I would never suggest it.

> But when I want to know the primary key of the inserted row
> I have to do an extra select query. If I use oid I just use
> pg_getlastoid() in php.
> And it is redundant data to store another integer.

You should not use oid's as primary keys just because you want
to save a little HD space.

I contributed a some code on PHPBuilder for a function that
returns the insert id of a SERIAL primary key.  You can view it
here:
http://www.phpbuilder.com/snippet/download.php?type=snippet&id=304
.  It works well for me and it does not kludge my code like
using the oid does.

Brent

__________________________________________________
Do You Yahoo!?
Get email at your own domain with Yahoo! Mail.
http://personal.mail.yahoo.com/

And you can get the last SERIAL inserted using currval.

MikeA

>> -----Original Message-----
>> From: Brent R. Matzelle [mailto:bmatzelle@yahoo.com]
>> Sent: 22 March 2001 16:36
>> To: pgsql-general@postgresql.org
>> Subject: Re: Re: [GENERAL] OID as Primary Key
>>
>>
>> --- Jonas Bengtsson <jonas.b@home.se> wrote:
>> > Can't you do a dump with the oid's?
>>
>> Yes, but I would never suggest it.
>>
>> > But when I want to know the primary key of the inserted row
>> > I have to do an extra select query. If I use oid I just use
>> > pg_getlastoid() in php.
>> > And it is redundant data to store another integer.
>>
>> You should not use oid's as primary keys just because you want
>> to save a little HD space. 
>>
>> I contributed a some code on PHPBuilder for a function that
>> returns the insert id of a SERIAL primary key.  You can view it
>> here:
>> http://www.phpbuilder.com/snippet/download.php?type=snippet&id=304
>> .  It works well for me and it does not kludge my code like
>> using the oid does.
>>
>> Brent
>>
>> __________________________________________________
>> Do You Yahoo!?
>> Get email at your own domain with Yahoo! Mail.
>> http://personal.mail.yahoo.com/
>>
>> ---------------------------(end of
>> broadcast)---------------------------
>> TIP 1: subscribe and unsubscribe commands go to
>> majordomo@postgresql.org
>>

_________________________________________________________________________
This e-mail and any attachments are confidential and may also be privileged and/or copyright
material of Intec Telecom Systems PLC (or its affiliated companies). If you are not an
intended or authorised recipient of this e-mail or have received it in error, please delete
it immediately and notify the sender by e-mail. In such a case, reading, reproducing,
printing or further dissemination of this e-mail is strictly prohibited and may be unlawful.
Intec Telecom Systems PLC. does not represent or warrant that an attachment hereto is free
from computer viruses or other defects. The opinions expressed in this e-mail and any
attachments may be those of the author and are not necessarily those of Intec Telecom
Systems PLC.

This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.
__________________________________________________________________________


On Thu, 22 Mar 2001, Jonas Bengtsson wrote:

> Hello,
> I have problems with using OID as PK. I have created a
> UNIQUE INDEX on the oid on a relation called CourseEvents.
> The relation that is supposed to have a FK to CourseEvents
> is CourseEventsForums and it has a field called ceid which
> has oid as dtatype.
> But when I try to create a FK to that table I get following
> error:
> ---
> PostgreSQL said: ERROR: UNIQUE constraint matching given
> keys for referenced table "courseevents" not found
> Your query:
> ALTER TABLE CourseEventForums ADD CONSTRAINT
> RefCourseEvents43
> FOREIGN KEY (ceid)
> REFERENCES CourseEvents(oid)
> ---
> What is wrong?

The message is a bit misleading.  Oid isn't a "user" column,
and currently you can only create references to user
columns.  Given the issues involved with oid (since it's
system wide it rolls over faster than a serial, you have
to make sure to dump with oids, the fact that you have
much less control over its value if you need to do
something), I'm not 100% sure that's a bad thing, although
IIRC references to oid are on the todo list.

But why doesn't PostgreSQL complain when a create a unique index on oid? I
think it is quite confusing..


/Jonas B

-----Original Message-----
From: Stephan Szabo [mailto:sszabo@megazone23.bigpanda.com]
Sent: Thursday, March 22, 2001 5:59 PM
To: Jonas Bengtsson
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] OID as Primary Key

On Thu, 22 Mar 2001, Jonas Bengtsson wrote:

> Hello,
> I have problems with using OID as PK. I have created a
> UNIQUE INDEX on the oid on a relation called CourseEvents.
> The relation that is supposed to have a FK to CourseEvents
> is CourseEventsForums and it has a field called ceid which
> has oid as dtatype.
> But when I try to create a FK to that table I get following
> error:
> ---
> PostgreSQL said: ERROR: UNIQUE constraint matching given
> keys for referenced table "courseevents" not found
> Your query:
> ALTER TABLE CourseEventForums ADD CONSTRAINT
> RefCourseEvents43
> FOREIGN KEY (ceid)
> REFERENCES CourseEvents(oid)
> ---
> What is wrong?

The message is a bit misleading.  Oid isn't a "user" column,
and currently you can only create references to user
columns.  Given the issues involved with oid (since it's
system wide it rolls over faster than a serial, you have
to make sure to dump with oids, the fact that you have
much less control over its value if you need to do
something), I'm not 100% sure that's a bad thing, although
IIRC references to oid are on the todo list.

That's FAQ 4.16.2/4.16.3:
4.16.2) How do I get the value of a SERIAL insert?
One approach is to to retrieve the next SERIAL value from the sequence
object with the nextval() function before inserting and then insert it
explicitly. Using the example table in 4.16.1, that might look like this:
    $newSerialID = nextval('person_id_seq');
    INSERT INTO person (id, name) VALUES ($newSerialID, 'Blaise Pascal');
You would then also have the new value stored in $newSerialID for use in
other queries (e.g., as a foreign key to the person table). Note that the
name of the automatically created SEQUENCE object will be named
<table>_<serialcolumn>_seq, where table and serialcolumn are the names of
your table and your SERIAL column, respectively.
Alternatively, you could retrieve the assigned SERIAL value with the
currval() function after it was inserted by default, e.g.,
    INSERT INTO person (name) VALUES ('Blaise Pascal');
    $newID = currval('person_id_seq');
Finally, you could use the OID returned from the INSERT statement to look
up the default value, though this is probably the least portable approach.
In Perl, using DBI with Edmund Mergl's DBD::Pg module, the oid value is
made available via $sth->{pg_oid_status} after $sth->execute().
4.16.3) Don't currval() and nextval() lead to a race condition with other
users?
No. This is handled by the backends.

Mike Mascari
mascarm@mascari.com

-----Original Message-----
From:    Jonas Bengtsson [SMTP:jonas.b@home.se]
Sent:    Thursday, March 22, 2001 11:54 AM
To:    pgsql-general@postgresql.org
Subject:    RE: Re: [GENERAL] OID as Primary Key

Isn't is better to use nextval(). If there are many simultaneous users
vurrval might give the value of another insert.

/Jonas B

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Michael Ansley
Sent: Thursday, March 22, 2001 5:50 PM
To: pgsql-general@postgresql.org
Subject: RE: Re: [GENERAL] OID as Primary Key

And you can get the last SERIAL inserted using currval.
MikeA

>> -----Original Message-----
>> From: Brent R. Matzelle [ mailto:bmatzelle@yahoo.com
<mailto:bmatzelle@yahoo.com> ]
>> Sent: 22 March 2001 16:36
>> To: pgsql-general@postgresql.org
>> Subject: Re: Re: [GENERAL] OID as Primary Key
>>
>>
>> --- Jonas Bengtsson <jonas.b@home.se> wrote:
>> > Can't you do a dump with the oid's?
>>
>> Yes, but I would never suggest it.
>>
>> > But when I want to know the primary key of the inserted row
>> > I have to do an extra select query. If I use oid I just use
>> > pg_getlastoid() in php.
>> > And it is redundant data to store another integer.
>>
>> You should not use oid's as primary keys just because you want
>> to save a little HD space.
>>
>> I contributed a some code on PHPBuilder for a function that
>> returns the insert id of a SERIAL primary key.  You can view it
>> here:
>> http://www.phpbuilder.com/snippet/download.php?type=snippet&id=304
<http://www.phpbuilder.com/snippet/download.php?type=snippet&id=304>
>> .  It works well for me and it does not kludge my code like
>> using the oid does.
>>
>> Brent

You can create indexes on them. It's specifically
a limitation in the fk related code.  IIRC, there
were a couple of places in the triggers that
would fail on execution if you tried to reference
oid, so it now fails on creation rather than
execution of constraints.  There are a bunch
of things that need to get done to those triggers
anyway, so possibly for 7.2.

On Thu, 22 Mar 2001, Jonas Bengtsson wrote:

> But why doesn't PostgreSQL complain when a create a unique index on oid? I
> think it is quite confusing..

Why doesn't PostgreSQL has the same feature as MySQL (auto_increment) when
dealing with PK? In nearly every relation I use a SERIAL.
In PHP with MySQL you just use mysql_insert_id() instead of double queries.

I have to admit that I like PostgreSQL a lot more than MySQL. Is there any
good, objective, comparison between these to available? I'm mostly
interested in the performance.

/Jonas B

-----Original Message-----
From: Stephan Szabo [mailto:sszabo@megazone23.bigpanda.com]
Sent: Thursday, March 22, 2001 6:09 PM
To: Jonas Bengtsson
Cc: pgsql-general@postgresql.org
Subject: RE: [GENERAL] OID as Primary Key


You can create indexes on them. It's specifically
a limitation in the fk related code.  IIRC, there
were a couple of places in the triggers that
would fail on execution if you tried to reference
oid, so it now fails on creation rather than
execution of constraints.  There are a bunch
of things that need to get done to those triggers
anyway, so possibly for 7.2.

On Thu, 22 Mar 2001, Jonas Bengtsson wrote:

> But why doesn't PostgreSQL complain when a create a unique index on oid? I
> think it is quite confusing..

From: "Jonas Bengtsson" <jonas.b@home.se>

> Why doesn't PostgreSQL has the same feature as MySQL (auto_increment) when
> dealing with PK? In nearly every relation I use a SERIAL.
> In PHP with MySQL you just use mysql_insert_id() instead of double
queries.

There's nothing to stop you defining a sequence (CREATE SEQUENCE) separate
from a field if you like. You can manually use currval('seqname') and
nextval('seqname'). You can access the sequence created by a serial in the
same way - use \d tablename to see the sequence name.

IIRC mysql limits you to one auto_increment column per table whereas you can
have as many sequences used by a table as seems reasonable to you.

> I have to admit that I like PostgreSQL a lot more than MySQL. Is there any
> good, objective, comparison between these to available? I'm mostly
> interested in the performance.

I think mysql have some comparisons and greatbridge did a test too. Take
them all with a pinch of salt - the only way to tell for sure which works
better for you is to try both.

In my experience, for small systems with relatively few clients and doing
mostly reads mysql can be 3-5 times faster. Once you start doing a lot of
writes with a couple of dozen clients and multi-way joins PG catches up
fast.

The two systems actually have completely different characters so check your
requirements carefully and pick which works for the current project.

- Richard Huxton

Jonas Bengtsson wrote:
>
> Can't you do a dump with the oid's?
>
> But when I want to know the primary key of the inserted row
> I have to do an extra select query. If I use oid I just use
> pg_getlastoid() in php.
> And it is redundant data to store another integer.
>
> Comments?

I have often used OID as primary key, usually for the reason's you cite.
IMHO, in the long run, the two major extra worries are often not worth
it:

1) You must explicitly dump and restore OIDs all the time
2) You loose generic SQL compatibility

On the other hand, we've never had a failure from theses causes. In
short, using OIDs seems to work well enough for us, but the downsides in
practice have been larger than we expected, and the upsides smaller. In
our case, the extra serial would be a small part of the overall data, so
I kind of wish I had not used OIDs.

Just my $0.02 worth.

--
Karl