Обсуждение: 7.4.6 pg_dump failed

I am dumping some larger (less-used) tables from 7.4.6 to facilitate an
upgrade to 8.0.

A pg_dump of one table ran for 28:53:29.50 and produced a 30 GB dump
before it aborted with:

pg_dump: dumpClasses(): SQL command failed
pg_dump: Error message from server: out of memory for query result
pg_dump: The command was: FETCH 100 FROM _pg_dump_cursor

What causes this?  This is on a Sparc box with 8GB of real memory and
104GB of virtual, so I am fairly confident that it did not run out of
memory.  What would cause pg_dump to run for almost 29 hours and then
die?  The table has not been accessed during that period.

To be fair, the table is not tiny, it consists of 114 segments in the
base directory: files 17935163 through 17935163.113.

The table contains a text field that could contain several hundred MB of
data, although always less than 2GB.

How can I dump this table?

Thanks,
Marty

Marty Scholes <marty@outputservices.com> writes:
> A pg_dump of one table ran for 28:53:29.50 and produced a 30 GB dump
> before it aborted with:

> pg_dump: dumpClasses(): SQL command failed
> pg_dump: Error message from server: out of memory for query result
> pg_dump: The command was: FETCH 100 FROM _pg_dump_cursor

Even though it says "from server", this is actually an out-of-memory
problem inside pg_dump, or more specifically inside libpq.

> The table contains a text field that could contain several hundred MB of
> data, although always less than 2GB.

"Could contain"?  What's the actual maximum field width, and how often
do very wide values occur?  I don't recall the exact space allocation
algorithms inside libpq, but I'm wondering if it could choke on such a
wide row.

You might have better luck if you didn't use -d.

            regards, tom lane

The table contains postscript documents.  My company prints and mails
about 50,000 pages of invoices each day for a customer. We are
experimenting with putting up a 7 year web based document archive for
their customers to cut mailing costs and make it easier for their
customers to take advantage of early pay discounts.  The archive might
also be used for the customer service reps at the call centers.

I was able to truncate the table since this is experimental and most of
the data can be reconstructed after the upgrade to 8.0.  The TEXT field
contains the postscript of each document.  Some invoices are 10,000+
pages.  The largest one I recall was about 200 MB in size.

If we do get this project rolling under Pg, I will still have the issue
with dumping.  Perhaps it was the -d option that caused it.  I
appreciate the feedback.

Marty

Tom Lane wrote:
> Marty Scholes <marty@outputservices.com> writes:
>
>>A pg_dump of one table ran for 28:53:29.50 and produced a 30 GB dump
>>before it aborted with:
>
>
>>pg_dump: dumpClasses(): SQL command failed
>>pg_dump: Error message from server: out of memory for query result
>>pg_dump: The command was: FETCH 100 FROM _pg_dump_cursor
>
>
> Even though it says "from server", this is actually an out-of-memory
> problem inside pg_dump, or more specifically inside libpq.
>
>
>>The table contains a text field that could contain several hundred MB of
>>data, although always less than 2GB.
>
>
> "Could contain"?  What's the actual maximum field width, and how often
> do very wide values occur?  I don't recall the exact space allocation
> algorithms inside libpq, but I'm wondering if it could choke on such a
> wide row.
>
> You might have better luck if you didn't use -d.
>
>             regards, tom lane

I wrote:
>> You might have better luck if you didn't use -d.

After looking into it, the above seems to be sound advice.  pg_dump
appears to make several (three or four) copies of a wide field value
when using -d or -D mode, but only one copy when using the default
COPY mode.  I made a minor change in 8.0 sources to get rid of one
extra copy step, but getting it below three copies looks to be
impractical.

In any case it'd be a good idea to increase the data-segment ulimit
setting you are using for both the postmaster and pg_dump, if you intend
to push multi-hundred-meg field values around.

            regards, tom lane