Thread: DTOI4 integer out of range
Hello, 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++. Can anyone shed some light on this problem? Thank you, Sheheryar Sewani. Sheheryar Sewani Houston, TX _________________________________________________________________ MSN Photos is the easiest way to share and print your photos: http://photos.msn.com/support/worldwide.aspx
"shey sewani" <pakix2000@hotmail.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
Tom Lane wrote: > "shey sewani" <pakix2000@hotmail.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. Tom, I see this OID as 200M, not 2B. Am I missing something? -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Bruce Momjian <pgman@candle.pha.pa.us> writes:
>>> "dtoi4 integer out of Range, OID 218700327" when I try to update a record
> Tom, I see this OID as 200M, not 2B. Am I missing something?
I think he must have mistyped --- dtoi4 wouldn't be complaining if the
input were 200M.
regards, tom lane
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > >>> "dtoi4 integer out of Range, OID 218700327" when I try to update a record > > > Tom, I see this OID as 200M, not 2B. Am I missing something? > > I think he must have mistyped --- dtoi4 wouldn't be complaining if the > input were 200M. Yes, I thought about a mistype, but it looked like cut/paste, unless he left off the last digit. shey? -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
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@sss.pgh.pa.us> To: "shey sewani" <pakix2000@hotmail.com> CC: pgsql-general@postgresql.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@postgresql.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@postgresql.org>; Fri, 4 Oct 2002 14:34:05 -0400 (EDT) Received: from sss2.sss.pgh.pa.us (tgl@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@hotmail.com> References: <F424WjcsQlcy8zJByl800009769@hotmail.com> Comments: In-reply-to "shey sewani" <pakix2000@hotmail.com>message dated "Fri, 04 Oct 2002 10:03:42 -0500" Message-ID: <25453.1033756443@sss.pgh.pa.us> X-Virus-Scanned: by AMaViS new-20020517 Precedence: bulk Sender: pgsql-general-owner@postgresql.org Return-Path: pgsql-general-owner+M31271@postgresql.org X-OriginalArrivalTime: 04 Oct 2002 18:34:33.0011 (UTC) FILETIME=[AED33030:01C26BD4] "shey sewani" <pakix2000@hotmail.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@postgresql.org) Sheheryar Sewani Houston, TX _________________________________________________________________ Join the world�s largest e-mail service with MSN Hotmail. http://www.hotmail.com