Help

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:

  1. use a consistent style for defining the column datatypes (aka pick either bytea or oid and use it consistently)
  2. 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 :(

20 comments:
 
11. May 2010, 17:29 CET | Link
It has its reasons why postgresql splits this into bytea[] and oid.

ByteA is stored directly into the table, while Lobs/oids are stored on the harddisk and only a reference to them is stored in the table which gives serious performance improvements.

If PostgreSQL does not make this transparent, maybe hibernate could do this by adding a new annotation that specifies which type of large object the column is.

For the hibernate user it could then be transparent, which type he uses.
 
11. May 2010, 20:16 CET | Link
Dominik wrote on May 11, 2010 11:29:
It has its reasons why postgresql splits this into bytea[] and oid. ...

That's not a reason, thats a justification for the behavior. At the end of the day it should be irrelevant from the API (JDBC or other wise) which is being used under the covers. But PostgreSQL have chosen to handle it otherwise. AFAICT the only reason for this it to allow their users direct access to the oid value which is needed to cleanup the oid data because deleting the row containing the oid does not delete the blob data. Again, an implementation detail leaking into the API.

Dominik wrote on May 11, 2010 11:29:
If PostgreSQL does not make this transparent, maybe hibernate could do this by adding a new annotation that specifies which type of large object the column is. For the hibernate user it could then be transparent, which type he uses.

How is making a user attach an annotation to the property saying which style to use transparent? And besides we have this today... @Type

 
12. May 2010, 15:11 CET | Link
shane lee

So for bytea example is: persistence.xml

<property name="hibernate.jdbc.use_streams_for_binary" value="true"/>

persistence entity:

private byte[] pdfDocument;
@Column(name = "pdfDoc")
    @Basic(fetch = FetchType.LAZY)
    public byte[] getPdfDocument() {
        return pdfDocument;
    }
    
    public void setPdfDocument(byte[] pdfDocument) {
        this.pdfDocument = pdfDocument;
    }

Note:If you add @Lob annotation then hibernate will generate an oid.

 
12. May 2010, 19:44 CET | Link
shane lee wrote on May 12, 2010 09:11:
So for bytea example is: persistence.xml
<property name="hibernate.jdbc.use_streams_for_binary" value="true"/>
persistence entity:
private byte[] pdfDocument;
@Column(name = "pdfDoc")
    @Basic(fetch = FetchType.LAZY)
    public byte[] getPdfDocument() {
        return pdfDocument;
    }
    
    public void setPdfDocument(byte[] pdfDocument) {
        this.pdfDocument = pdfDocument;
    }
Note:If you add @Lob annotation then hibernate will generate an oid.

The thing you need to understand is that Hibernate needs to know about both types (java and sql). In fact its org.hibernate.type.Type is all about that. If you do not tell it which org.hibernate.type.Type to use it tries to make certain inferences based on the java type. For example, byte[] is (by default) interpreted as org.hibernate.type.BinaryType which handles byte[] and LONGVARBINARY; java.sql.Blob is a org.hibernate.type.BlobType which handles java.sql.Blob and BLOB.

However, BLOB and byte[] is also certainly a valid choice; its what I term a materialized blob and is handled by org.hibernate.type.MaterializedBlobType. This is where PostgreSQL starts getting difficult.

But yes, what you have above works if the table already exists and uses bytea or you are ok with Hibernate generating the table with the column as bytea.

 
17. May 2010, 07:17 CET | Link
Shane Lee

Hi Steve, I was just showing an example based on what you said for interested users using PostGres.

"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:

   1. use a consistent style for defining the column datatypes (aka pick either bytea or oid and use it consistently)
   2. set hibernate.jdbc.use_streams_for_binary appropriately based on which style you chose (false for oid, true for bytea)"
 
09. Sep 2010, 16:42 CET | Link
Justin
I don't understand why hibernate changed their type system and BROKE what was working in previous versions (with no fix).

Here is what hibernate _could_ do for a class annotated as such:

   // annotated object
   public class SomeObject {
     @Lob
     public byte[] getBigData() { return m_bigData; }
   }

Create a runtime proxy which is aware of the proper method to get the blob data (here I'm not aware of how hibernate actually does it, this is just a loose example to show how it could be done, I'm sure hibernate could do it much more efficiently)
      // generated proxy for annotated object (conceptual)
   public class Proxy_For_SomeObject {
     public byte[] getBigData() {
      if (getMetadata("SomeObject").isOid()) {
       return BlobFetcher.fetchBlob(m_bigData);
      }
      else {
       return m_bigData;
      }
     }
   }
    In this way, the first time the application reads the blob it actually fetches the content using whatever mechanism postgresql has (yes creating the 1-n selects problem), but hibernate could probably model this whole scenario by treating this scenario as a @OneToOne(lazy = true) relationship between the owning object and the blob.
 
16. Sep 2010, 15:45 CET | Link

I did not break anything. Yes you may have to do a little bit more now to get LOBs working on PostgreSQL but I already outlined what is required above.

So your suggestion is that we create specialized proxies to wrap entities to work around a PostgreSQL limitation in regards to how it treats certain column types? Am I understanding that correctly?

 
17. Sep 2010, 17:29 CET | Link
Justin

This is obviously a very contention thing: people point the finger at hibernate, hibernate points the finger at postgresql. It seems to me like you are trying to push pgsql developers to provide reasonable blob support and use the JDBC API in a standard way (understandable).

 
17. Sep 2010, 17:30 CET | Link
Justin
Perhaps I just don't understand what the workaround is. What worked perfectly in hibernate 3.1 no longer does in 3.5, what would you call it?

The whole point of annotations (and hibernate) is to make the database mapping part transparent to the developer and provide a simple portable interface.
Portability is probably the #1 or #2 reason most people use hibernate -- I don't see how to achieve this.

It was merely a suggestion to show it could be done, not a proposed implementation. What is proposed is indeed terrible.
Actually after reading a bit more about it I'm still confused as to why you just don't map @Lob + byte[] as bytea (which works on both postgresql and oracle).

Do people actually uses postgresql's oid support with hibernate?, I can't see that they would since when you retrieve the blob data you only get back the oid, which you then have to feed to some totally PGSQL specific API to retrieve?
 
17. Sep 2010, 19:26 CET | Link
Justin wrote on Sep 17, 2010 11:30:
Do people actually uses postgresql's oid support with hibernate?, I can't see that they would since when you retrieve the blob data you only get back the oid, which you then have to feed to some totally PGSQL specific API to retrieve?

See thats just not true. PostgreSQL's OID comes closest to a BLOB. And in fact thats how you treat it via its JDBC driver. If you call getBlob() on a ResultSet on an OID column you get the BLOB data. If you call getBytes() etc on the same then, yes you get the OID value itself. See the difficulty?

byte[] most naturally maps to the JDBC type LONGVARBINARY, which is the precursor to BLOBs. In JDBC these are handled by getBytes/setBytes or getBinaryStream/setBinaryStream, which is how PostgreSQL suggests you handle bytea oddly enough.

java.sql.Blob maps to the JDBC type BLOB, which are handled by getBlob/setBlob, which again oddly enough, PostegreSQL says is how you should deal with OIDs.

See the stars aligning? ;)

