Re: [GENERAL] using ID as a key

Marc Tardif wrote:
>
> I've been using OID's for a while now, and I haven't had any trouble at
> all so far. Because this is an active database, it has been important to
> keep the current data intact. Nevertheless, I have managed to keep
> everything in order even to the last OID. To dump with oid's, simply add
> the -o flag to pg_dump (as explained in the pg_dump manpage).
>
> In my situation, I particularly appreciate OID's because they have little
> overhead and they simply do the job. More specifically, they come in handy
> for creating a fulltextindex, which I recommend you take a look at in the
> contributions.
>
> Although I don't completely agree with Ed Loehr, he does have a point
> though. Keeping track of OID's can be more complicated than using an id
> field in a table. The major problem being that you can't change an OID and
> you can't predict OID's either. As Bruce was telling me, OID's are
> gathered in batches of 100 by each backend, so you never really know what
> you'll end up with. Regardless of these obstacles, it is not wrong to use
> OID's and you should be open to this option.

Now *that* is a helpful answer to my original question.  Seems OIDs
are viable primary keys, provided you don't get bit by failing to add
the -o flag to your pg_dumps and don't mind the unpredictability.  I
appreciate the info.

As for the tradeoffs you mention, I have found it pretty convenient
during development to be able to glance at data and tell the order of
record creation from the serial id, but I could live without that (I'm
also time-stamping record creations).  I've never needed to change a
serial/sequence-generated *primary* key, so I doubt that read-only
constraint would bite you with OIDs.  And I can see that saving 4
bytes per record could be pretty significant for some large databases
or systems with scarce resources.

Cheers,
Ed Loehr