Thread: Solving OID wrap-around in 7.4 DB?

On behalf of a previous employer who, due to major downsizing, is left
without an answer to this:

What's the best way to treat impending OID wrap-around in a 7.4.8
database? This DB has been online for about 18 months, and is expected
to hit the dreaded wrap-around in about a month. At an application
level, there's some small chance that OID wrap will cause failures:
there's code that writes rows, and (for tables without primary keys)
relies on DBD::Pg's pg_oid_status method to identify the last INSERT.
(The vast majority of the code does not rely on this, but there's a tiny
fraction that does, and rewriting the code to remove this is not an
option due to the aforementioned downsizing.)

My immediate take on this is that the only sure way to avoid the problem
would be to dump and reload the database. Due to the size of the
database, this is likely to take most of a day. Any other options?

jeff@endpoint.com (Jeff Boes) writes:
> On behalf of a previous employer who, due to major downsizing, is left
> without an answer to this:
>
> What's the best way to treat impending OID wrap-around in a 7.4.8
> database? This DB has been online for about 18 months, and is expected
> to hit the dreaded wrap-around in about a month. At an application
> level, there's some small chance that OID wrap will cause failures:
> there's code that writes rows, and (for tables without primary keys)
> relies on DBD::Pg's pg_oid_status method to identify the last INSERT.
> (The vast majority of the code does not rely on this, but there's a tiny
> fraction that does, and rewriting the code to remove this is not an
> option due to the aforementioned downsizing.)
>
> My immediate take on this is that the only sure way to avoid the
> problem would be to dump and reload the database. Due to the size of
> the database, this is likely to take most of a day. Any other
> options?

Another method would be to use replication to load the data into a
fresh DB instance.

You could, in principle, use Slony-I to do this; that is an intended
sort of "use case."
--
output = ("cbbrowne" "@" "acm.org")
http://www3.sympatico.ca/cbbrowne/slony.html
"What if you slept?  And what if, in your sleep, you dreamed?
 And what if, in your dream, you went to heaven and there
 plucked a strange and beautiful flower?  And what if, when
 you awoke, you had the flower in your hand?  Ah, what then?"
    --Coleridge

On Mon, 2005-10-17 at 12:04, Chris Browne wrote:
> jeff@endpoint.com (Jeff Boes) writes:
> > On behalf of a previous employer who, due to major downsizing, is left
> > without an answer to this:
> >
> > What's the best way to treat impending OID wrap-around in a 7.4.8
> > database? This DB has been online for about 18 months, and is expected
> > to hit the dreaded wrap-around in about a month. At an application
> > level, there's some small chance that OID wrap will cause failures:
> > there's code that writes rows, and (for tables without primary keys)
> > relies on DBD::Pg's pg_oid_status method to identify the last INSERT.
> > (The vast majority of the code does not rely on this, but there's a tiny
> > fraction that does, and rewriting the code to remove this is not an
> > option due to the aforementioned downsizing.)
> >
> > My immediate take on this is that the only sure way to avoid the
> > problem would be to dump and reload the database. Due to the size of
> > the database, this is likely to take most of a day. Any other
> > options?
>
> Another method would be to use replication to load the data into a
> fresh DB instance.
>
> You could, in principle, use Slony-I to do this; that is an intended
> sort of "use case."

I thought that with 7.4 all you needed was to vacuum all databases (full
maybe???) to prevent oid wraparound.

On Mon, Oct 17, 2005 at 02:50:01PM -0500, Scott Marlowe wrote:
> I thought that with 7.4 all you needed was to vacuum all databases (full
> maybe???) to prevent oid wraparound.

Sure you're not confusing oid wraparound with transaction ID wraparound?

--
Michael Fuhr

On Sun, Oct 16, 2005 at 07:28:00AM -0400, Jeff Boes wrote:
> On behalf of a previous employer who, due to major downsizing, is left
> without an answer to this:
>
> What's the best way to treat impending OID wrap-around in a 7.4.8
> database? This DB has been online for about 18 months, and is expected
> to hit the dreaded wrap-around in about a month. At an application

How many OIDs have been recovered?  If the answer is "none" (i.e. the
database doesn't have many deletes), then there isn't likely much you
can do about it -- reloading the database is going to cause the same
problem.

As Chris says in another message, you can use Slony to replicate the
database, in order not to have a long outage.  Note, too, that since
you say most tables don't actually use this pg_oid_status method, you
can get away with creating most of the tables without OIDs, which
will stave off your problem for a while.  That'd probably even help
if the database mostly just grows, depending (of course) on where the
growth is.

A

--
Andrew Sullivan  | ajs@crankycanuck.ca
Information security isn't a technological problem.  It's an economics
problem.
        --Bruce Schneier