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,
Marco
[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
[
http://community.jboss.org/message/629596#629596]
Start a new discussion in jBPM at Community
[
http://community.jboss.org/choose-container!input.jspa?contentType=1&...]