DTOI4 integer out of range

"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