Re: BLOB is read into memory instead of streaming (bug?)

On Wed, 30 Apr 2008, Andrew Perepelytsya wrote:

> I'm trying to stream a BLOB from the database, and expected to get it
> via rs.getBinaryStream(1), but the execution fails without reaching
> this point:
>
> org.postgresql.util.PSQLException: Ran out of memory retrieving query
> results.
> Exception: java.lang.OutOfMemoryError: Java heap space
> Stack Trace:
> java.lang.OutOfMemoryError: Java heap space
> at org.postgresql.core.PGStream.ReceiveTupleV3(PGStream.java:349)
>
> CREATE TABLE jackrabbit_x0020_core_binval
> (
> binval_id character varying NOT NULL,
> binval_data bytea NOT NULL
> )
> WITH (OIDS=FALSE);
>
>
> The expected behavior is to execute a statement and get a ref to the
> blob's stream, read it from there, which doesn't work yet
> unfortunately.
>

There are two methods to store binary data in pg and they have different
access methods and performance characteristics. Bytea data is expected to
be shorter and is returned in whole with a ResultSet by the server. For
larger data you want to use large objects which return a pointer (oid) to
the actual data which you can then stream from the server at will.

This page describes some of the differences between the two and
demonstrates using a pg specific api to access large objects, but
getBlob/setBlob will work just fine.

http://jdbc.postgresql.org/documentation/83/binary-data.html

Kris Jurka