4 billion + oids

Hi all,

We have a problem with our PostgreSQL application. We are trying to decide if this is a major problem with Postgres, or is due to our application design.

Our application generates thousands of temp tables and data is moved and sorted several times in each batched insertion of data.

We chew though OIDs very quickly. At our customers' site, the OID count is exceeding 4 billion and wrapping every two weeks (approx.).

We have found this to be a major problem. It seems once the OIDs wrap; we constantly get errors due to "Cannot insert a duplicate key into unique index pg_class_oid_index". There are about 3,000 entries in pg_class at this stage. As most of the tables are dynamically generated, a failure means lost data.

I am in the middle of designing an application change as a stop-gap fix:

1.. Change all creation of temp tables with "without oids", hoping to reduce the consumption of OIDS
2.. Check for the error string "Cannot insert a duplicate key into unique index pg_class_oid_index" in the batch shells for each function call and re-run if required.
3.. Remove all truncate statements from processing
4.. Trigger dump and restore of the database during nightly processing if the error occurs.

Can anyone comment if they know this is a fundamental limitation of PostgreSQL and if other databases have addressed this problem? Or alternatively, is it a problem with our application that must be corrected?

If anyone can instruct me on a better way to handle this, it would be appreciated.

We are running PostgreSQL 7.2.1 on Pentium 4 x86 type systems.

Thanks

Andrew Bartley

Evolvo Systems Pty Ltd.
Level 3, 351-353 Elizabeth Street
Melbourne, VIC, 3000
andrewb(at)evolvosystems(dot)com
tel. +613 9642 3200
fax. +613 9642 4108
mob. +613 0414 520 920

You need very much to read the manual and administrator's guide. I
/believe/ it is possible to recompile postgresql with support for more
oids. Remember to vacuum and analyze routinely. And when you recompile
upgrade to 7.3.2.
--
Daniel R. Anderson
Chief Lab Rat and Helper Monkey
Great Lakes Industries, Inc.
80 Pineview Ave.
Buffalo, NY 14218
(716) 691-5900 x218

"Never let your schooling interfere with your education"
-- Mark Twain

On Sun, 2003-03-23 at 21:42, Daniel R. Anderson wrote:
> You need very much to read the manual and administrator's guide. I
> /believe/ it is possible to recompile postgresql with support for more
> oids.

No, it's not.

> Remember to vacuum and analyze routinely.

AFAIK, neither of these would have any effect on OID usage.

Cheers,

Neil

On Mon, Mar 24, 2003 at 01:35:54PM +1100, Andrew Bartley wrote:

> We chew though OIDs very quickly. At our customers' site, the OID count
> is exceeding 4 billion and wrapping every two weeks (approx.).
>
> 1.. Change all creation of temp tables with "without oids", hoping to reduce the consumption of OIDS

