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&...]