Re: Workarounds for getBinaryStream returning ByteArrayInputStream on bytea

From: Radosław Smogura <rsmogura(at)softperience(dot)eu>
To: Александър Шопов <lists(at)kambanaria(dot)org>
Cc: <pgsql-jdbc(at)postgresql(dot)org>
Subject: Re: Workarounds for getBinaryStream returning ByteArrayInputStream on bytea
Date: 2010-11-26 12:05:30
Message-ID: d6544fb5a3293dfb4ac6586893ab3417@smogura-softworks.eu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-jdbc

Hi,

I would like to send few files for getBinaryStream(). So this will work
much like stream and will don't eat so much heap. I don't copy source
this_row[i] array, so I don't know how this will do with concur updates,
(original method doesn't make this when column is not bytea, too). I left
few comments if we should throw exception on broken streams in 8.4, or just
silence notify EOF.

One thing in the below code is to change to PSQLException.
Below is AbstractJdbc2ResultSet.getBinaryStream

public InputStream getBinaryStream(int columnIndex) throws SQLException
{
checkResultSet( columnIndex );
if (wasNullFlag)
return null;

if (connection.haveMinimumCompatibleVersion("7.2")) {
//Version 7.2 supports BinaryStream for all PG bytea type
//As the spec/javadoc for this method indicate this is to be
used for
//large binary values (i.e. LONGVARBINARY) PG doesn't have a
separate
//long binary datatype, but with toast the bytea datatype is
capable of
//handling very large values. Thus the implementation ends up
calling
//getBytes() since there is no current way to stream the value
from the server

//Copy of some logic from getBytes
//Version 7.2 supports the bytea datatype for byte arrays
if (fields[columnIndex - 1].getOID() == Oid.BYTEA) {
//TODO: Move to datacast in future
final byte[] bytes = this_row[columnIndex - 1];
// Starting with PG 9.0, a new hex format is supported
// that starts with "\x". Figure out which format we're
// dealing with here.
//
if (bytes.length < 2 || bytes[0] != '\\' || bytes[1] !=
'x') {
return new PGByteaTextInputStream_8_4(bytes,
(maxFieldSize > 0 &&
isColumnTrimmable(columnIndex)) ? maxFieldSize : Long.MAX_VALUE);
}else {
if (bytes.length % 2 == 1)
getExceptionFactory().createException(
GT.tr("Unexpected bytea result size, should be
even."),
PSQLState.DATA_ERROR);
return new PGByteaTextInputStream_9_0_1(bytes,
(maxFieldSize > 0 &&
isColumnTrimmable(columnIndex)) ? maxFieldSize : Long.MAX_VALUE);
}
}else {
return new ByteArrayInputStream(getBytes(columnIndex));
}
} else {
// In 7.1 Handle as BLOBS so return the LargeObject input
stream
if ( fields[columnIndex - 1].getOID() == Oid.OID)
{
LargeObjectManager lom = connection.getLargeObjectAPI();
LargeObject lob = lom.open(getLong(columnIndex));
return lob.getInputStream();
}
}
return null;
}

On Thu, 25 Nov 2010 00:53:31 +0200, Александър Шопов
<lists(at)kambanaria(dot)org>
wrote:
> В 16:04 -0600 на 24.11.2010 (ср), Radosław Smogura написа:
>> I see only two possibilities
>> 1. Decrease fetch size, e.g. to 1.
> Even if I do, bytea is potentially 1GB. Plus peaks in usage can still
> smash the heap.
> So refactoring to BLOBs is perhaps the only way out.
> Will the JDBC driver always present bytea InputStream as
> ByteArrayInputStream? No plans to change that? (even if there are, I
> will still have to refactor meanwhile).
> Perhaps this behaviour should be better communicated to DB schema
> designers.
> It seems to me from the Npgsql2.0.11 readme.txt that reading in chunks
> is provided for .Net.
> Is there need to perhaps make patches for this in the jdbc driver?
> Kind regards:
> al_shopov

--
----------
Radosław Smogura
http://www.softperience.eu

Attachment Content-Type Size
pgjdbc-patch-binary-stream.tgz application/octet-stream 1.6 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Robert Haas 2010-11-26 12:29:48 Re: contrib: auth_delay module
Previous Message Dimitri Fontaine 2010-11-26 11:30:37 Re: pg_execute_from_file review

Browse pgsql-jdbc by date

  From Date Subject
Next Message Kris Jurka 2010-11-26 15:25:01 Re: Workarounds for getBinaryStream returning ByteArrayInputStream on bytea
Previous Message Radosław Smogura 2010-11-26 09:01:24 Re: [JDBC] JDBC and Binary protocol error, for some statements