Re: bytea size limit?

Michael Privat wrote:
> 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?

The char[] arises from the fact that the driver turns each parameter
into a string representation at the time setBinaryStream etc. is called.
It's this string representation that is big. Take a look at
AbstractJdbc1Statement.setBytes() and the call to PGbytea.toPGString().

To fix this the Right Way involves:

  - changing setBytes() and setBinaryStream() to store the parameter
value for later use, not turn it into a string immediately.
  - using the V3 extended query protocol (this requires a number of
other driver changes, as at a minimum the driver will need to split up
queries that contain multiple statements) to allow use of a separate
Bind message.
  - using a binary-format parameter in the Bind message to represent the
bytea field, and streaming from the byte[]/InputStream to the socket
directly when writing the parameter.

This is not a trivial piece of work unfortunately.

There may be a way to do a temporary fix that provides streaming without
the other work. For V2, it may be possible to stream while still using a
string representation of the bytea, as V2 queries are null-terminated
and we don't need to know the length in advance. I haven't looked into
this in detail.

It may be possible to do this for V3 too, as we can in theory predict
the length of the stringized parameter (necessary as V3 messages are
preceeded by a total message length field): the bytea string format is
predictable, and the driver is using a predetermined encoding
(unicode/utf8).

It'd be ugly, though..

-O