This is your solution. Do oyu use OID anywhere within your application. If
not (you shouldn't be) create all table "without oids". If you stop using
OIDs so rapidly, your problems should go away.

> 2.. Check for the error string "Cannot insert a duplicate key into unique index pg_class_oid_index" in the batch shells for each function call and re-run if required.

> Can anyone comment if they know this is a fundamental limitation of
> PostgreSQL and if other databases have addressed this problem? Or
> alternatively, is it a problem with our application that must be
> corrected?

Postgresql uses OIDs for tables and triggers internally. If you don't need
OIDs within your data tables you should be able to reduce your OID usage
considerably.
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> IT is not something like pizza that you order in at one o'clock in
> the morning. - John Loebenstein, St George CIO

On Sun, 2003-03-23 at 21:35, Andrew Bartley wrote:
> 1. Change all creation of temp tables with "without oids", hoping
> to reduce the consumption of OIDS

That should be the first thing you should do (in 7.4, there will be an
ALTER TABLE command to disable OIDs). If you're not making use of OIDs
in your application, you can specify WITHOUT OIDS for all user tables.
IMHO this should be the default for CREATE TABLE some time in the near
future.

> 1. Check for the error string "Cannot insert a duplicate key into
> unique index pg_class_oid_index" in the batch shells for each
> function call and re-run if required.

If you define all your tables (especially temp tables) using WITHOUT
OIDS, you shouldn't need to bother with this.

> Can anyone comment if they know this is a fundamental limitation of
> PostgreSQL

It's not a "fundamental limitation" at all, merely a problem of
remaining backward compatible with the behavior of previous PostgreSQL
releases.

> We are running PostgreSQL 7.2.1 on Pentium 4 x86 type systems.

Upgrading to the latest stable 7.2 release (7.2.4), or if possible
7.3.2, is always encouraged.

Cheers,

Neil

Thanks for your comments.

I don't think we use OIDS in the application. Is the cluster command
dependent on OIDS at the row/tupple level?

Now that the application is live at our customers' site, have you any
suggestions on how we go about converting all of our permanent and
dynamically created app tables?

I am thinking of doing a schema dump and editing the file globally. Then a
schema load then a restore.

Is there a PG table I can update to achieve the same end?

Thanks again

Andrew Bartley

----- Original Message -----
From: "Neil Conway" <neilc(at)samurai(dot)com>
To: "Andrew Bartley" <abartley(at)evolvosystems(dot)com>
Cc: "PostgreSQL General" <pgsql-general(at)postgresql(dot)org>
Sent: Monday, March 24, 2003 2:14 PM
Subject: Re: [GENERAL] 4 billion + oids

> On Sun, 2003-03-23 at 21:35, Andrew Bartley wrote:
> > 1. Change all creation of temp tables with "without oids", hoping
> > to reduce the consumption of OIDS
>
> That should be the first thing you should do (in 7.4, there will be an
> ALTER TABLE command to disable OIDs). If you're not making use of OIDs
> in your application, you can specify WITHOUT OIDS for all user tables.
> IMHO this should be the default for CREATE TABLE some time in the near
> future.
>
> > 1. Check for the error string "Cannot insert a duplicate key into
> > unique index pg_class_oid_index" in the batch shells for each
> > function call and re-run if required.
>
> If you define all your tables (especially temp tables) using WITHOUT
> OIDS, you shouldn't need to bother with this.
>
> > Can anyone comment if they know this is a fundamental limitation of
> > PostgreSQL
>
> It's not a "fundamental limitation" at all, merely a problem of
> remaining backward compatible with the behavior of previous PostgreSQL
> releases.
>
> > We are running PostgreSQL 7.2.1 on Pentium 4 x86 type systems.
>
> Upgrading to the latest stable 7.2 release (7.2.4), or if possible
> 7.3.2, is always encouraged.
>
> Cheers,
>
> Neil
>
>
>

"Andrew Bartley" <abartley(at)evolvosystems(dot)com> writes:
> We have found this to be a major problem. It seems once the OIDs wrap; we =
> constantly get errors due to "Cannot insert a duplicate key into unique ind=
> ex pg_class_oid_index". There are about 3,000 entries in pg_class at this =
> stage.

Once the OID counter wraps, there's certainly some risk of OID
collisions. However, if you have only 3000 entries in pg_class it's
hard to see why the odds would be worse than 3000/4billion or less than
one failure in 1 million tries. I think there is something you have not
told us.

The nearby suggestions to minimize the rate of OID consumption seem
rather beside the point from here ... what I'd wonder about is why you
need as many as three thousand tables. Reducing that footprint should
reduce the odds of OID collision.

regards, tom lane

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.

Thanks...
Amin

On Mon, 24 Mar 2003 01:46:36 -0500
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>"Andrew Bartley" <abartley(at)evolvosystems(dot)com> writes:
>> We have found this to be a major problem. It seems once
>>the OIDs wrap; we =
>> constantly get errors due to "Cannot insert a duplicate
>>key into unique ind=
>> ex pg_class_oid_index". There are about 3,000 entries
>>in pg_class at this =
>> stage.
>
>Once the OID counter wraps, there's certainly some risk
>of OID
>collisions. However, if you have only 3000 entries in
>pg_class it's
>hard to see why the odds would be worse than
>3000/4billion or less than
>one failure in 1 million tries. I think there is
>something you have not
>told us.
>
>The nearby suggestions to minimize the rate of OID
>consumption seem
>rather beside the point from here ... what I'd wonder
>about is why you
>need as many as three thousand tables. Reducing that
>footprint should
>reduce the odds of OID collision.
>
> regards, tom lane
>
>
>---------------------------(end of
>broadcast)---------------------------
>TIP 6: Have you searched our list archives?
>
>http://archives.postgresql.org