Why "ERROR: dtoi4: integer out of range" on pg_dump
Folks,
We have a database with several very large tables. When trying
to pg_dump we get the above error, e.g.:
pg_dump -v wsdb
-- 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 v3otgdsrcq.
Explanation from backend: ERROR: dtoi4: integer out of range
Making another small database (same system, 7.1.2 on Debian/GNU Linux
2.2), gives the same sort of problem:
pg_dump -v tmp
-- 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
-- reading indices information
-- reading table inheritance information
-- finding the attribute names and types for each table
-- finding the attrs and types for table: 'tmp'
-- flagging inherited attributes in subtables
-- dumping out database comment
DumpComment: SELECT failed: 'ERROR: dtoi4: integer out of range
If I init a new db and restart postgres with the new base,
no problem.
I suspect some sort of corruption but we're not sure where to
look. A vacuum did not help. We'd like to recover, if at all
possible. Any ideas (no luck on other lists or I wouldn't post
here)?
TIA,
--Martin
Martin Weinberg <weinberg@osprey.astro.umass.edu> writes:
DumpComment: SELECT failed: 'ERROR: dtoi4: integer out of range
Hmm. I can reproduce this error message if I suppose that you have
OIDs exceeding 2 billion. pg_dump will produce queries like:
regression=# select * from pg_description where objoid = 2500000000;
ERROR: dtoi4: integer out of range
A short-term workaround is to hack pg_dump so that it explicitly coerces
the literal to OID and/or quotes the literal:
regression=# select * from pg_description where objoid = 2500000000::oid;
objoid | classoid | objsubid | description
--------+----------+----------+-------------
(0 rows)
regression=# select * from pg_description where objoid = '2500000000';
objoid | classoid | objsubid | description
--------+----------+----------+-------------
(0 rows)
This is done in many places in pg_dump, but not in DumpComment which is
relatively new code :-(
A longer-term question is how to persuade the parser to get this right
without such help. I think that this is another variant of the
perennial numeric-precision issue and will not be real easy to fix.
regards, tom lane
Thanks, Tom! This was the problem. Here is my patch to pg_dump.c
that appears to fix the problem. Turns out that the oid needed to
be coerced in two places.
-------------------------------------------------------------------------------
--- pg_dump.c Thu Sep 6 21:18:21 2001
+++ pg_dump.c.orig Thu Sep 6 21:19:08 2001
@@ -2289,7 +2289,7 @@ resetPQExpBuffer(query);
appendPQExpBuffer(query,
- "SELECT Oid FROM pg_index i WHERE i.indisprimary AND i.indrelid =
'%s'::oid ",
+ "SELECT Oid FROM pg_index i WHERE i.indisprimary AND i.indrelid = %s
",
tblinfo[i].oid);
res2 = PQexec(g_conn, query->data);
if (!res2 || PQresultStatus(res2) != PGRES_TUPLES_OK)
@@ -3035,7 +3035,6 @@
query = createPQExpBuffer();
appendPQExpBuffer(query, "SELECT description FROM pg_description WHERE
objoid = ");
appendPQExpBuffer(query, oid);
- appendPQExpBuffer(query, "::oid");/*** Execute query ***/
-------------------------------------------------------------------------------
Tom Lane wrote on Mon, 03 Sep 2001 17:46:29 EDT
Show quoted text
Martin Weinberg <weinberg@osprey.astro.umass.edu> writes:
DumpComment: SELECT failed: 'ERROR: dtoi4: integer out of range
Hmm. I can reproduce this error message if I suppose that you have
OIDs exceeding 2 billion. pg_dump will produce queries like:regression=# select * from pg_description where objoid = 2500000000;
ERROR: dtoi4: integer out of rangeA short-term workaround is to hack pg_dump so that it explicitly coerces
the literal to OID and/or quotes the literal:regression=# select * from pg_description where objoid = 2500000000::oid;
objoid | classoid | objsubid | description
--------+----------+----------+-------------
(0 rows)regression=# select * from pg_description where objoid = '2500000000';
objoid | classoid | objsubid | description
--------+----------+----------+-------------
(0 rows)This is done in many places in pg_dump, but not in DumpComment which is
relatively new code :-(A longer-term question is how to persuade the parser to get this right
without such help. I think that this is another variant of the
perennial numeric-precision issue and will not be real easy to fix.regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
Martin Weinberg <weinberg@osprey.astro.umass.edu> writes:
Thanks, Tom! This was the problem. Here is my patch to pg_dump.c
that appears to fix the problem. Turns out that the oid needed to
be coerced in two places.
I've already committed fixes (I found one or two more places that were
missing the same coercion :-().
regards, tom lane