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(at)osprey(dot)astro(dot)umass(dot)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(at)postgresql(dot)org so that your
>message can get through to the mailing list cleanly
>