Re: bytea size limit?

Mmmh, well thanks guys. Only problem with changing to LOs is that I
already have data in production so changing the DB schema will be a
little complicated. I guess I could also contribute to the driver to
provide a streaming implementation. Do you know where that char[] is
in the code?

Sunday, April 11, 2004, 10:44:47 PM, you wrote:

OJ> Dave Cramer wrote:
>> Michael,
>>
>> that doesn't surprise me, as the postgresql driver currently buffers
>> that internally, so you end up with two buffers of 1400000 bytes, have a
>> look through the archives for out of memory errors.

OJ> It's worse that that, Jim..

OJ> From memory, we actually end up with a char[] of size 3*array length on
OJ> average (assuming 50% of the data needs escaping to a '\\nnn' form),
OJ> i.e. we need about 6 times the array's size in temporary storage (8.4mb
OJ> in this case).

>>> byte[] data = new byte[size];
>>>
>>> int id = Math.abs(new Random().nextInt());
>>>
>>> PreparedStatement stmt = c.prepareStatement(sql);
>>> stmt.setInt(1, id);
>>> stmt.setBinaryStream(2, new
>>> ByteArrayInputStream(data), data.length);

OJ> setBinaryStream ends up allocating a new byte array and reading into it,
OJ> then passing the result to setBytes. So you need a total of almost 10mb
OJ> of temporary storage to insert a 1.4mb bytearray. Yes, this sucks.

OJ> You'd be better off in this case (where the data is already in a byte
OJ> array) to call setBytes() directly. Once we have a streaming
OJ> implementation, though, calling setBinaryStream() will be better, as
OJ> setBytes() will have to take an immediate copy of the array to avoid
OJ> seeing later changes before the statement is executed and the data streamed.

OJ> If you can't throw memory at the problem, using LOs as Dave suggested
OJ> earlier is probably a better idea. The LO manager uses 4k blocks (IIRC)
OJ> when moving data to/from the backend so the memory overhead is much lower.

OJ> -O