Thread: pg_dump oid problems
Apologies for posting to [Hackers], have already posted to [Patches] with no reply. When trying to pg_dump on 7.1.2 (& 7.1.3) I get the following error message: bash-2.04$ pg_dump dwh getTables(): SELECT (for PRIMARY KEY NAME) failed for table nlcdmp. Explanation from backend: ERROR: dtoi4: integer out of range bash-2.04$ pg_dump -v dwh -- 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 NAME) failed for table nlcdmp. Explanation from backend: ERROR: dtoi4: integer out of range I have already applied the patches described by Martin Weinberg and Tom Lane (see below), but this doesn't deem to have fixed my problem. --------- --- pg_dump.cThu Sep 6 21:18:21 2001 +++ pg_dump.c.origThu 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 ***/ -------- Several of my tables have very large OIDs (over 4 billion in some cases ! don't know why) , these are obviously also causing dtoi4 error messages when entering table design in pgaccess, but one can carry on past the messages and continue working. I am also having problems in CodeCharge using the ODBC driver - Codecharge fails to get column names for tables with high OIDs. Tables with lower OIDs in the same database work fine :-) I've had no problems with any previous version of PostgreSQL much of the data in this database has been progressively migrated over the last couple of years from 6.2. My interest in pg_dump is to dump my database without OIDs (normally I dump with OIDs so I've been carrying these big numbers for some time), drop everything and rebuild (psql < data.out) so that I hopefully get new smaller OIDs generated. Is this likely to work if I get round the pg_dump problems? Anyway, what's needed now is suggestions as to what else I must do to get pg_dump working with my large OIDs, any ideas?? Thanks, Steve
steve <steve@jlajla.com> writes:
> When trying to pg_dump on 7.1.2 (& 7.1.3) I get the following error
> message:
> bash-2.04$ pg_dump dwh
> getTables(): SELECT (for PRIMARY KEY NAME) failed for table nlcdmp.
> Explanation from backend: ERROR: dtoi4: integer out of range
> Several of my tables have very large OIDs (over 4 billion in some cases
Hmm. Okay, I think I can see how over-2-gig OIDs might lead to that
error message, but that doesn't really help in tracking down the specific
location of the problem. Could you run pg_dump after doingexport PGOPTIONS="-d2"
so that its queries get sent to the postmaster log? Then looking at the
log to see the last couple of queries before the failure should tell us.
regards, tom lane
Tom, Thanks for the prompt reply. Following is the postgresql log output: DEBUG: ProcessQuery DEBUG: CommitTransactionCommand DEBUG: StartTransactionCommand DEBUG: query: SELECT Oid FROM pg_index i WHERE i.indisprimary AND i.indrelid = '3527162388'::oid DEBUG: ProcessQuery DEBUG: CommitTransactionCommand DEBUG: StartTransactionCommand DEBUG: query: SELECT c.relname FROM pg_index i LEFT OUTER JOIN pg_class c ON c.oid = i.indexrelid WHERE i.indrelid = 3527162388AND i.indisprimary ERROR: dtoi4: integer out of range DEBUG: AbortCurrentTransaction DEBUG: proc_exit(0) DEBUG: shmem_exit(0) DEBUG: exit(0) The 3527162388AND is exactly as shown in the log, with no space between the value and the AND, I guess this is the problem, wherever it's being generated in the code. HTH Thanks, Steve Tom Lane wrote: > steve <steve@jlajla.com> writes: > > When trying to pg_dump on 7.1.2 (& 7.1.3) I get the following error > > message: > > > bash-2.04$ pg_dump dwh > > getTables(): SELECT (for PRIMARY KEY NAME) failed for table nlcdmp. > > Explanation from backend: ERROR: dtoi4: integer out of range > > > Several of my tables have very large OIDs (over 4 billion in some cases > > Hmm. Okay, I think I can see how over-2-gig OIDs might lead to that > error message, but that doesn't really help in tracking down the specific > location of the problem. Could you run pg_dump after doing > export PGOPTIONS="-d2" > so that its queries get sent to the postmaster log? Then looking at the > log to see the last couple of queries before the failure should tell us. > > regards, tom lane
steve <steve@jlajla.com> writes:
> DEBUG: query: SELECT c.relname FROM pg_index i LEFT OUTER JOIN pg_class c
> ON c.oid = i.indexrelid WHERE i.indrelid = 3527162388AND i.indisprimary
> ERROR: dtoi4: integer out of range
> The 3527162388AND is exactly as shown in the log, with no space between the
> value and the AND, I guess this is the problem, wherever it's being
> generated in the code.
That's evidently coming from line 2346 of src/bin/pg_dump/pg_dump.c:
"WHERE i.indrelid = %s"
Try changing it to
"WHERE i.indrelid = '%s'::oid "
(Problem seems to be solved already in 7.2devel)
regards, tom lane
Problem solved, 3GB dumped OK -- Thanks Tom Steve Tom Lane wrote: > steve <steve@jlajla.com> writes: > > DEBUG: query: SELECT c.relname FROM pg_index i LEFT OUTER JOIN pg_class c > > ON c.oid = i.indexrelid WHERE i.indrelid = 3527162388AND i.indisprimary > > ERROR: dtoi4: integer out of range > > > The 3527162388AND is exactly as shown in the log, with no space between the > > value and the AND, I guess this is the problem, wherever it's being > > generated in the code. > > That's evidently coming from line 2346 of src/bin/pg_dump/pg_dump.c: > > "WHERE i.indrelid = %s" > > Try changing it to > > "WHERE i.indrelid = '%s'::oid " > > (Problem seems to be solved already in 7.2devel) > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)