Thread: Use of OIDS as primary keys

Use of OIDS as primary keys

From
Alan Wayne
Date:
Hi!

I'm wondering how I can use the system generated OIDS
as primary keys. Does postgre automatically fill in
the system generated oid when a field of type oid is
created as the primary key? And if so, how do I read
it back on a record just created so I can use it as a
secondary key in another table? And lastly, is it even
a good idea to use the oid as keys at all when I'm
going to eventually migrate the data from one machine
to another?

Any help would be appreciated,
AJW

__________________________________________________
Do You Yahoo!?
LAUNCH - Your Yahoo! Music Experience
http://launch.yahoo.com

Re: Use of OIDS as primary keys

From
Martijn van Oosterhout
Date:
On Sat, May 11, 2002 at 10:56:00PM -0700, Alan Wayne wrote:
> Hi!
>
> I'm wondering how I can use the system generated OIDS
> as primary keys. Does postgre automatically fill in
> the system generated oid when a field of type oid is
> created as the primary key? And if so, how do I read
> it back on a record just created so I can use it as a
> secondary key in another table? And lastly, is it even
> a good idea to use the oid as keys at all when I'm
> going to eventually migrate the data from one machine
> to another?

I'm sure this is mentioned in the FAQ somewhere but in postgres every tuple
has an OID (except in 7.2 where you can specify that you don't want them for
certain tables). It's not output by default but you can show it using
"select *,oid from table;".

Cons of using it as primary key:
- It's not guarenteed to be unique
- Forget the -o switch on your dump and your DB is hosed
- Moving to another machine can become a pain

Pros of using it as a primary key:
- INSERT tells you the oid it just inserted

Just use a sequence/serial. It's far clearer and more reliable.

HTH,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Canada, Mexico, and Australia form the Axis of Nations That
> Are Actually Quite Nice But Secretly Have Nasty Thoughts About America

Re: Use of OIDS as primary keys

From
Oliver Elphick
Date:
On Sun, 2002-05-12 at 06:56, Alan Wayne wrote:
> Hi!
>
> I'm wondering how I can use the system generated OIDS
> as primary keys. Does postgre automatically fill in

Don't do it!

Oids are not saved in a dump unless you specifically ask for them;
you'll be in a right mess if you forget and then need to restore from
the dump.  Then, oids aren't guaranteed to be unique.  In any case, your
database design should not depend on an internal feature of the database
implementation.

If there is no primary key in the data, create one by including a SERIAL
field (an integer with an associated next number generator) and use that
as the key.

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C

     "Watch ye and pray, lest ye enter into temptation. The
      spirit truly is ready, but the flesh is weak."
                           Mark 14:38

Attachment

Re: Use of OIDS as primary keys

From
Elaine Lindelef
Date:
>On Sun, 2002-05-12 at 06:56, Alan Wayne wrote:
> > Hi!
> >
> > I'm wondering how I can use the system generated OIDS
> > as primary keys. Does postgre automatically fill in
>
>Don't do it!
>
>Oids are not saved in a dump unless you specifically ask for them;
>you'll be in a right mess if you forget and then need to restore from
>the dump.  Then, oids aren't guaranteed to be unique.  In any case, your
>database design should not depend on an internal feature of the database
>implementation.
>
>If there is no primary key in the data, create one by including a SERIAL
>field (an integer with an associated next number generator) and use that
>as the key.
>
>--
>Oliver Elphick                                Oliver.Elphick@lfix.co.uk
>Isle of Wight                              http://www.lfix.co.uk/oliver
>GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C

I think it would be valuable if a discussion of this were added to
the FAQ somewhere. It took me a while to figure this out - in Oracle
and mySQL I was used to making my own autoincrement columns, and at
first usage it seemed that postgre was saving me from that bit of
drudgery. ;^) Eventually I realized I needed my own columns, but it's
not obvious and it's a bit of a pain to undo.

Elaine Lindelef


Re: Use of OIDS as primary keys

From
Bill Moran
Date:
Elaine Lindelef wrote:
>> On Sun, 2002-05-12 at 06:56, Alan Wayne wrote:
>> > I'm wondering how I can use the system generated OIDS
>> > as primary keys. Does postgre automatically fill in
>>
>> Don't do it!
>>
>> Oids are not saved in a dump unless you specifically ask for them;
>> you'll be in a right mess if you forget and then need to restore from
>> the dump.  Then, oids aren't guaranteed to be unique.  In any case, your
>> database design should not depend on an internal feature of the database
>> implementation.
>>
>> If there is no primary key in the data, create one by including a SERIAL
>> field (an integer with an associated next number generator) and use that
>> as the key.
>>
>> --
>> Oliver Elphick                                Oliver.Elphick@lfix.co.uk
>> Isle of Wight                              http://www.lfix.co.uk/oliver
>> GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
>
> I think it would be valuable if a discussion of this were added to the
> FAQ somewhere. It took me a while to figure this out - in Oracle and
> mySQL I was used to making my own autoincrement columns, and at first
> usage it seemed that postgre was saving me from that bit of drudgery.
> ;^) Eventually I realized I needed my own columns, but it's not obvious
> and it's a bit of a pain to undo.

I'm also curious about the use of WITHOUT OIDS in the creation of
tables.
Somewhere I gleaned from the docs that OIDS aren't necessary, but they are
a good idea when there is no primary key.  Thus, I've been adding "WITHOUT
OIDS" on any table that has a primary key.
Is there any drawback to this?  I figure it's saving 4 bytes per record,
right? (it adds up when you have 100,000 records)
But I don't understand why OIDs are ever necessary.  It seems like they
could be useful at times, but if I need something that works like a
primary key, I'll create a primary key.

