Thread: No buffer space available

I have an SQL file with a set of about 3000 insert statements. This
file is executed twice from a remote client machine running Windows
XP. The first time it is executed against a Windows 2003 Server
running PostgreSQL 8.0. The second time it is executed against a
Windows 2003 Server running PostgreSQL 8.2. This setup has been in
place for about a year now and never had any issues.

However, I noticed today that all the file executions fail with
[nativecode=could not send data to server: No buffer space available
(0x00002747/10055)] error.

I attempted to run the same file using pgAdmin III version 1.6.2, but
every time the file is executed the only return in messages pane is
the colon sign ":". No data is inserted.

I attempted to run the same file using psql.exe and this worked
without any problems.

I also noticed that if I use less inserts, for example only a few
hundred, the file executes fine.

What could be the issue?

Thank you.

Nik

Nik wrote:
> I have an SQL file with a set of about 3000 insert statements. This
> file is executed twice from a remote client machine running Windows
> XP. The first time it is executed against a Windows 2003 Server
> running PostgreSQL 8.0. The second time it is executed against a
> Windows 2003 Server running PostgreSQL 8.2. This setup has been in
> place for about a year now and never had any issues.
>
> However, I noticed today that all the file executions fail with
> [nativecode=could not send data to server: No buffer space available
> (0x00002747/10055)] error.
>
> I attempted to run the same file using pgAdmin III version 1.6.2, but
> every time the file is executed the only return in messages pane is
> the colon sign ":". No data is inserted.
>
> I attempted to run the same file using psql.exe and this worked
> without any problems.
>
> I also noticed that if I use less inserts, for example only a few
> hundred, the file executes fine.
>
> What could be the issue?
>
> Thank you.
>
> Nik
>
    This error is returned by libpq, as a reaction on WSANOBUFS error
from Windows Socket System. This means, applications tries to send much
data, and system can't do it the same quickly. Possible, libpq should
handle this error more carefully, and wait until all data will be send
to the server.
    To avoid this problem you can:
1) try to use client software, which is not depends on libpq,
or
2) divide your query into smaller parts,
or
3) use software which parses long queries, separates them into single
SQL commands and then executes this commands one by one.

On 3/7/07, andyk <andyk@commandprompt.com> wrote:

> 1) try to use client software, which is not depends on libpq,
> or
> 2) divide your query into smaller parts,
> or
> 3) use software which parses long queries, separates them into single
> SQL commands and then executes this commands one by one.
4) use Postgres on a Unix-type operating system...

On Tue, Mar 06, 2007 at 08:38:22PM +0200, andyk wrote:
>    This error is returned by libpq, as a reaction on WSANOBUFS error
> from Windows Socket System. This means, applications tries to send much
> data, and system can't do it the same quickly. Possible, libpq should
> handle this error more carefully, and wait until all data will be send
> to the server.

That's bizarre. If libpq is sending data too fast then the socket
library should block. The only possible thing lbpq can do is send it
again, which will just give the same error... busy loop. This is a
problem the socket library should handle for you.

Incidently, according to this page:

http://www.dispi.com/proxyplus/faq/articles/EN/art10002.htm

The error relates to too many sockets, rather than out of memory. In
which case libpq really can't do anything about it.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

On Wed, Mar 07, 2007 at 07:46:16AM +0100, Martijn van Oosterhout wrote:
> On Tue, Mar 06, 2007 at 08:38:22PM +0200, andyk wrote:
> >    This error is returned by libpq, as a reaction on WSANOBUFS error
> > from Windows Socket System. This means, applications tries to send much
> > data, and system can't do it the same quickly. Possible, libpq should
> > handle this error more carefully, and wait until all data will be send
> > to the server.
>
> That's bizarre. If libpq is sending data too fast then the socket
> library should block. The only possible thing lbpq can do is send it
> again, which will just give the same error... busy loop. This is a
> problem the socket library should handle for you.
>
> Incidently, according to this page:
>
> http://www.dispi.com/proxyplus/faq/articles/EN/art10002.htm
>
> The error relates to too many sockets, rather than out of memory. In
> which case libpq really can't do anything about it.

This points even more clearly to asking the standard questino for any
network related stuff - are you running any personal firewall software
(other than the one builtin to windows - that one is known not to be so
troublesome), bandwidth limiters or antivirus that has network scanning
fetures? If so, try uninstalling it (you can first try disabling it, but
quite often you need to uninstall all the way).

