Re: 4 billion + oids

I hope this helps.

PostgreSQL OID problem summary

The default object creation properties set OID's to "with" at tuple level.

OIDs start at 0 and reach a limit at 4,294,967,295.  New OID's are assigned
for each object created.

OIDs will reach the maximum limit, this will cause an "OID wrap".  An OID
wrap will cause a problem eventually.
Its a statistical probability that data loss will occur.

Suggested solution(s)

1.reduce oid consumption
 pros - less chance of oid wrap
 cons - wrap will happen (but may take many years)

2.drop and reload database
 pros - starts oid count from 0
 cons - downtime is significant (maybe many hours)

3.reduce number of tables,indexes etc.
 pros - less chance of oid clash
 cons - application may require high number of tables,indexes etc.

Re: our application problem, the cons for solutions 2/3 are significant so
as to prevent
them being used in our application.

Solution 1 is practical.  The database will be dropped and reloaded with a
modified schema that does
not use OID's at the tuple level.  No user tables at the tuple level will
use OID's in the new schema.

We believe that our OID consumption will decrease from 4,294,967,295 every
two weeks (approx.), to 4,294,967,295 every 190 years (approx.)

Thanks

Andrew Bartley

----- Original Message -----
From: "Andrew Sullivan" <andrew@libertyrms.info>
To: <pgsql-general@postgresql.org>
Sent: Tuesday, March 25, 2003 8:39 AM
Subject: Re: [GENERAL] 4 billion + oids


> On Mon, Mar 24, 2003 at 03:35:11PM -0600, Larry Rosenman wrote:
> > but there is a BIGSERIAL or SERIAL8 IIRC.
>
> Yes.  I just was trying to note that it's not like you automatically
> get the advantage of the larger sequences if you dump a 7.1.x
> database and load it into 7.2.  (Please redirect discussions about
> how hard/easy Postgres is to upgrade to some other list ;-)
>
> A
>
> --
> ----
> Andrew Sullivan                         204-4141 Yonge Street
> Liberty RMS                           Toronto, Ontario Canada
> <andrew@libertyrms.info>                              M2P 2A8
>                                          +1 416 646 3304 x110
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>