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

From: Andrew Perepelytsya <aperepel(at)gmail(dot)com>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: BLOB is read into memory instead of streaming (bug?)
Date: 2008-04-30 19:27:50
Message-ID: df63a214-20da-498e-bf6c-f83468b7ac76@a70g2000hsh.googlegroups.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc

PostgreSQL 8.3.1
Win XP SP2
JDK 6
JDBC drivers: 8.3.603.<tried them all from this release>

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)
at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:
1306)
at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:
192)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:
451)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:
350)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:
254)
at org.mule.galaxy.JDBCBlobTest.testRetrieveBlob(JDBCBlobTest.java:
42)

The test method is trivial:

public void testRetrieveBlob() throws Exception
{
System.out.println("Retrieveing BLOB");

Class.forName("org.postgresql.Driver");
Connection conn =
DriverManager.getConnection("jdbc:postgresql://localhost/test",
"postgres", "postgres");

PreparedStatement ps = conn.prepareStatement("SELECT
binval_data FROM jackrabbit_x0020_core_binval WHERE binval_id = ?");
ps.setString(1, "some_file_name");
ResultSet rs = ps.executeQuery(); <<<<<< Fails here with OOME
assertTrue(rs.next());

InputStream is = rs.getBinaryStream(1);
assertTrue(is.available() > 0);

rs.close();
ps.close();
}

Upload through the driver was fine - got a 70MB file in without
problems (just can't get it out of db now).

The table structure is generated by a JCR implementation (Jackrabbit
1.4.1), here's a reverse script according to pgAdmin III:

CREATE TABLE jackrabbit_x0020_core_binval
(
binval_id character varying NOT NULL,
binval_data bytea NOT NULL
)
WITH (OIDS=FALSE);

I also noticed that a select blob statement works the same with
pgAdmin (reads it all into memory), but it could be the same
programming error/limitation.

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.

Any thoughts are more than welcome?

Thanks,
Andrew

Responses

Browse pgsql-jdbc by date

  From Date Subject
Next Message Kris Jurka 2008-04-30 23:34:12 Re: invalid message format and I/O error while comunicating with backend
Previous Message Robert Wimmer 2008-04-30 10:11:38 Re: insufficient data left in message II