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






Wed, 10 Oct 2001 16:32:16 +0100
steve <steve@jlajla.com>
pg_dump oid problems
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

Thu, 11 Oct 2001 00:45:59 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: pg_dump oid problems 
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


Thu, 11 Oct 2001 08:49:06 +0100
steve <steve@jlajla.com>
Re: pg_dump oid problems
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

Thu, 11 Oct 2001 12:15:16 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: pg_dump oid problems 
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)


Fri, 12 Oct 2001 09:24:37 +0100
steve <steve@jlajla.com>
Re: pg_dump oid problems