Dump

Mihai Gheorghiu <tanhq@bigplanet.com> writes:

I wanted to back up a database prior to upgrading to 7.0.2
pg_dump dbname > dbname.bak
FATAL 1: Memory exhausted in AllocSetAlloc()
PQendcopy: resetting connection
SQL query to dump the contents of Table 'tblname' did not execute correctly.

Hmm, what version are you using now? COPY used to have a memory leak
problem according to the CVS logs, but that was long ago (pre-6.4).

regards, tom lane

Error in dumpig defaults on serial type!
If table name have non-statndart name (example: "Order")
sequenser auto created with name "Order_id_seq".
In the dump filed definition is:

...
"id" int4 DEFAULT nextval ( 'Order_id_seq' ) NOT NULL,
...

, but need:

...
"id" int4 DEFAULT nextval ( '"Order_id_seq"' ) NOT NULL,
...

--
Trurl McByte, Capt. of StasisCruiser "Prince"
|InterNIC: AR3200 RIPE: AR1627-RIPE|
|--98 C3 78 8E 90 E3 01 35 87 1F 3F EF FD 6D 84 B3--|

7.0 on RH6.1
I read about the 6.2 requirement only yesterday, so I'm in the process of
upgrading the OS.
But is this the cause?

Mihai Gheorghiu <tanhq@bigplanet.com> writes:

I wanted to back up a database prior to upgrading to 7.0.2
pg_dump dbname > dbname.bak
FATAL 1: Memory exhausted in AllocSetAlloc()
PQendcopy: resetting connection
SQL query to dump the contents of Table 'tblname' did not execute

correctly.

All tables have names starting in tbl - they were exported from Access using
the public domain ODBC drivers.
Could the problem originate here?

Trurl McByte <trurl@realtor3d.odessa.ua> writes:

Error in dumpig defaults on serial type!
If table name have non-statndart name (example: "Order")
sequenser auto created with name "Order_id_seq".
In the dump filed definition is:
...
"id" int4 DEFAULT nextval ( 'Order_id_seq' ) NOT NULL,
...
, but need:
...
"id" int4 DEFAULT nextval ( '"Order_id_seq"' ) NOT NULL,
...

Hmm. This is not pg_dump's fault: the default expression is actually
being stored that way in the database. Someone seems to have thought
it was a good idea to strip the double quotes at parse time instead
of run time :-(.

Will fix for 7.1 ... in the meantime, don't name your sequences that way
...

regards, tom lane

Mihai Gheorghiu <tanhq@bigplanet.com> writes:

I wanted to back up a database prior to upgrading to 7.0.2
pg_dump dbname > dbname.bak
FATAL 1: Memory exhausted in AllocSetAlloc()
PQendcopy: resetting connection
SQL query to dump the contents of Table 'tblname' did not execute
correctly.

Hmm, what version are you using now? COPY used to have a memory leak
problem according to the CVS logs, but that was long ago (pre-6.4).

7.0 on RH6.1

OK, so much for the old-version theory. What is the full declaration of
table 'tblname'? (Easiest way to get it is pg_dump -s -t tblname dbname.)
Also, how many rows in the table?

regards, tom lane

pg_dump... outputs nothing!? I did it with -f filename too, and the file is
empty.
All the files in the database were exported from MSAccess using the psqlODBC
driver directly into Postgres, with no prior declaration.
Size of the file in question: 5MB, 7062 rows. Name of table:
tblReservations.
I wanted to re-create the error today, but I got something different:

pg_dump tantest > tantest.bak
pqWait() -- connection not open
PQendcopy: resetting connection
SQL query to dump the contents of Table 'tblTransactionsUnprocessed' did not
execute correctly. After we read all the table contents from the backend,
PQendcopy failed. Explanation from backend: '
The Data Base System is starting up
'.
The query was: 'COPY "tblTransactionsUnprocessed" TO stdout;
'.

tbl TransactionsUnprocessed: 98kB, 378 rows
BTW, this leaves temp file traces in the database. Next time I want to try
pg_dump I get a "No such file or directory" error. I look with pgaccess and
there is a table with that name shown, only no such file in the tantest
directory.

Tried once again and I got no error!!! Any idea what's going on?
Then:

createdb newtest
psql -e newtest < tantest.bak

Crashes in tblTransactions (3.5MB, 117krows) with:

COPY "tblTransactions" FROM stdin
ERROR: copy: line 116229, Bad timestamp external representation '2000-05-15
00800:00-08'
PQendcopy: resetting connection

If there was such an error in the original table, shouldn't it be shown by
pg_dump?

On Wed, 7 Jun 2000 (Yesterday), Tom Lane wrote:

TL> Trurl McByte <trurl@realtor3d.odessa.ua> writes:
TL> > Error in dumpig defaults on serial type!
TL> > If table name have non-statndart name (example: "Order")
TL> > sequenser auto created with name "Order_id_seq".
TL> > In the dump filed definition is:
TL> > ...
TL> > "id" int4 DEFAULT nextval ( 'Order_id_seq' ) NOT NULL,
TL> > ...
TL> > , but need:
TL> > ...
TL> > "id" int4 DEFAULT nextval ( '"Order_id_seq"' ) NOT NULL,
TL> > ...
TL>
TL> Hmm. This is not pg_dump's fault: the default expression is actually
TL> being stored that way in the database. Someone seems to have thought
TL> it was a good idea to strip the double quotes at parse time instead
TL> of run time :-(.

In the same dump pg_dump writes the following:
...
CREATE SEQUENCE "Order_id_seq" start 0 increment 1 maxvalue 2147483647 minvalue 1 cache 1 ;
SELECT nextval ('"Order_id_seq"');
...

Quite probably and serial type it is possible to generate differently.

TL>
TL> Will fix for 7.1 ... in the meantime, don't name your sequences that way
TL> ...
I shall wait for...
TL>
TL> regards, tom lane
Thanx

--
Trurl McByte, Capt. of StasisCruiser "Prince"
|InterNIC: AR3200 RIPE: AR1627-RIPE|
|--98 C3 78 8E 90 E3 01 35 87 1F 3F EF FD 6D 84 B3--|

Mihai Gheorghiu <tanhq@bigplanet.com> writes:

pg_dump... outputs nothing!? I did it with -f filename too, and the file is
empty.
Size of the file in question: 5MB, 7062 rows. Name of table:
tblReservations.

Mixed case huh? It's a little tricky to get pg_dump's -t switch to work
with that; I think you have to write
pg_dump -s -t '"tblReservations"' dbname ...
Without the quoting, pg_dump lowercases the given name.

I wanted to re-create the error today, but I got something different:
pg_dump tantest > tantest.bak
pqWait() -- connection not open
PQendcopy: resetting connection

Hmm, that looks like a backend coredump. Did you find a core file in
the database directory? If so, can you get a backtrace from it?

regards, tom lane