Re: 4 billion + oids

> -----Original Message-----
> From: Amin Abdulghani [mailto:amin(at)quantiva(dot)com]
> Sent: Monday, March 24, 2003 8:43 AM
> To: pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] 4 billion + oids
>
> My guess is most of the applications on postgress wouldn't
> totally rely on oids, though they may implicitly use them
> if they use standard sql create table statements. My
> concern is that during the wrap arounds this could create
> unintended problems in table, index creations or
> elsewhere. Probably its worthwhile to enumerate the list
> of potential problems (eg what we now know table creation,
> index creation), their error messages (so applicatons can
> handle them cleanly) and possibly their workarounds. This
> list could then be very useful as part of the discussion
> on oids in the documentation.

I have definitely seen serious problems relating to OID wrap.
We have an application that uses the OID's to create joins.
We scan tables from a list of source tables from some external (usually
non-postgresql) database.
For each of these tables we create a 64 bit checksum for each record and
store it in a table, along with an OID.
Then, after the passage of time, we rename the current checksum table
and repeat the process.
Then, we can do joins between the two checksum tables and see which
records are updated, added or deleted.
Often, we deal with systems that have tens or even hundreds of millions
of records, and the tasks run on a nightly basis.
Hence, we eventually run out of new OIDs.

Our current solution is to drop the database and recreate it. It's an
awful solution to the problem but currently there is nothing better. It
requires us to run a full synchronization instead of an incremental,
which is orders of magnitude slower.

It sure would be nice to have 64 bit OID values.