Re: DTOI4 integer out of range

"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