On May 2, 2007, at 3:58 PM, Brent Wood wrote:
Richard Huxton wrote:
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.
An OID is not guaranteed to be unique. They're only 32 bits wide.
(And if you do wrap them around the failure modes could be far worse
than non-uniqueness in a user table.)
What you want instead is a single sequence that is used to generate
the id field in each table. That guarantees uniqueness, unless you
manage to wrap the sequence around. With a 64 bit id, that's unlikely
to happen.
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,
Steve
