We've recently had to move from MySQL to PostgreSQL. I've been trying to get it to handle blobs. After some fiddling with the driver (to force it to use LOBs rather than BYTEA - not sure how I can pass a Properties object to the Postgres Connection yet...) I've managed to get a blob into Postgres via Hibernate. Hurrah! I can even get it out again using psql and doing a SELECT lo_export.

What I can't do is get the blob back out using Hibernate - namely running a query via Session.find(). Here's the exception:

java.io.IOException: org.postgresql.util.PSQLException: FastPath call returned ERROR: invalid large-object descriptor: 0

Googling around, this appears to be because there is no current transaction at the time of the call. From what I can tell, there should be: we're using a Hibernate connection pool (and expecting Hibernate to run with autocommit off) and calling Session.beginTransaction() to start a new transaction before the Query is run).

Has anyone come across this before?

This was indeed the problem. I had a DAO search method that was basically returning the results of the query and then closing the transaction. Because the blob data of the resulting objects wasn't being read until after the connection was closed, Postgres couldn't retrieve the blob data.

So I've taken the commit out of the DAO search method, but now I'm left with lots of open transactions, consuming processes on the server. Not sure how best to deal with this, so I've posted again...

Use an interceptor to hold a Session and Transaction open while a thread executes. The interceptor opens a Session and starts a transaction when a new (client) request comes in, and commits the Transaction (and closes the Session) before the response is send.

Use a ThreadLocal variable to do this. See the HibernateUtil in the Quickstart chapter of the documentation and search on the wiki for those keywords.

I'm pretty much doing this already, though not via an Interceptor. I have a HibernateSimpleTransactionManager that stores the session and transaction in a threadlocal. The client code is then responsible for calling start, commit or rollback on the transaction manager.

The problem is/was that the transaction was being controlled down at the DAO layer, but the blob was being streamed out of the object by a component above that. By the time that component got the object and tried to read the blob the transaction manager had already been told to close the transaction by the DAO.

It should be just a question of pushing the demarcation up to the component: the DAOs are written to only start a transaction if there isn't one already going.