Re: [BUGS] BUG #2525: 'No buffer space available' error running pg_dump on win2k for large bytea values

#1

No ideas on this I take it?

Quote:

The following bug has been logged online:

Bug reference: 2525
Logged by: Steve McWilliams
Email address: smcwilliams (AT) emprisanetworks (DOT) com
PostgreSQL version: 8.1.4
Operating system: Windows 2000
Description: "No buffer space available" error running pg_dump
on win2k for large bytea values
Details:

We are having a problem with pg_dump that seems to be limited to windows
2000 platforms when we try to dump a db containing large bytea values.
When I run pg_dump (options "-b -Fc -Z0") to dump the contents of a test
database that isolates this problem I get the following error printed to
the terminal:

pg_dump: could not receive data from server: No buffer space available
(0x00002747/10055)
pg_dump: SQL command to dump contents of table 'my_table' failed:
PQendcopy() failed.
pg_dump: Error message from server: could not receive data from server:
No buffer space available (0x00002747/10055)
pg_dump: The command was: COPY public.my_table (my_id, my_array) TO
stdout;

The postmaster log file shows the following corresponding entry:

LOG: could not receive data from client: No connection could be made
because the target machine actively refused it.

The database I am backing up here contains a single table. The table
contains 2 columns, an integer id column and a bytea column. The table
contains a single row with a bytea value containing 25.7 mb of binary
data. If necessary I can send a dump of the db in question containing
its schema and data (generated on linux).

I am running postgres 8.1.4 on windows 2000 v5.00.2195 (srevice pack 4)
on a pentium 4 2.6 ghz dedicated machine with 784 mb physical memory and
1.5 gb total virtual memory. The same machine is running both the
postgres server and the pg_dump client, with nothing much else going on
concurrently. I do not see the OS running out of resources during the
pg_dump attempt (watching via TaskManager).

We are not passing any options to postmaster at launch time other than
the "-i" option to tell it to use tcp sockets, and we are using the
default values in postgresql.conf.

Note we are not able to reproduce this on windows xp pro or on windows
2003, or on linux. On windows 2k however it seems to always happen.

A co-worker here reported this problem to pgsql-general several months
ago. The relevent links are:

http://archives.postgresql.org/pgsql...1/msg00328.php
http://archives.postgresql.org/pgsql...1/msg00952.php
http://archives.postgresql.org/pgsql...1/msg00927.php

At the time my co-worker thought he had worked around it by adding a
sleep to the pg_dump read loop. This may have helped at the time (at
the expense of making dumps take much much longer) I'm not sure, however
recently the problem has returned so we need to find a better solution.

As pointed out in one of the responses in the link above, the underlying
problem seems to be that windows is returning a WSAENOBUFS error at some
point during the transfer of data over the socket involved. The
microsoft link referred to is:

http://support.microsoft.com/kb/q201213

I'm not sure what the socket send/receive buffer sizes being used by
postgres are. I tried hacking the postgres source where the sockets are
created (both server and client sockets) to set their options to make
sure they use buffer sizes less than 64k as suggested by microsoft,
however that did not affect the problem.

The tweakable registry entries referred to I am guessing are the same
ones mentioned in another link I found when googling this problem:

http://wiki.noreply.org/noreply/TheO...BufferProblems

I have not tried these registry tweaks yet, but from the description I
don't think they would help because they pertain to WSAENOBUFS problems
that occur when many sockets (5000+) are being used by the application,
which is not the case here.

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Steve McWilliams
Software Engineer
Emprisa Networks
703-691-0433x21
smcwilliams (AT) emprisanetworks (DOT) com

The information contained in this communication is intended only for the
use of the recipient named above, and may be legally privileged,
confidential and exempt from disclosure under applicable law. If the
reader of this communication is not the intended recipient, you are hereby
notified that any dissemination, distribution or copying of this
communication, or any of its contents, is strictly prohibited. If you have
received this communication in error, please resend this communication to
the sender and delete the original communication and any copy of it from
your computer system. Thank you.

---------------------------(end of broadcast)---------------------------
TIP 1: 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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: [BUGS] BUG #2525: 'No buffer space available' error running pg_dump on win2k for large bytea values

mailing.database.pgsql-bugs mailing.database.pgsql-bugs

Re: [BUGS] BUG #2525: 'No buffer space available' error running pg_dump on win2k for large bytea values - 07-20-2006 , 03:33 PM