The difference is that most drivers allow you to treat BLOB data as bytes and streams as well. PostgreSQL simply does not because it needs that distinction to deal with oid versus bytea. I say needs because really the only reason it needs the distinction is so that you can actually get at the oid value itself (not the data).

So, with that in mind...

private byte[] myData;

is interpreted as LONGVARCHAR by default, which on PostgreSQL maps to bytea via the PostgreSQLDialect.

@Lob private byte[] myData;

however is interpreted as BLOB by default, which on PostgreSQL maps to oid via the PostgreSQLDialect.

If thats not what you want you have a number of options:

  1. Override the type mapping for byte[] in the Hibernate TypeRegistry. See http://docs.jboss.org/hibernate/core/3.6/reference/en-US/html/types.html#types-registry
  2. Supply a custom Dialect (extending from PostgreSQLDialect) that simply maps java.sql.Types.BLOB to bytea if thats what you want.
  3. Explicitly name the type mapping you want to use for that property via the org.hibernate.annotations.Type annotation
Justin wrote on Sep 17, 2010 11:30:
The whole point of annotations (and hibernate) is to make the database mapping part transparent to the developer and provide a simple portable interface. Portability is probably the #1 or #2 reason most people use hibernate -- I don't see how to achieve this.

Right, and what we have now is by far the most portable solution.

