Thread: OID question

Hello all,

We have a live server running on PG 8.0.3 which we backup on a regular bases
by doing a pg_dumpall --oids.  We have a test server currently running 8.1.1
where we reload that dumpall using psql.

Now we have a history tracking system in the db which via triggers and rules
keeps track of changes to certain tables via OID and logs them in history
tables.  Now our problem is that when we load the db from 8.0.3 into the
8.1.1 db, the current data with all the oids is loaded correctly.  But, as
soon as we try to add rows (new data created during testing), we run into a
problem where the OID on the testing machine has collisions with data that
was loaded from the live server.

I've been searching for awhile on further information about OID and haven't
been real successfull.   Any pointers would be helpfull...

1.  Is OID stored per cluster, or is it per database?  I assume it's per
cluster since databases appear to have an OID.

2. Is there any way to get the test machine to have it's OID reset to the
current live server OID after a reload so we don't get OID collisions?

We aren't sure at this point if the problem we are having has to do with the
fact that we drop and reload the test db quite often and so we are running
into OID wraparound, or if it has to do with the fact that we recently went
to 8.1 on this test machine and OID is now handled differently on reload.

Anyt thoughts or suggestions?

-Chris

Chris Kratz <chris.kratz@vistashare.com> writes:
> We aren't sure at this point if the problem we are having has to do with the
> fact that we drop and reload the test db quite often and so we are running
> into OID wraparound, or if it has to do with the fact that we recently went
> to 8.1 on this test machine and OID is now handled differently on reload.

We did change the handling of OIDs in 8.1, specifically this patch:
http://archives.postgresql.org/pgsql-committers/2005-08/msg00109.php
which ensued from this discussion:
http://archives.postgresql.org/pgsql-hackers/2005-08/msg00074.php

Pre-8.1 versions contained a kluge that made COPY FROM ... WITH OIDS
increase the OID counter to be at least the maximum of the OIDs loaded
into the table.  The lack of this hack is what's making the collision
problem manifest almost immediately for you.  However, you would have
had a problem eventually anyway, because the OID counter is only 32 bits
and will eventually wrap around.

So the first thing to ask yourself is whether depending on OID
uniqueness is a sane design decision at all.  IMHO the answer is
no unless (1) the tables you need OIDs for will never exceed 2^32
rows, or even reach any significant fraction of that, and (2) you
aren't assuming global uniqueness of OIDs but only per-table uniqueness.
(NOTE: you can create a database-wide unique identifier by combining
tableoid and row OID, if necessary.)

Assuming you meet those sanity checks, the way to do this in 8.1 is
to create a unique index on OID for each table you need unique OIDs
in.  This not only is needed anyway to enforce the design assumption,
but the presence of such an index triggers the code we added to 8.1
to select an unused OID.
http://developer.postgresql.org/cvsweb.cgi/pgsql/doc/src/sgml/ddl.sgml.diff?r1=1.42&r2=1.43

            regards, tom lane

Thank you Tom for your concise response.  Your explanation is very helpfull.
We were aware of the wraparound issues, but this has worked so well for us
that we haven't explored changing it yet.  Having a unique identifier per row
is most usefull in our situation and we didn't think we were anywhere near
wraparound on our live machine, hence we've let it ride.

It sounds like we will need to start thinking about migrating to the
tableoid+rowoid at some point.

Thanks again,

-Chris

On Wednesday 01 February 2006 09:53 am, you wrote:
> Chris Kratz <chris.kratz@vistashare.com> writes:
> > We aren't sure at this point if the problem we are having has to do with
> > the fact that we drop and reload the test db quite often and so we are
> > running into OID wraparound, or if it has to do with the fact that we
> > recently went to 8.1 on this test machine and OID is now handled
> > differently on reload.
>
> We did change the handling of OIDs in 8.1, specifically this patch:
> http://archives.postgresql.org/pgsql-committers/2005-08/msg00109.php
> which ensued from this discussion:
> http://archives.postgresql.org/pgsql-hackers/2005-08/msg00074.php
>
> Pre-8.1 versions contained a kluge that made COPY FROM ... WITH OIDS
> increase the OID counter to be at least the maximum of the OIDs loaded
> into the table.  The lack of this hack is what's making the collision
> problem manifest almost immediately for you.  However, you would have
> had a problem eventually anyway, because the OID counter is only 32 bits
> and will eventually wrap around.
>
> So the first thing to ask yourself is whether depending on OID
> uniqueness is a sane design decision at all.  IMHO the answer is
> no unless (1) the tables you need OIDs for will never exceed 2^32
> rows, or even reach any significant fraction of that, and (2) you
> aren't assuming global uniqueness of OIDs but only per-table uniqueness.
> (NOTE: you can create a database-wide unique identifier by combining
> tableoid and row OID, if necessary.)
>
> Assuming you meet those sanity checks, the way to do this in 8.1 is
> to create a unique index on OID for each table you need unique OIDs
> in.  This not only is needed anyway to enforce the design assumption,
> but the presence of such an index triggers the code we added to 8.1
> to select an unused OID.
> http://developer.postgresql.org/cvsweb.cgi/pgsql/doc/src/sgml/ddl.sgml.diff
>?r1=1.42&r2=1.43
>
>             regards, tom lane