Re: Utility of OIDs in postgres

From: Brent Wood <b(dot)wood(at)niwa(dot)co(dot)nz>
To: Richard Huxton <dev(at)archonet(dot)com>
Cc: Jasbinder Singh Bali <jsbali(at)gmail(dot)com>, pgsql-general(at)postgresql(dot)org
Subject: Re: Utility of OIDs in postgres
Date: 2007-05-02 22:58:38
Message-ID: 4639179E.5030601@niwa.co.nz
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Richard Huxton wrote:
> Jasbinder Singh Bali wrote:
>> Hi,
>>
>> What would be the benefit of creating tables with OIDs as against
>> one's not
>> with OIDs
>> Giving a unique identifier to each row inserted has some extra
>> efficiency
>> factor involved or what.
>
> OIDs are used by the various system tables.
> Historically, all user tables had them too.
> There's no reason to use them in a new system - they offer no
> advantages over an ordinary integer primary-key.
>
Generally this is correct. However I can show one case where they are
very useful:

Table 1: oid, id, category, name, desc, f1, f2, f3, f4
Table 2: oid, id, category, name, desc, f1, f2
Table 3: oid, id, category, name, desc, f1, f2, f3, f4, f5, f6, f7, ...

ID is a serial int as primary key.

create view v_demo as
select oid, name, category, desc from table1
union
select oid, name, category, desc from table2
union
select oid, name, category, desc from table3;

As oid is unique across all tables (in fact all database objects), but
serial is unique within a table, there are odd cases like this where
using an oid in each table ensures an automatic unique key in the view.
So oids can be useful.

This is a real case, for listing objects identified in seabed photos,
table1 is substrates, table2 is scampi burrow types, table 3 is taxa.
The user is presented with a pick list on the view, & the oid is used to
specify what has been identified. The underlying tables contain detail
about each category of object. We could do something much more
complicated in an application, but with oids there is no need.

A similar example could be power stations, all have name, output,
contacts, etc, but depending on whether it is coal/gas/hydro/geothermal,
etc, the attributes will vary. So a table for each type, with a view
enabling a common access of the common fields.

Cheers,

Brent Wood

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Steve Atkins 2007-05-02 23:16:54 Re: Utility of OIDs in postgres
Previous Message William Garrison 2007-05-02 22:57:12 Help tracking down error in postgres log