Re: OID Usage

On Fri, Jan 14, 2005 at 07:39:16PM +0100, Bo Lorentsen wrote:
>
> But, does this mean that the oid sollution I have decriped (and
> implimentet) have some unknown problems, or will oid's become obsolete
> in the near future ?

The PostgreSQL documentation discourages the use of OIDs for primary
keys. For example, the "Object Identifier Types" section in the
"Data Types" chapter says:

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.

The "System Columns" section of the "Data Definition" chapter says:

OIDs are 32-bit quantities and are assigned from a single
cluster-wide counter. In a large or long-lived database, it is
possible for the counter to wrap around. Hence, it is bad practice
to assume that OIDs are unique, unless you take steps to ensure that
they are unique.

The CREATE TABLE documentation in the Reference part says:

Once the counter wraps around, uniqueness of OIDs can no longer be
assumed, which considerably reduces their usefulness. Specifying
WITHOUT OIDS also reduces the space required to store the table on
disk by 4 bytes per row of the table, thereby improving performance.

The 8.0 Release Notes say the following under "Deprecated Features":

By default, tables in PostgreSQL 8.0 and earlier are created with
OIDs. In the next release, this will _not_ be the case: to create a
table that contains OIDs, the WITH OIDS clause must be specified or
the default_with_oids configuration parameter must be enabled.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/