[jboss-user] [jBPM] - Re: JBPM4 Migrating from Oracle to Postgres, Transaction Error on JBPM4_LOB
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.
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). 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. 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).
 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.
 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...
More information about the jboss-user