Re: identity columns ?

"Joshua b. Jore" <josh@greentechnologist.org> writes:
> So this raises a question I was wondering, what use *is* the oid column in
> non-system tables? The only thing I could think of is for last-resort row
> uniqueness if the application somehow borked up. Are there any other
> reasons to even have this column in a user table?

OID is not an unreasonable thing to use for relatively short-term row
identity.  Some time ago I had a moderately complex C++-and-Tcl
application that had objects in memory representing rows fetched from
various tables; it used OIDs as a general-purpose way of remembering
which object was associated with which row.  Without that I'd have had
to devise some way of dealing with table-specific primary keys.

What you don't really want to do is use OIDs for cross-references in the
database data itself, because that creates headaches when you want to
dump and reload the database.

There has been some talk of replacing the global OID counter with
per-table OID sequence generators --- if that happened, the problems
with dumping and reloading OIDs would largely go away, since it'd be
relatively painless for the system to automatically advance any table's
OID counter past all the OID values loaded into that table.

What you should definitely NOT do is imagine that OID is a globally
unique identifier.  At best it is a unique identifier of a row within
its table, and even that is only reliable if you put a unique index on
OID onto that table.  (The combination of table OID and row OID can
serve as a global identifier if you need one; but remember that you will
not be able to count on table OID remaining the same over dump/reload.)

            regards, tom lane