Re: OID Question

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