Thread: Use of OIDS as primary keys
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
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
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
>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
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
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
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
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
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.
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