1 Reply Latest reply on Sep 30, 2011 11:36 AM by Marco Rietveld

    JBPM4 Migrating from Oracle to Postgres,  Transaction Error on JBPM4_LOB

    Jeff Maas Newbie

      I have inherited an enterprise service bus style application (MULE,JBPM4,Hibernate,Spring) and have been tasked with migrating the application from Oracle to Postgres.  Much of this has gone without a hitch however I have run into a problem that I beleve others have seen before.  Specifically Postgres8.4 does not appear to like auto-commits on OID -> LOB,  which appears to be key with how JBPM4_LOB works.


      [http-80-2] [org.hibernate.util.JDBCExceptionReporter] Large Objects may not be used in auto-commit mode.


      Root Exception stack trace:

      org.postgresql.util.PSQLException: Large Objects may not be used in auto-commit mode.

              at org.postgresql.largeobject.LargeObjectManager.open(LargeObjectManager.java:200)

              at org.postgresql.largeobject.LargeObjectManager.open(LargeObjectManager.java:172)

              at org.postgresql.jdbc2.AbstractJdbc2BlobClob.<init>(AbstractJdbc2BlobClob.java:47)

              at org.postgresql.jdbc2.AbstractJdbc2Blob.<init>(AbstractJdbc2Blob.java:21)

              at org.postgresql.jdbc3.AbstractJdbc3Blob.<init>(AbstractJdbc3Blob.java:19)

              at org.postgresql.jdbc4.AbstractJdbc4Blob.<init>(AbstractJdbc4Blob.java:20)

              at org.postgresql.jdbc4.Jdbc4Blob.<init>(Jdbc4Blob.java:20)

              at org.postgresql.jdbc4.Jdbc4ResultSet.getBlob(Jdbc4ResultSet.java:52)

              at org.postgresql.jdbc2.AbstractJdbc2ResultSet.getBlob(AbstractJdbc2ResultSet.java:335)

              at com.mchange.v2.c3p0.impl.NewProxyResultSet.getBlob(NewProxyResultSet.java:285)

              at org.hibernate.type.BlobType.get(BlobType.java:80)


      Admittedly I'm a bit out of my element,  I've tried various forum searched and googles but haven't found a beginners guide to a none beginneres topic.  It would appear I must add a transaction around JBPM4_LOB even though in the context that it is used it is absolutely reaad only.


      Nowehere else do I use BLOB's and right now I've got the various jbpm4 libraries (maven) on my classpath.  But I'm at a loss on how to deal with this issue surrounding auto-commit.  I've tried various forms of turning auto-commit off at the hibernate level but to no avail.  Previous seraches in the forum suggest others have had this issue but I'm not sure if anyone has recieved an answer.  Any help or sympathy is greatly appreciated.





        • 1. Re: JBPM4 Migrating from Oracle to Postgres,  Transaction Error on JBPM4_LOB
          Marco Rietveld Novice

          I have bad news, Jeff..

          (Well, sort of.. there may be hope, see the end of the message).


          The problem you're running into with jbpm 4 is that a database operation is being attempted with an object that, indeed, has a @LOB field -- which Postgresql handles with it's super cool (yay.. :/ ) "Large Object" facility[1].


          See http://jdbc.postgresql.org/documentation/84/binary-data.html


          The important sentence on that page is the following:

          You must access Large Objects within an SQL transaction block. You can start a transaction block by calling setAutoCommit(false). 


          And somewhere deep in the jbpm 4 code, that's exactly what's not happening: jbpm 4 is creating, modifying or accessing an entity with a LOB field "outside" of a transaction. The code doesn't even have to modify the object/entity containing a LOB: even retrieving an entity with a LOB field  will cause this problem.


          The reason I happen to know about this is because I've been looking at this issue in jbpm 5 (with PostgreSQL, which is the only DB that has this)[2]. In jBPM 5, we're close to fixing the code so that it works. It's actually not that hard, I just have to be careful I don't break anything else while fixing it.   :/


          Short of modifying the code in jBPM 4 yourself (it is open source!), I don't think there's a solution for this.


          Wait, I take that back -- one of the possible solutions I looked into was modifying the mapping of the objects in question so that the LOB was stored as a byte array and not using the "Large Object" facility[1]. To tell the truth, though, I unfortunately just deleted the (git) stash and branch containing that stuff this week, after having not looked at it for a month. In short, I'm only about 85% sure that what I'm suggesting is possible -- and can't remember all the details of the solution. 


          [Oh yeah, now I remember -- the byte [] field in PostgreSQL is limited (wrt to size), and while the limit is pretty high, because of.. how jBPM 5 is put together, it wasn't the better solution. I don't think that jBPM 4 will have as large @Lob/byte [] fields, but I would check the maximum size of the byte [] fields in your Oracle jbpm 4 database. If the max size of the @Lob/byte [] field in the Oracle db is larger than the max allowable size of the byte [] field in PostgreSQL, then at least you have a good reason that upper management will understand when you tell them why you can't migrate the system. ]


          I'm not at all unfamilar with jbpm 4, but the trick is the following:

          • You need to have access (be able to modify) the persistence.xml in order to do this.
            • If necessary, you can replace the persistence.xml in the appropriate jbpm 4 jar.
          • You can override the mappings that are present in the classes:
            • You will have to go through all the jbpm 4 code and find which entities these are.
            • Then you will have write entity-mapping xml files for these entities.
              • Make sure to include the xml-mapping-metadata-complete element in order to disable the anno's (including @Lob) in the java files.
              • This make take some trial an error: I use eclipse's debugging capabilities with a good unit test for this type of thing.
              • There are probably some other descriptions of how to do this, but see Pro JPA 2, pp. 373-375 for the overriding anno's and pp. 385-398 for some basic entity-mapping xml info.
                • The order of elements is (bizarrely) important: make sure to verify your mapping (and the order of your xml elements) agains orm_1_0.xsd (or whichever schema you're using.)
          • The mapping files you write for each entity must be referred to in your persistence.xml and if necessary, inserted in the appropriate jbpm 4 jar (see first bullet).



          Good luck,



          [1] The Large Object facility stores an id in the actual field of the entity, which then refers to a row/record in an another table where the actual byte data is stored -- hence the need for transactions since multiple tables are involved.


          [2] Yes, the "Large Object" facility is unique -- unique like the way we made jokes about "special" people in middle school. ;D That said, there are actually worse databases out there with regards to (XA) transactions -- worse commercial databases, that make me Sy.. , I mean sigh. /:)