In triaging a reported issue I ended up having to once again delve into investigating PostgreSQL BLOB suppport
. So for the sake of posterity and for the sake of my swiss cheese memory I decided to record my findings here.
By far the best resource I found on the subject was http://jdbc.postgresql.org/documentation/84/binary-data.html. Anyway, it sets the tone right from the get go:
PostgreSQL™ provides two distinct ways to store binary data.
Each way utilizes a different proprietary data type, oid or bytea:
To use the bytea data type you should simply use the getBytes(), setBytes(), getBinaryStream(), or setBinaryStream() methods. To use the Large Object functionality you can use either the LargeObject class provided by the PostgreSQL™ JDBC driver, or by using the getBLOB() and setBLOB() methods.
Unfortunately there is no effort to make this transparent to the developer. So as of this moment, bottom line is that to use Hibernate and PostgreSQL together for storing large binary data your best bet is to:
- use a consistent style for defining the column datatypes (aka pick either bytea or oid and use it consistently)
- set hibernate.jdbc.use_streams_for_binary appropriately based on which style you chose (false for oid, true for bytea)
Otherwise you'll need to set hibernate.jdbc.use_streams_for_binary for your most commonly used style and handle deviations property-by-property :(
