Re: OID wraparound: summary and proposal

Hannu Krosing wrote:
> 
> mlw wrote:
> >
> > I posted this question earlier, but it looks like it never made it on.
> >
> > If you can control the OIDs on a per table basis, and some tables need not even
> > have any, why not let each table have its own OID range? Essentially, each
> > record will be numbered relative to 0 on its table?
> >
> > That would really cut down the OID wrap around problem, and allow records to
> > have a notion of serialization.
> 
> What would the meaning of such an "OID" be ?
> 
> Apart from duplicating the primary key that is ?

Some other databases have the notion of a ROWID which uniquely identifies a row
within a table. OID can be used for that, but it means if you use it, you must
limit the size of your whole database system. The other alternative is to make
a column called "rowid" and a sequence for it and a default of
nextval('table_rowid'). That means more work for those porting. 

My thinking was that if the "OID" issue was being addressed, maybe it could be
changed quite a bit. The problem with the current OID is that it severely
limits the capacity of the database AND does not carry with it enough
information.

For instance, as far as I can see, one can not take an OID and make any sort of
determination about what it is. One also needs to know the table and the
database from which it was retrieved. So an OID is meaningless without the
contextual information. Why should it be a system wide limitation when it needs
to be used in the context of a specific table?

That way PostgreSQL has a knowable 4B (or 2B signed)  record limit per table,
not per system. One could create a new virtual OID like thing, called SYSOID,
or something, which is a 64 bit value, the upper 4 bytes being the OID of the
table from the catalog, and the lower 4 bytes being the OID of the record.

The SYSOID would really tell you something! Given a SYSOID you could find the
database, the table, and the record.

-- 
5-4-3-2-1 Thunderbirds are GO!
------------------------
http://www.mohawksoft.com