Thread: oids as primary keys?
hi , someone has drawn my attention to the fact that oids may not be such a good idea to set as a primary key in a table. i have designed a relativley large database and defined oids as primary keys. i would like your opinions or recomendations on this. thanx.
Le Saturday 16 April 2005 00:42, Cima a écrit : > hi , > > someone has drawn my attention to the fact that oids may not be such a good > idea to set as a primary key in a table. i have designed a relativley large > database and defined oids as primary keys. i would like your opinions or > recomendations on this. It's surelly me, I reply to you directly instead to the list :( About this I find where I read the warning: http://www.postgresql.org/docs/8.0/interactive/datatype-oid.html where you can read: "The oid type is currently implemented as an unsigned four-byte integer. Therefore, it is not large enough to provide database-wide uniqueness in large databases, or even in large individual tables. So, using a user-created table's OID column as a primary key is discouraged. OIDs are best used only for references to system tables." Notice it is maybe not a problem for you currently.
Attachment
At 06:42 PM 4/15/05, Cima wrote:
>someone has drawn my attention to the fact that oids may not be such a good
>idea to set as a primary key in a table. i have designed a relativley large
>database and defined oids as primary keys. i would like your opinions or
>recomendations on this.
http://www.postgresql.org/docs/faqs.FAQ.html#4.12
On Fri, Apr 15, 2005 at 03:42:40PM -0700, Cima wrote: > > someone has drawn my attention to the fact that oids may not be such a good > idea to set as a primary key in a table. i have designed a relativley large > database and defined oids as primary keys. i would like your opinions or > recomendations on this. See "Object Identifier Types" in the "Data Types" chapter of the documentation: http://www.postgresql.org/docs/8.0/interactive/datatype-oid.html "The oid type is currently implemented as an unsigned four-byte integer. Therefore, it is not large enough to provide database-wide uniqueness in large databases, or even in large individual tables. So, using a user-created table's OID column as a primary key is discouraged. OIDs are best used only for references to system tables." See also "What is an OID? What is a TID?" in the FAQ: http://www.postgresql.org/docs/faqs.FAQ.html#4.12 "OIDs are autotomatically assigned unique 4-byte integers that are unique across the entire installation. However, they overflow at 4 billion, and then the OIDs start being duplicated." "To uniquely number columns in user tables, it is best to use SERIAL rather than OIDs because SERIAL sequences are unique only within a single table and are therefore less likely to overflow. SERIAL8 is available for storing eight-byte sequence values." -- Michael Fuhr http://www.fuhr.org/~mfuhr/
Hi, If you're creating a database, you probably have a data model about that 'thing' you're trying to craft your system upon. If this table in which you used the oids as PK correspond to any object of your model, you better use a unique attribute to identify that object, keeping the semantics and the spirit of the relational model. Just an idea ! Andres ... --- Cima <ruel.cima@facinf.uho.edu.cu> wrote: > hi , > > someone has drawn my attention to the fact that oids > may not be such a good > idea to set as a primary key in a table. i have > designed a relativley large > database and defined oids as primary keys. i would > like your opinions or > recomendations on this. > > > thanx. > > > ---------------------------(end of > broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to > majordomo@postgresql.org > __________________________________ Do you Yahoo!? Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/