(It could still simply be increasing the number of connections too far,
but a broken fw might cause the problem to show up even though it's not
really there)

//Magnus

On Mar 6, 2:20 pm, andrej.gro...@gmail.com ("Andrej Ricnik-Bay")
wrote:
> On 3/7/07, andyk <a...@commandprompt.com> wrote:
>
> > 1) try to use client software, which is not depends on libpq,
> > or
> > 2) divide your query into smaller parts,
> > or
> > 3) use software which parses long queries, separates them into single
> > SQL commands and then executes this commands one by one.
>
> 4) use Postgres on a Unix-type operating system...
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster

Thanks for the suggestions. I chose to divide the query into two
different sets of inserts. This seems to work.

Now I wonder why is it that I started getting WSANOBUFS errors from
Windows Socket System if this same setup with the same machines has
been operational for over a year? The query size did not change and
the number of connections to the server did not change. Is there any
good reason for this error to start showing up?

"Nik" <XLPizza@gmail.com> writes:

> Now I wonder why is it that I started getting WSANOBUFS errors from
> Windows Socket System if this same setup with the same machines has
> been operational for over a year? The query size did not change and
> the number of connections to the server did not change. Is there any
> good reason for this error to start showing up?

Did your Windows change?  (Updates, service packs, etc.)



--
Jorge Godoy      <jgodoy@gmail.com>

I am using machine which is exceeding more than 100 connection from my
application(iview-syslog server) to postgres. I have updated configuration
for postgres in postgres.conf for max connection. But then also it is giving
me the error as No buffer space available. And it is happening with only
iviewdb named database. Other databases are working well and good. After
searching on net i found the limitation is from windows itself. and i have
fixed the error with the following registry hack.

Regards,
Narendra Shah.


No buffer space available Fix

Editting the registry is not for beginners, if you don't know what you're
doing I suggest you don't try this, basically it's use at your own risk.

Anytime you want to edit the registry it is a good idea to back it up first.
For information on how to backup and restore the registry in all versions of
Windows click here.

If you are using Windows 95/98/Me follow these steps:

First step is to launch the registry editor. To do this go to Start, Run and
type regedit. In the left pane navigate to
HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\VxD\MSTCP once there,
look for an entry called MaxConnections. If it exists highlight it by
clicking it and then right click it and select modify. Increase the value
(recommended value is to double the current value).

If the MaxConnections entry does not exist you must create it. To do this,
right click in the right pane and choose new from the menu and select String
Value. Give it the name MaxConnections. Then right click it and select
modify and enter a value of 200.

Restart your computer, if all goes well then you fixed the problem, if not,
revert the changes by restoring the registry. (You may have to reboot to
safe mode to do this).

If you are running Windows NT/2000/XP follow these steps: First step is to
launch the registry editor. To do this go to Start, Run and type regedit. In
the left pane navigate to
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters once
there, you must create the entry TcpNumConnections. To do this, right click
in the right pane and choose new from the menu and select DWORD Value. Give
it the name TcpNumConnections. Then right click it and select modify and
enter a value of 200.

Restart your computer, if all goes well then you fixed the problem, if not,
revert the changes by restoring the registry. (You may have to reboot to
safe mode to do this).
--
View this message in context: http://www.nabble.com/No-buffer-space-available-tp9335358p25238999.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Hi Lennnin,

Thanks a lot for providing your kind help. But after tweaking registry, yet
problem not solved. On yesterday evening when i check again. Same problem
occured, what i have done is i have queried one table. Then after Database
again giving me the same error. Requesting you to please provide solution to
this. That table might contains record more than 5 lakh. And that table is
not indexed. So is that a problem ?

Regards,
Narendra Shah,
Cyberaom,
Elitecore Technolgies Ltd,
Ahmedabad



Lennin Caro wrote:
>
>
> IMHO you have to use pgpool to handle many connections
>
>
> Lennin Caro Pérez
>
> Usuario:GNU/LINUX
>
> PHP Developer
>
> PostgreSQL DBA
>
> Oracle DBA
>
> Linux  counter id 474393
>
>
>
>

--
View this message in context: http://www.nabble.com/No-buffer-space-available-tp9335358p25251894.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.