Re: oid not "UNIQUE" for use as FOREIGN KEY?

On Tue, 23 Oct 2001, Ernesto Baschny wrote:

> On 23 Oct 2001 at 9:18, Stephan Szabo wrote:
>
> > > (...)
> > > As of yesterday, I think this is a bug, because you can
> now create a
> > > unique constraint on the OID column ... but I see it still
> doesn't work:
>
> > The yes was to it being a bug. I've always treated it as
> such since you
> > could make the "constraint" by making the index manually,
> just a low
> > priority one since such uses of oid should be discouraged
> anyway. :)
>
> Thats interesting, as I thought that would be an "elegant"
> way of doing that. I've got the idea from Bruce Momjian's
> book, here:

Well, the smiley was meant to be indicative of the fact that it's
just my opinion on the whole matter (I'll give some more detail below)
In general, oids aren't quite as unique as the book makes them out
to be since they are only effectively an int4 AFAIK.

> http://www.ca.postgresql.org/docs/aw_pgsql_book/node71.html
>
> Should I instead stick to separate INTEGER PRIMARY KEY _id
> fields instead of using the OID's for it? What use would one
> want to make of an OID then?

Well, it's going to work soon since Tom's on it, but in general,
I think assuming an oid is actually unique is dangerous since
in large systems it may wrap (although this will be less bad
in 7.2) and at that point you either have to deal with:
1) You've made a unique index on oid to make it really unique.
Now some inserts fail due to the unique constraint when you
run into an oid that already exists in the table. You have
to realize that this is a transitory problem and that if you
try again enough times it'll work. Heaven help you if you
are doing large insert ... select queries.
2) You don't make a unique index. Now you have two rows with the
same oid value. The foreign key stuff won't like that, nor
will subqueries that expect a single row, etc...

With your own key, while it may still wrap around, it's much
more within your control. This is of course just an opinion and
I'm sure someone will jump in with a pro oid use one too. :)
Of course, most of this is academic for most people who aren't
likely to run into oid conflicts of this sort, but...