Thread: BUG #2305: "No bufferspace available" error on large insert
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).
... > 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 ?
>> 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
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 ? >