Re: Use of OIDS as primary keys

Bill Moran <wmoran(at)potentialtech(dot)com> writes:
> So the upshot is that using WITHOUT OIDS on tables with primary keys
> doesn't hurt a thing. But it's a good idea to keep OIDS on tables
> without primary keys, since it can improve the operation of certain
> internal actions. There are no space consideration because the OID
> is part of a (currently) fixed data structure.

There aren't any "internal actions" that care about OIDs, except for
OIDs in the system tables. The recommendation to keep OIDs in user
tables without primary keys comes from the notion that you might use
the OID as a substitute primary key --- if you have no primary key
at all, then you're going to find yourself in trouble as soon as you
need to identify a specific row (eg, to correct a mistake).

However, because of the wraparound issue you can't really assume that
OIDs will save your bacon as a substitute primary key either. There
could be duplicate OIDs in a table if rows were inserted exactly 4
billion OID-creations apart. The system tables that have OIDs all
defend against this scenario by defining unique indexes on OID; but
if you had that much foresight for a user table you'd likely have
created your own primary key anyway.

In short, there's very little reason except backwards-compatibility
why WITHOUT OIDS isn't the default. It probably will become the
default in a few releases...

regards, tom lane

PS: if you ever are up against the need to uniquely identify a specific
row in a table with no primary key, the CTID column is the thing to use.
CTID is not a substitute primary key either because it changes on UPDATE
... but it's just the thing to finger a specific row for fixing.