Re: OID Question

#1

Terry Lee Tucker wrote:

Quote:

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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

#2

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Thursday 11 November 2004 07:04 am, Terry Lee Tucker wrote:

Quote:

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+UyrgD0 RftGBpbwCgrHPi
r8mynfAyne7lRETGLIMCz5E=
=Dl/9
-----END PGP SIGNATURE-----

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

#3

Many thanks to all who have responded. I hope to be as helpful to the list
someday as each of you have been. Thanks...

On Thursday 11 November 2004 10:04 am, Terry Lee Tucker saith:

Quote:

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 (AT) esc1 (DOT) com

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

--
Quote: 80
"Let us pray for the salvation of all of those who live in their
totalitarian darkness -- pray that they will discover the joy of
knowing God. But until they do, let us be aware that while they preach
the supremacy of the state, declare its omnipotence over individual
man, and predict its eventual domination of all peoples on the earth,
they are the locus of evil in the modern world."

--Ronald Reagan

Work: 1-336-372-6812
Cell: 1-336-363-4719
email: terry (AT) esc1 (DOT) com

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: OID Question

comp.databases.postgresql.general comp.databases.postgresql.general

Re: OID Question - 11-11-2004 , 11:50 AM

Re: OID Question - 11-11-2004 , 11:58 AM

Re: OID Question - 11-11-2004 , 05:24 PM