Re: Inserting large BLOBs via JDBC - OutOfMemoryError

>PS. It should also be possible to not prebuild the entire sql statement
>in memory (which includes the entire escaped binary value), but to
>stream the value back to the server. Thus instead of building the
>following String in memory:
>insert into table foo (bar, baz, boo) values (1000, '\123\432\543\567...
>continuing on for possibly megabytes...', 'boo value')
>The driver should be able to just send back the pieces and stream the
>bytea value from the inputstream:
>send -> "insert int table foo (bar, baz, boo) values("
>send -> 1000 (value for column bar)
>send -> ", "
>send -> (value for column baz by reading from the input stream here,
>encoding the results byte by byte and sending them)
>send -> ", "
>send -> "'boo value'" (value for column boo)
>send -> ")"

of course the prebuilding of the statement is the root of all troubles. with
all the escaping which has to be done, the arrays will always build up in
memory. The organization of the source is pretty good in terms of oop, I think,
because the responsibilites are pretty simple to understand. however, this
approach is only workable for regular types (string, int etc.) or small blobs.

The "perfect" way would be to stream the BLOB (which is passed as a stream
to setBinaryStream) directly to the db server. escaping could be done by
nesting streams. in this case, no arrays would be needed at all. While being
probably the best way, this would imply some sort of big overhaul of the code.

>I am not sure how much work this will be, but it is probably the correct
>long term solution. Any volunteers for looking into this and submitting
>a patch?

I would be interested in doing/helping this, but as I am not familiar with
the PostgreSQL JDBC source code, I'd have to work together with somebody who
really knows what's going on.

--
GMX - Die Kommunikationsplattform im Internet.
http://www.gmx.net