The following bug has been logged online:

Bug reference: 2305
Logged by: H. Guijt
Email address: hg@terma.com
PostgreSQL version: 8.0.3
Operating system: Windows 2000
Description: "No bufferspace available" error on large insert
Details:

We are using PostgreSQL 8.0.3 on Windows 2000, and using pgsql to insert a
series of records into a database. The table into which we are inserting has
a string column, two integer columns, and two BYTEA columns.

Upon issuing the insert statement I receive the following error message:

"could not send data to server: No buffer space available
(0x00002747/10055)"

I suspect that this happens because we are inserting a lot of data: the
first BYTEA column is about 1.8MB, while the second is about 0.5MB.
However...

- The same operation completes without problems when running on other
machines (which are all running Windows XP and the same Postgres version).

- We have inserted even longer records (10MB+) on this machine without
problems.

- This insert is part of a larger group of inserts; the total group size is
the largest we have ever tried on this machine.

- The problem can be reliably reproduced.

- The machine has 384MB of memory. It gives no indication of being starved
for memory during the operation (responsiveness of the system is not
compromised).

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

....
> Upon issuing the insert statement I receive the following error message:
>
> "could not send data to server: No buffer space available
> (0x00002747/10055)"
>
> I suspect that this happens because we are inserting a lot of data: the
> first BYTEA column is about 1.8MB, while the second is about 0.5MB.
> However...
>
> - The same operation completes without problems when running on other
> machines (which are all running Windows XP and the same Postgres version).
....

are the various tuning parameters in $PGDATA/postgresql.conf set the
same on these various systems ?

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

>> Upon issuing the insert statement I receive the following error message:
>> "could not send data to server: No buffer space available
>> (0x00002747/10055)"
>>
>> I suspect that this happens because we are inserting a lot of data: the
>> first BYTEA column is about 1.8MB, while the second is about 0.5MB.
>> However...
>>
>> - The same operation completes without problems when running on other
>> machines (which are all running Windows XP and the same Postgres
>> version).
> ...
>
> are the various tuning parameters in $PGDATA/postgresql.conf set the same
> on these various systems ?

Yes. We haven't touched these parameters; all the copies of Postgres are
installed with their default parameters.

I have also been able to try the same operation on another Windows 2000
machine and it completed without problems, so we know it isn't the OS perse.


Regards,

Hans Guijt

TERMA B.V.
Schuttersveld 9
NL-2316 XG, Leiden
The Netherlands
Phone: +31 (0)71 52 40 835
Telefax: +31 (0)71 51 43 277
E-mail: hg@terma.com


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Hi,


I've investigated the problem further, and here are my findings. I believe
this will be enough to allow you to provide a permanent fix to this problem.

To recap: we get an unusual error message from Postgres when storing a lot
of data (hundreds of megabytes) on a machine that is running Windows 2000
and doesn't have all that much memory (384MB).

What happens is this: apparently, when both the client and the server of a
TCP connection are on the same machine, Windows (at least, this version of
it) will allocate as much memory as it needs to hold the data. Since my
client is writing much faster than Postgres can store the data, that buffer
grows until memory runs out. At that point, instead of returning the normal
error code for "out of memory", Windows returns an unusual and non-standard
error message.

So:

- The buffer actually used by Windows is much larger than the one specified
by TCP/IP - I'm guessing, for such a local connection, it is simply made as
large as it needs to be to hold all the data.

- If there is too much data a non-standard error code is returned.

I solved the problem by simply waiting for a few seconds when I get back the
"no buffer space available" message from Postgres, and retrying the command.
This gives Postgres time to do its work, and allows the buffer to shrink to
acceptable proportions. Obviously it would be appreciated if Postgres
actually did that for me ;-)

To reproduce, I simply allocate most of the memory available on the machine
(be sure to force it to be committed by writing to it), and then send a
large number of large inserts. This causes the buffer to overflow, thus
triggering the weird error.

I hope this helps in solving this issue. If you have any further questions,
please let me know.


Regards,

Hans Guijt

TERMA B.V.
Schuttersveld 9
NL-2316 XG, Leiden
The Netherlands
Phone: +31 (0)71 52 40 835
Telefax: +31 (0)71 51 43 277
E-mail: hg@terma.com


----- Original Message -----
From: "John R Pierce" <pierce@hogranch.com>
To: "H. Guijt" <hg@terma.com>; <pgsql-bugs@postgresql.org>
Sent: Wednesday, March 08, 2006 10:35 PM
Subject: Re: [BUGS] BUG #2305: "No bufferspace available" error on large
insert


> ...
>> Upon issuing the insert statement I receive the following error message:
>> "could not send data to server: No buffer space available
>> (0x00002747/10055)"
>>
>> I suspect that this happens because we are inserting a lot of data: the
>> first BYTEA column is about 1.8MB, while the second is about 0.5MB.
>> However...
>>
>> - The same operation completes without problems when running on other
>> machines (which are all running Windows XP and the same Postgres
>> version).
> ...
>
> are the various tuning parameters in $PGDATA/postgresql.conf set the same
> on these various systems ?
>


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