Justin wrote on Sep 17, 2010 11:30:
Actually after reading a bit more about it I'm still confused as to why you just don't map @Lob + byte[] as bytea (which works on both postgresql and oracle).

I've already explained that above.

 
17. Sep 2010, 21:34 CET | Link
Justin
Steve Ebersole wrote on Sep 17, 2010 13:26:
byte[] most naturally maps to the JDBC type LONGVARBINARY, which is the precursor to BLOBs. In JDBC these are handled by getBytes/setBytes or getBinaryStream/setBinaryStream, which is how PostgreSQL suggests you handle bytea oddly enough.

It seems like there are (at least) two distinct use cases for blobs
1) You want the blob data in the table (so you can just fetch join it) and must be careful not to access your domain object when you don't need it.
2) You don't want blob data in the table since you access the object that contains it way more than the blob (but sometimes you might need it).

In case 1 you want bytea (my case) and you should use byte[] in your domain objects
In case 2 you want oid and you should use Blob in on your domain objects.

In either case, I still can't imagine a situation where the application developer would want the OID returned instead of the actual bytes (especially when using JPA). Perhaps if I read all the hibernate source, and all the postgresql JDBC driver source, and the JDBC spec this might make sense to me.

 
18. Sep 2010, 03:31 CET | Link

Well there is a 3rd case you are missing. You have BLOB in the database but you want byte[] in your application. Thats exactly what

@Lob byte[] myData;
is!

 
20. Sep 2010, 16:50 CET | Link
Justin

After tracing things through in the debugger, it seems like MaterializedBlobType does not work on postgres no matter what setting is used for jdbc.use-streams. As you say, postgres drivers interpret JDBC.setStream() and JDBC.setBytes() both as intended to work with bytea, the logic in MaterializedBlobType.set() is thus might just have been JDBC.setBytes(). Also, JDBC.setNull() is interpreted by postgres as an OID, and so if your column type is bytea, MaterializedBlobType does not work even if you change your table column.

As far as I can tell, the PostgreSQLDialect (as of 3.5.5) needs to change not to use MaterializedBlobType until the postgres team changes their driver (which does not seem to have happened in the last 6 years). As per the options you listed, I am looking in to (1), (2) does not work due to (I think) the way the jdbc driver interprets setNull; (3) is not an option since I want my code to be portable.

I had started a bounty on stack overflow to try and get a solution to this, there is more info there if you are interested. http://stackoverflow.com/questions/3677380/proper-hibernate-annotation-for-byte

 
09. May 2014, 09:48 CET | Link

This is the 2014 FIFA World Cup in Brazil, the official moment of glory Hublot replica watches watch ! This is the first time in the history of Hublot watch blockbuster launch Big Bang Unico Dual Retrograde timer (Bi-Retrograde Chrono) - the first dual- equipped central chronograph movement Retrograde watch. While this new movement is unique Hublot Hublot engineers and watchmakers to the football world as a tribute ! But also to celebrate the Hublot watch became Brazil's 2014 FIFA World Cup is the official timekeeper and official watch specially build ! Has been equipped with self-produced movement UNICO Swiss replica watches series with stunning craftsmanship unique watchmaking industry . Dial side of the integrated column-wheel and dual- clutch design standards become pioneer stone UNICO watch family , but also demonstrate superior flexibility UNICO movement . After 18 months of painstaking research and development and testing , the new self-winding movement HUB1260 production debut - composed of 385 parts and has a central chronograph features a double retrograde chronograph movement ( vibration frequency of 4 Hz , 28,800 / each h ) . Hublot watch is also patent-pending technology for the While this movement . It is currently in the watchmaking industry , the same type of wrist replica Hublot watch that uses only one movement technology. This is the first launch of dual Hublot watch Retrograde Chronograph ( pointer can instantly zero ) . When you press the button chronograph , chronograph second hand and minute hand from left to right at 45 minutes showed a circular track start time , which is half of the game of football time . Another area increased by 15 minute timer , so that the whole time time of 60 minutes. Table with two chronograph buttons on both sides of the crown , representing the timing to start, stop and reset . Two o'clock position of the button will also be used to control the timing of the four patterns of timing : the first half, intermission , the end of the second half and the game , and through a window located at the 12 o'clock position clear display .

 
15. Jun 2014, 08:08 CET | Link
hyjs

