followup question Bug #476: pg_dump error: dtoi4: integer out of range

Started by Nathan Sudermanover 23 years ago2 messagesbugs
Jump to latest
#1Nathan Suderman
nathan@pollstar.com

all I can find out about this bug is that it was reported back in
October 2001, all the lists make no mention of it after that. posts
from Tom indicate that 7.1+ do not have an issue with running out of
OID's yet I still have a problem. I run a fairly large database that
gets updated via temp tables several times a day, as such I passed the
int4 limit for an OID yesterday. Now supposedly they are just suppose
to roll over the top and continue working, however, in my situation it
paralyzes the database usefulness. The database itself continues to run
just fine however I am using a combination of pg_dumps and restores to
distribute the data to a number of other servers. Since rolling over
the int4 limit pg_dump gives the following error:

$ pg_dump news -v -t admin

-- saving database definition
-- last builtin oid is 18539
-- reading user-defined types
-- reading user-defined functions
-- reading user-defined aggregates
-- reading user-defined operators
-- reading user-defined tables
getTables(): SELECT (for PRIMARY KEY) failed on table hp_photo_type.
Explanation from backend: ERROR: dtoi4: integer out of range

it used to fail on other tables so I removed all the offending tables
(so I could at least make a backup) dropped the database and recreated
it then imported all the data. No help, now the rest of the tables are
complaining. the table listed above has the following OID's
2204178165
2204178166
2204178167
2204178168
2204178169

this is the table structure
\d hp_photo_type
Table "hp_photo_type"
Attribute | Type | Modifier
---------------+-----------------------+----------
hppt_key | character(2) | not null
hppt_typename | character varying(50) |
Indices: hp_photo_type_hppt_key,
hp_photo_type_pkey

I have even tried to create a brand new DB and insert some data then
dump, I get the same error.

At this point I am going to re-install postgres so that I can get some
new OID's but I am concerned that I will be right back in the same place
in 6 months.

I am running V 7.1.1

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Nathan Suderman (#1)
Re: followup question Bug #476: pg_dump error: dtoi4: integer out of range

"Nathan Suderman" <Nathan@pollstar.com> writes:

$ pg_dump news -v -t admin
getTables(): SELECT (for PRIMARY KEY) failed on table hp_photo_type.
Explanation from backend: ERROR: dtoi4: integer out of range

I am running V 7.1.1

7.1's pg_dump has a lot of problems with OIDs past 2G --- it's not
careful about signed versus unsigned values.

I'd recommend an update to 7.2.1. You should be able to use 7.2.1's
pg_dump to dump the database.

regards, tom lane