I guess the ultimate question in all this is "Is there any ill effect from
using WITHOUT OIDS on a table that doesn't have a primary key?"

--
Bill Moran
Potential Technology
http://www.potentialtech.com


Re: Use of OIDS as primary keys

From
Martijn van Oosterhout
Date:
On Mon, May 13, 2002 at 09:26:20PM -0400, Bill Moran wrote:
> I'm also curious about the use of WITHOUT OIDS in the creation of
> tables.
> Somewhere I gleaned from the docs that OIDS aren't necessary, but they are
> a good idea when there is no primary key.  Thus, I've been adding "WITHOUT
> OIDS" on any table that has a primary key.
> Is there any drawback to this?  I figure it's saving 4 bytes per record,
> right? (it adds up when you have 100,000 records)
> But I don't understand why OIDs are ever necessary.  It seems like they
> could be useful at times, but if I need something that works like a
> primary key, I'll create a primary key.
>
> I guess the ultimate question in all this is "Is there any ill effect from
> using WITHOUT OIDS on a table that doesn't have a primary key?"

OIDs are used extensivly in the system tables to reference functions,
tables, attributes etcetera. I don't beleive they were ever particularly
useful for non-system tables (hence the option to remove them).

Do they actually save spaces (consider alignment issues and such)?
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Canada, Mexico, and Australia form the Axis of Nations That
> Are Actually Quite Nice But Secretly Have Nasty Thoughts About America

Re: Use of OIDS as primary keys

From
Tom Lane
Date:
Martijn van Oosterhout <kleptog@svana.org> writes:
> Do they actually save spaces (consider alignment issues and such)?

WITHOUT OIDS doesn't currently save any space --- the tuple header
layout is the same either way.  It should save a few microseconds
per row inserted (since you don't have to generate an OID) but
that's about it.

The main reason for inventing the feature was to postpone OID-counter
wraparound in large installations.  Wraparound isn't fatal, but can
be annoying --- for example, pg_dump may get confused about the best
order to dump tables in.

            regards, tom lane

Re: Use of OIDS as primary keys

From
Bill Moran
Date:
Tom Lane wrote:
> Martijn van Oosterhout <kleptog@svana.org> writes:
>
>>Do they actually save spaces (consider alignment issues and such)?
>
> WITHOUT OIDS doesn't currently save any space --- the tuple header
> layout is the same either way.  It should save a few microseconds
> per row inserted (since you don't have to generate an OID) but
> that's about it.
>
> The main reason for inventing the feature was to postpone OID-counter
> wraparound in large installations.  Wraparound isn't fatal, but can
> be annoying --- for example, pg_dump may get confused about the best
> order to dump tables in.

Thanks Tom.
So the upshot is that using WITHOUT OIDS on tables with primary keys
doesn't hurt a thing.  But it's a good idea to keep OIDS on tables
without primary keys, since it can improve the operation of certain
internal actions.  There are no space consideration because the OID
is part of a (currently) fixed data structure.

Do I understand correctly?

Thanks.

--
Bill Moran
Potential Technology
http://www.potentialtech.com


Re: Use of OIDS as primary keys

From
Tom Lane
Date:
Bill Moran <wmoran@potentialtech.com> writes:
> So the upshot is that using WITHOUT OIDS on tables with primary keys
> doesn't hurt a thing.  But it's a good idea to keep OIDS on tables
> without primary keys, since it can improve the operation of certain
> internal actions.  There are no space consideration because the OID
> is part of a (currently) fixed data structure.

There aren't any "internal actions" that care about OIDs, except for
OIDs in the system tables.  The recommendation to keep OIDs in user
tables without primary keys comes from the notion that you might use
the OID as a substitute primary key --- if you have no primary key
at all, then you're going to find yourself in trouble as soon as you
need to identify a specific row (eg, to correct a mistake).

However, because of the wraparound issue you can't really assume that
OIDs will save your bacon as a substitute primary key either.  There
could be duplicate OIDs in a table if rows were inserted exactly 4
billion OID-creations apart.  The system tables that have OIDs all
defend against this scenario by defining unique indexes on OID; but
if you had that much foresight for a user table you'd likely have
created your own primary key anyway.

In short, there's very little reason except backwards-compatibility
why WITHOUT OIDS isn't the default.  It probably will become the
default in a few releases...

            regards, tom lane

PS: if you ever are up against the need to uniquely identify a specific
row in a table with no primary key, the CTID column is the thing to use.
CTID is not a substitute primary key either because it changes on UPDATE
... but it's just the thing to finger a specific row for fixing.

tool for comparing databases (?)

From
Michael Adler
Date:
Does there exist a method or tool to compare two PostgreSQL databases?

Say you have an application based on PostgreSQL and it is deployed to
dozens or hundreds of disparate systems. Different versions (v1.2, v1.3)
of the application require somewhat different database schemas (e.g. v1.3
has an extra column on one table). Over a long period of time and many
upgrades, it would be worthwhile to validate that your database fits the
expected schema. How could one acheive this?

This seems like a useful general purpose tool, so perhaps this question
has already been addressed.

If one could "pg_dump" the system catalogs, they would have database
schema snapshots. Next, they would need a tool to compare two snapshots
(in whole or in part) and determine the relationship (A matches B exactly,
A contains all of B plus + some extra X, or A contains all of B except for
missing  Y).

Comments? Suggestions? Interest?

Mike Adler