Thread: Re: OID Perfomance - Object-Relational databases

Hi everyone,

I just came across a thread in the pgsql archives from October 2000, and found this post particularly interesting:

http://archives.postgresql.org/pgsql-sql/2000-10/msg00044.php

...because I had already been designing something similar but on a larger scale, for an integrated information system
withscheduling, accounting, internal messaging, inventory, safety management, and slew of other things.  Initially when
Iread through the pgsql docs, I decided that I didn't ever want to deal with wraparound, and so I wouldn't use OIDs.
Butas I've been designing and implementing prototypes of this system, I've found it less than elegant to have universal
references,because I always need to have a table name involved.  Recently I clued in that OIDs could solve this, so I
wentlooking through the archives and found that thread.  I'm just wondering a few things. 

First, how would Michael/Tom's suggestion (to have a single sequence used by multiple tables) work exactly, i.e., if I
hada number from that sequence, how would I know which table it belonged to without checking *all* of the tables for a
rowwith that ID number?  Josh said he would try the idea and report back on performance...how did this all turn out? 

If there's an answer to that question, then is it conceivable to use an int8 sequence across, say, 100 tables (which
theremay well be in the pool of things I would want to be able to arbitrarily reference by the time I'm done this
project)as a substitute for OIDs until there are 64-bit OIDs? 

Either way, are 64-bit OIDs planned within the next couple years?  I could only find discussion on this from 2000, and
theannouncement that OIDs were optional after a certain version. 

Thanks,
Kev

"Kevin Field" <kev@brantaero.com> writes:
> Either way, are 64-bit OIDs planned within the next couple years?

No, they're not planned at all.  That line of thought has pretty much
died off, to the point where OIDs in user tables are not just deprecated
but not there at all by default.

There are basically two ways to attack the problem of a database-wide
unique ID:

* Use a single int8 sequence for the whole database;

* Use the combination of table OID and row OID (or, perhaps, an int8
sequence for the row identifier, if you need more than a billion or
so rows in the table).

The good thing about a two-part unique ID is that you can tell by
inspection which table the object is in, which is pretty handy.
But of course it's a bit ugly notationally, since you have to deal with
two fields not one.

The problem of dumping and restoring raw OIDs is still as bad as it was
in 2000.  However there's now the "regclass" datatype that can provide
a symbolic display of table OID.  If I were doing this today I'd use a
regclass column for the table part of a unique ID, and per-table serial
or bigserial counters for the row part.

            regards, tom lane

> There are basically two ways to attack the problem of a database-wide
> unique ID:
>
> * Use a single int8 sequence for the whole database;
>
> * Use the combination of table OID and row OID (or, perhaps, an int8
> sequence for the row identifier, if you need more than a billion or
> so rows in the table).

There's a third way: int8 serial, lower 48 bits for row id, upper 16 bits
appended with your own table id.

--
Scott Ribe
scott_ribe@killerbytes.com
http://www.killerbytes.com/
(303) 722-0567 voice