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

[Kris Jurka <books(at)ejurka(dot)com>]

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