JPA, PostgreSQL and bytea vs. oid type

I started to work on a private project using JBoss Seam, Facelets, RichFaces (proven stack from Seam’s Booking demo :-)) with PostgreSQL 8.3 as a backing database – database I prefer for years for whatever reason. Hibernate is used as the JPA provider (packaged with Seam anyway) and the most recent JDBC 4 driver for PostgreSQL is used.

I added column:

picture bytea;

Somehow – after reading 8.4 Binary Data Types – I thought that bytea is the right guy for the job. Of course I had an entity with proper annotation and field:

private byte[] picture;

@Lob
@Basic(fetch = FetchType.LAZY)
public byte[] getPicture() {
    return picture;
}

public void setPicture(byte[] picture) {
    this.picture = picture;
}

What was my surprise when following exception occurred:

java.sql.SQLException: ERROR: column "picture"
  is of type bytea but expression is of type oid

Now – when I know the solution – this message is pretty clear. It would be in case different types were mentioned in the message. While I understood that “he” somehow dislikes bytea, I couldn’t understand what the hell “oid” (as an “object identifier”) has to do with it?

I started my investigation with Google in one hand and PostgreSQL in the other. While 8.16 Object Identifier Types was not very helpful – as in “what does have ‘unsigned four-byte integer’ to do with my byte array?!” – some googled pages convinced me that oid indeed is that type I should use – mostly one from postgresql.com domain. Truth is that I didn’t understand why and how that oid type manages to store my byte array. But I’ve tried it, it stores it, it reads it, no exception is thrown.

Now I’ve finished second part of my investigation and the results are:

• There is special Large Object facility in PostgreSQL and it’s based on using oid type that references actual large object stored in different table behind the scene.
• JDBC documentation also mentions that you can use oid instead of bytea.
• In that same chapter you can learn that bytea is supported since JDBC 7.2 version – that is not very long ago.
• I don’t know if JPA/Hibernate can use bytea, but – honestly – I don’t care while this works fine. And it does.

So if you use PostgreSQL along with JPA and bytea type is throwing an exception, try oid. I’m not sure how well JDBC supports bytea, I don’t know why JPA wants to use oid and if other JPA providers (Toplink?) can use bytea. It’s not straightforward to use oid as a type for binary data – because you can learn this only in JDBC documentation and not in the mainstream PostgreSQL manual (I don’t count that chapter 31 because you’ll likely never use it while creating your tables). If you google this with along with “JPA” or “@Lob” your results might not lead you to proper articles quickly enough… so hopefully this post might help it a bit.

If you have some facts to add, feel free to comment.

Edit 8-Feb-2013 – warning: My colleague informed me that after removing oid row from a table, large object itself stays in the DB if you use Hibernate (can’t tell anything about other providers). Reportedly, there is hot ongoing debate between Hibernate and PostgreSQL guys. Both may be right that it’s not their job to do the stuff and they don’t have enough information to delete the object, because large object itself can be shared and referenced from more tables and in fact only you, the programmer can know if the object can go away or not. Check the PostgreSQL documentation to find out how to remove the object, also check if vacuumlo doesn’t solve your problem.