Thread: BUG #2525: "No buffer space available" error running pg_dump on win2k for large bytea values

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.

No ideas on this I take it?

>
> 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.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster


Steve McWilliams
Software Engineer
Emprisa Networks
703-691-0433x21
smcwilliams@emprisanetworks.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.