A artist Replica Handbags as allotment of the Women's shirts and dresses for animal drillmaster purses aperture for a baby bulk of cash, again you will absolutely get the adventitious to acquaintance the architect Bargain Authentic Louis Vuitton Cheap Replica Handbags air bottomward bombs over pockmarked jungles, has confused far above the Vietnamese actuality alarm the American war Drillmaster Aperture You are not alone they may aswell crave a abode to analyze to accumulate humans in cases area the ancestors destination, addition abundant arch on over to the assets of a lot added to get anybody calm to survive is ideal for blockage Dior replica handbags with a minumum of a academy abode room.

 
21. Jun 2014, 15:30 CET | Link

Exchange rates calculator may seem simple on the surface, but its easy to confuse for those without much memory of mathematics. While converting $100 to foreign currency when traveling isnt a big deal, converting exchange rate calculator An Exchange rate converter is software code that is designed to convert one currency into another in order to check its corresponding value. The code is generally a part of a web site or it forms a mobile app and it is based on exchange rate converter

 
07. Jul 2014, 09:01 CET | Link

In 1997 he joined replica Louis Vuitton outlet Handbags (Louis Vuitton’s) Marc works in preparation for the first quarter, the brand does not have any clothes can provide a reference for him, he had to start from scratch from scratch. He kept asking myself, “I should be designed in the end what kind of ralph lauren outlet australia clothes? People also want to see what kind of longchamp outlet clothes? Maybe I should not have to design the clothes they want to see. Luxuries that what is it? “In this constantly self-torture and self overthrow the process, he decided the first quarter of Cheap Louis Vuitton online Fall Winter works using only black and white and gray colors, without any brand logo. This does not mean that Ma Ying-jeou and gucci replica completely abandoned the long history of the brand essence of the brand, he explained that the inspiration comes from Louis Vuitton’s first quarter from the beginning of the 1880s produced a gray car trunk, this is a real Louis Vuitton wealth, he hopes to pursue such a fusion of quality among the works in the first quarter. Like every piece of clothing lining the hermes outlet online trunk lining, as refined and delicate. In fact, the first quarter is that the former works to meet you, Ma Ying-jeou and his design team would like LVMH CEO Bernard Arnault Group return to their creative inspiration and design process. They do not have furniture in a hotel room, hotel renderings plastered walls, sitting on the floor of the hotel and Bernard discussion.

 
29. Sep 2014, 04:24 CET | Link
jenny bolton  | backlink13(AT)yahoo.com

It's really nice and meanful. it's really cool blog. Linking is very useful thing.you have really helped lots of people who visit blog and provide them usefull information. how to grow bigger breast in a week

 
20. Oct 2014, 11:25 CET | Link
miel

This is a great article thanks for sharing this informative information. HD mxf files conversion software mxf converter pdf conversion to Office pdf to word r

 
07. Nov 2014, 06:41 CET | Link
nupisonontergil | nupisonontergil(AT)hotmail.com

Speaking of typically the strap, you might realize that all the Hublot features little metal wedges inside secure where they meet the case. They're there just for comfort, and to prevent the actual wrist strap from pulling down too much together with looking bad replica hublot big bang. Equally, lugless case tend to help you wear smaller. Lugs include a lot to how large is a case, and without them perhaps even larger watches appear slighter. Even more, families often like the structure of a watch as their lugs extend towards end of their Hublot, and with lugless conditions that often isn't conceivable. One other consideration is the width from the strap or bracelet again. In order for a watch to undertake a beefier feel the strap has to be wide. This is often twice as critical on lugless Hublot designs because concept can make still wide straps look narrow given the abrupt move from case to strap.