Re: bytea size limit?

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.

It's worse that that, Jim..

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

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

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

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

-O