Re: DTOI4 integer out of range

Hello,

I am in the process of upgrading my database server and OS.
I decided to upgrade to RedHat 8.0 and Postgres 7.2.3.

I have had some problems with the upgrade. When I tried to dump the
database using dump_all I recieved a similar dtoi4 error. After my heart
skipped a couple of beats I decided to "dump" the tables using a perl script
and "copy" commands.

Thanks for yor help!

----Original Message Follows----
From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "shey sewani" <pakix2000(at)hotmail(dot)com>
CC: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] DTOI4 integer out of range
Date: Fri, 04 Oct 2002 14:34:03 -0400
Received: from mc3-f9.law16.hotmail.com ([65.54.236.144]) by
mc3-s4.law16.hotmail.com with Microsoft SMTPSVC(5.0.2195.5600); Fri, 4 Oct
2002 11:43:49 -0700
Received: from relay2.pgsql.com ([64.49.215.143]) by
mc3-f9.law16.hotmail.com with Microsoft SMTPSVC(5.0.2195.5600); Fri, 4 Oct
2002 11:34:32 -0700
Received: from postgresql.org (postgresql.org [64.49.215.8])by
relay2.pgsql.com (Postfix) with ESMTPid CF8EEEE1A8A; Fri, 4 Oct 2002
14:34:12 -0400 (EDT)
Received: from localhost (postgresql.org [64.49.215.8])by postgresql.org
(Postfix) with ESMTP id 303D7476920for <pgsql-general(at)postgresql(dot)org>; Fri,
4 Oct 2002 14:34:06 -0400 (EDT)
Received: from sss.pgh.pa.us (unknown [192.204.191.242])by postgresql.org
(Postfix) with ESMTP id 60DCF47641Cfor <pgsql-general(at)postgresql(dot)org>; Fri,
4 Oct 2002 14:34:05 -0400 (EDT)
Received: from sss2.sss.pgh.pa.us (tgl(at)localhost [127.0.0.1])by
sss.pgh.pa.us (8.12.5/8.12.5) with ESMTP id g94IY3hR025454;Fri, 4 Oct 2002
14:34:04 -0400 (EDT)
In-reply-to: <F424WjcsQlcy8zJByl800009769(at)hotmail(dot)com>
References: <F424WjcsQlcy8zJByl800009769(at)hotmail(dot)com>
Comments: In-reply-to "shey sewani" <pakix2000(at)hotmail(dot)com>message dated
"Fri, 04 Oct 2002 10:03:42 -0500"
Message-ID: <25453(dot)1033756443(at)sss(dot)pgh(dot)pa(dot)us>
X-Virus-Scanned: by AMaViS new-20020517
Precedence: bulk
Sender: pgsql-general-owner(at)postgresql(dot)org
Return-Path: pgsql-general-owner+M31271(at)postgresql(dot)org
X-OriginalArrivalTime: 04 Oct 2002 18:34:33.0011 (UTC)
FILETIME=[AED33030:01C26BD4]

"shey sewani" <pakix2000(at)hotmail(dot)com> writes:
> I am currently running Postgres 7.1.2 on my RedHat 7.1 Linux machines. I
> have had a database on postgres for two years now. During the past two
> years the database size has grown considerably.

> There are more than 250 Million records in the database. Furthermore
many
> of these records have been inserted, deleted and re-inserted over the
past 2
> years.

> Within the last few days I have recieved several errors when using
pgaccess
> with postgres. The most common error I recieve is
> "dtoi4 integer out of Range, OID 218700327" when I try to update a record
> through pgaccess. I do not recieve any errors when I do updates with
psql
> or updates with programs that use libpq++.

Evidently your OID counter has passed 2 billion. OID is supposed to be
unsigned, so it can go to 4 billion ... but we used to have quite a lot
of client code that was sloppy and tried to store OIDs in plain "int"
variables. It looks like you have some variant of that problem here.

I would recommend an update to PG 7.2.3 in the near future.
That will at least temporarily solve your OID problems, since the
dump/initdb/reload process will reset the OID counter. (I can't honestly
say whether pgaccess has been fixed to deal correctly with OIDs > 2G;
anybody know?)

The reason I think you should do this soon is that if your OID counter
is approaching wraparound then your transaction counter may be too.
7.1.* will *not* survive wraparound of the transaction counter --- after
4 billion transactions, it's more or less sayonara to your data, because
after the counter wraps all your rows look to be "in the future". 7.2
is our first release that can survive more than 4G transactions without
problems. See
http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)

Sheheryar Sewani
Houston, TX

_________________________________________________________________
Join the worlds largest e-mail service with MSN Hotmail.
http://www.hotmail.com