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

No ideas on this I take it?

>
> 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-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(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.