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

Fri, 31 Aug 2001 16:04:31 -0400
Martin Weinberg <weinberg@osprey.astro.umass.edu>
Why "ERROR:  dtoi4: integer out of range" on pg_dump
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

Mon, 03 Sep 2001 17:46:29 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Why "ERROR: dtoi4: integer out of range" on pg_dump 
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
>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
>
>---------------------------(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
>



Fri, 07 Sep 2001 13:29:34 -0400
Martin Weinberg <weinberg@osprey.astro.umass.edu>
Re: Why "ERROR: dtoi4: integer out of range" on pg_dump 
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

Fri, 07 Sep 2001 15:15:40 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Why "ERROR: dtoi4: integer out of range" on pg_dump 