BUG #2525: "No buffer space available" error running pg_dump on win2k for large bytea values - Mailing list pgsql-bugs

The following bug has been logged online:

Bug reference:      2525
Logged by:          Steve McWilliams
Email address:      smcwilliams@emprisanetworks.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-general/2006-01/msg00328.php
http://archives.postgresql.org/pgsql-general/2006-01/msg00952.php
http://archives.postgresql.org/pgsql-general/2006-01/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/TheOnionRouter/WindowsBufferProblems

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.