Thread: OID Question
Greetings, Here is a simple question: Is it ok to put a unique index on the oid for my tables? We are in the process of moving from Progress Software to PostgreSQL. In the Progress world, you can always uniquely, and quickly find a record by using their version of oid, which is recid. I remember reading somewhere that the oid could be duplicated across the cluster, but would not be duplicated in a single table. Maybe I dreamed it. What is the recommendation regarding this and why? Version: PostgreSQL 7.4.6 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 3.2.3 20030502 (Red Hat Linux 3.2.3-42) Thanks for your input... -- Work: 1-336-372-6812 Cell: 1-336-363-4719 email: terry@esc1.com
On Thu, 11 Nov 2004, Terry Lee Tucker wrote: > Is it ok to put a unique index on the oid for my tables? We are in the process > of moving from Progress Software to PostgreSQL. In the Progress world, you > can always uniquely, and quickly find a record by using their version of oid, > which is recid. I remember reading somewhere that the oid could be > duplicated across the cluster, but would not be duplicated in a single table. > Maybe I dreamed it. What is the recommendation regarding this and why? Well, some system tables that want unique oids already do this. One issue with making a unique index on oid is that after oid counter rollover you'll potentially get intermittent constraint violations due to duplicate oids that you need to be able to handle.
On Thu, Nov 11, 2004 at 10:04:30 -0500, Terry Lee Tucker <terry@esc1.com> wrote: > Greetings, > > Here is a simple question: > > Is it ok to put a unique index on the oid for my tables? We are in the process Yes, but you may occasionally have insert failures if the oid wraps around and you try to reuse one on an insert. > of moving from Progress Software to PostgreSQL. In the Progress world, you > can always uniquely, and quickly find a record by using their version of oid, > which is recid. I remember reading somewhere that the oid could be You should probably just use a normal column named recid and not try to use the special oid column to do this. > duplicated across the cluster, but would not be duplicated in a single table. > Maybe I dreamed it. What is the recommendation regarding this and why?
Bruno Wolff III wrote: > On Thu, Nov 11, 2004 at 10:04:30 -0500, Terry Lee Tucker > <terry@esc1.com> wrote: > >> Greetings, >> >> Here is a simple question: >> >> Is it ok to put a unique index on the oid for my tables? We are in >> the process > > > Yes, but you may occasionally have insert failures if the oid wraps > around and you try to reuse one on an insert. So this prompts a question regarding the documentation. I'm assuming that I can address the wrap-around issue based on the following found under "Notes" section of the "Create Table" document: "Whenever an application makes use of OIDs to identify specific rows of a table, it is recommended to create a unique constraint on the oid column of that table, to ensure that OIDs in the table will indeed uniquely identify rows even after counter wraparound." Am I reading this correctly? If I place a unique constraint on the oid column, I will not have to worry about oid wrap around? -- Until later, Geoffrey
On Thu, Nov 11, 2004 at 15:34:14 -0500, Geoffrey <esoteric@3times25.net> wrote: > > "Whenever an application makes use of OIDs to identify specific rows of > a table, it is recommended to create a unique constraint on the oid > column of that table, to ensure that OIDs in the table will indeed > uniquely identify rows even after counter wraparound." > > Am I reading this correctly? If I place a unique constraint on the oid > column, I will not have to worry about oid wrap around? No. The unique constraint may result in a transaction failing that wouldn't otherwise fail and your application needs to be able to handle this case.
Terry Lee Tucker wrote: > Greetings, > > Here is a simple question: > > Is it ok to put a unique index on the oid for my tables? We are in the process > of moving from Progress Software to PostgreSQL. In the Progress world, you > can always uniquely, and quickly find a record by using their version of oid, > which is recid. I remember reading somewhere that the oid could be > duplicated across the cluster, but would not be duplicated in a single table. > Maybe I dreamed it. What is the recommendation regarding this and why? Just create a "recid" field of type SERIAL and add a unique constraint. You can create tables WITHOUT OIDS if you want to reclaim some space in the process. OIDs will wrap around and are only guaranteed to be unique in system-tables IIRC. -- Richard Huxton Archonet Ltd
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On Thursday 11 November 2004 07:04 am, Terry Lee Tucker wrote:
> Greetings,
>
> Here is a simple question:
>
> Is it ok to put a unique index on the oid for my tables? We are in the
> process of moving from Progress Software to PostgreSQL. In the Progress
> world, you can always uniquely, and quickly find a record by using their
> version of oid, which is recid. I remember reading somewhere that the oid
> could be duplicated across the cluster, but would not be duplicated in a
> single table. Maybe I dreamed it. What is the recommendation regarding this
> and why?
OID's are unique per database as long as they don't turn over - meaning oid's
have a limited range - AFAIK 32 bit integer, so yes, inside a table you could
use the oid (assuming you created the table WITH OIDS) to identify a record.
HOWEVER: bad design. oid's are likely to go away at some point down the road
(maybe in postgresql 12.0 or so :-)) ). Therefor, and since you're migrating
anyways, it would be better to add a primary key column to each table. Unless
I have a real need for a primary key, I usually just add a "id" column (as PK
with default from a sequence) to my tables. Therefor I can always use the id
column to identify records. This is portable and easy to migrate if you need
to upgrade to a newer version of postgresql - or if you strike it rich and
have to go to oracle or db2 it's still portable.
Also: having a real column with a key if more failsafe. I once (long long ago)
used oids as foreign keys. I remember back then dumping and restoring the db
was a pain. Now you could use the "dump oids" option, but if you forget that
and you restore the DB your relationship model will be a big pile of trash
because the oid's change on restore. I'd recommend to go the extra mile and
add at least a unique key column to your tables.
Hope that helps
UC
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)
iD8DBQFBk6gwjqGXBvRToM4RAu5kAKCbMcYtk7qs3xv+UyrgD0RftGBpbwCgrHPi
r8mynfAyne7lRETGLIMCz5E=
=Dl/9
-----END PGP SIGNATURE-----
On Thu, Nov 11, 2004 at 03:34:14PM -0500, Geoffrey wrote: > So this prompts a question regarding the documentation. I'm assuming > that I can address the wrap-around issue based on the following found > under "Notes" section of the "Create Table" document: > > "Whenever an application makes use of OIDs to identify specific rows of > a table, it is recommended to create a unique constraint on the oid > column of that table, to ensure that OIDs in the table will indeed > uniquely identify rows even after counter wraparound." > > Am I reading this correctly? If I place a unique constraint on the oid > column, I will not have to worry about oid wrap around? Nope, it means that OIDs are not inherintly unique and you need to make an index to force them to be unique. The OID counter will still wraparound but now if the OID is reused your INSERT will just fail instead of inserting a duplicate. Seriously, use a SERIAL, OIDs are nowhere near as interesting as people keep suggesting... -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.