Re: 4 billion + oids

> -----Original Message-----
> From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
> Sent: Monday, March 24, 2003 11:17 AM
> To: Dann Corbit
> Cc: pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] 4 billion + oids
>
>
> "Dann Corbit" <DCorbit(at)connx(dot)com> writes:
> > 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.
>
> Why in the world aren't you using a serial int8 column, instead?

I am using Postgresql 7.1.3 (our own native port) and I was not aware
that feature was available.
If I can create tables without OID's and with 8 byte serial columns I
will change to that.

The documentation I read said that serial values were 4 bytes long. How
do I declare and 8 byte serial column?

"Dann Corbit" <DCorbit(at)connx(dot)com> writes:
>> Why in the world aren't you using a serial int8 column, instead?

> I am using Postgresql 7.1.3 (our own native port) and I was not aware
> that feature was available.

It may not be, in 7.1 --- I forget when we widened sequences to int8.

However, 64-bit OIDs are certainly never going to appear in 7.1 either.

regards, tom lane

On Mon, Mar 24, 2003 at 02:29:34PM -0500, Tom Lane wrote:
> It may not be, in 7.1 --- I forget when we widened sequences to int8.

7.2. The SERIAL fields are int4 still, though.

A
--
----
Andrew Sullivan 204-4141 Yonge Street
Liberty RMS Toronto, Ontario Canada
<andrew(at)libertyrms(dot)info> M2P 2A8
+1 416 646 3304 x110

--On Monday, March 24, 2003 16:28:01 -0500 Andrew Sullivan
<andrew(at)libertyrms(dot)info> wrote:

> On Mon, Mar 24, 2003 at 02:29:34PM -0500, Tom Lane wrote:
>> It may not be, in 7.1 --- I forget when we widened sequences to int8.
>
> 7.2. The SERIAL fields are int4 still, though.
but there is a BIGSERIAL or SERIAL8 IIRC.

>
> A
> --
> ----
> Andrew Sullivan 204-4141 Yonge Street
> Liberty RMS Toronto, Ontario Canada
> <andrew(at)libertyrms(dot)info> M2P 2A8
> +1 416 646 3304 x110
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 E-Mail: ler(at)lerctr(dot)org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

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(at)libertyrms(dot)info> M2P 2A8
+1 416 646 3304 x110

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(at)libertyrms(dot)info>
To: <pgsql-general(at)postgresql(dot)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(at)libertyrms(dot)info> M2P 2A8
> +1 416 646 3304 x110
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
>
>

On Tue, Mar 25, 2003 at 11:43:57AM +1100, Andrew Bartley wrote:

> 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.

Note that you probably need to REINDEX the table pg_class if you use too
much temp tables.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"La gente vulgar solo piensa en pasar el tiempo;
el que tiene talento, en aprovecharlo"

Hi, group,
How do I know whether my postgresql database is using Turple level or object level OIDs?
postgresql 7.2.3 on Redhat 8.0
Thank you
Andrew Bartley <abartley(at)evolvosystems(dot)com> wrote: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"
To:

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
> M2P 2A8
> +1 416 646 3304 x110
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
>
>

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org

---------------------------------
Do you Yahoo!?
Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop!