Re: JDBC driver's (non-)handling of InputStream:s

Peter Schuller wrote:

>>You could use LOs instead of bytea if you have some flexibility in your
>>schema; the LO interface should stream nicely. It's nastier to deal with
>>LOs on the JDBC side, though: you'll need to drop the driver into a
>>previous-version-compatibility mode (7.1?) or use the LO manager
>>directly. Also LOs don't play well with autocommit.
>
>
> Hmm, okay. So in versions greater than 7.1, I can not simply use a normal
> PreparedStatement and setBinaryStream(), as suggested in the documentation?
> Is this for some technical reason or is this method now deprecated?

It's a technical reason. Some background..

In 7.1, calling setBinaryStream()/setBytes()/etc (and the equivalent
ResultSet methods) would assume the field contained a Large Object OID;
the driver would talk to the LO API to handle retrieving/storing the
actual LO data as needed. This worked but had some odd quirks, since a
field containing a LO OID is not the same as a field containing the data
itself -- you had to be careful with transaction demarcation etc.

In 7.2+, calling those methods assumed the field was a bytea and the
data could be directly stored/retrieved as part of the query without a
separate operation via the LO API. 'bytea' is a much better match to
JDBC's LONGVARBINARY, so (at least to me) this change makes sense.

Since the driver doesn't know much about the context of a parameter in a
query, and has to pick one approach or the other at the point where
setBinaryStream() is called, the only way to switch behaviours was to
globally change the driver's behaviour by telling it "behave like you're
a 7.1 driver" (a 'compatible=7.1' URL parameter).

> I am only getting timeouts on postgresql.org at the moment, so perhaps this is
> explicitly stated in the docs, but can I safely assume that any usage of the
> Large Object Manager as part of a transactions whose queries are otherwise
> done through normal JDBC channels, will not cause any interference between
> the JDBC API and the LO manager? I.e., is it "safe" to intermix usage of
> large objects with normal statements through JDBC?

Yes, it's safe.

-O