[jboss-user] [jBPM] - Re: JBPM4 Migrating from Oracle to Postgres, Transaction Error on JBPM4_LOB

Marco Rietveld do-not-reply at jboss.com
Fri Sep 30 11:36:40 EDT 2011

Marco Rietveld [http://community.jboss.org/people/marco.rietveld] created the discussion

"Re: JBPM4 Migrating from Oracle to Postgres,  Transaction Error on JBPM4_LOB"

To view the discussion, visit: http://community.jboss.org/message/629596#629596

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 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. /:)

Reply to this message by going to Community

Start a new discussion in jBPM at Community

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.jboss.org/pipermail/jboss-user/attachments/20110930/ef1e27f7/attachment-0001.html 

More information about the jboss-user mailing list