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

[deepti.tyagi]

Hi,

I am facing an issue on importing any package from JBPM-Engine admin console. It's always showing a message stating: Unable to import package(s) – null

On checking logs, I found that there is an exception being thrown every time I select a package and click on import.

org.hibernate.exception.GenericJDBCException: could not insert: [org.drools.persistence.info.SessionInfo]

Environment:- JBPM5.4, PostgreSQL 9.5, JBoss 6.1.0.Alpha1, Hibernate 4.0.0.Final

On debugging the drools-5.5.0.Final source jars, I found this error occurs in class SingleSessionCommandService at the highlighted line of code where it tries to persist SessionInfo object into postgresql db.

transactionOwner = txm.begin(); // transactionOwner value changed to true

registerRollbackSync();

persistenceContext.joinTransaction();

persistenceContext.persist( this.sessionInfo );

txm.commit( transactionOwner );

Also the standalone.xml datatasource configuration is as follows:-

<datasource jta="false" jndi-name="java:jboss/datasources/dsbpm" pool-name="dsbpm" enabled="true" use-java-context="true" use-ccm="true">

                    <connection-url>jdbc:postgresql://<server>:<port>/bpm</connection-url>

                    <driver>postgresql</driver>

                    <pool>

                        <min-pool-size>2</min-pool-size>

                        <max-pool-size>20</max-pool-size>

                        <prefill>false</prefill>

                        <use-strict-min>false</use-strict-min>

                        <flush-strategy>FailingConnectionOnly</flush-strategy>

                    </pool>

                    <security>

                        <user-name>username</user-name>

                        <password>password</password>

                    </security>

                    <validation>

                        <check-valid-connection-sql>SELECT 1</check-valid-connection-sql>

                        <validate-on-match>false</validate-on-match>

                        <background-validation>false</background-validation>

                    </validation>

                </datasource>

<drivers>

                    <driver name="postgresql" module="org.postgresql">

                        <driver-class>org.postgresql.Driver</driver-class>

                    </driver>

              </drivers>

I am attaching the logs and some debug time outputs to provide more detail on it.

I will really appreciate any help on this.

[marco.rietveld]

I would suggest, to start with, trying jBPM 5.5.

[deepti.tyagi]

Thank you Marco for the suggestion.

Just in case, we are not in state to immediately upgrade to 5.5, is there any alternative to solve this issue?

[marco.rietveld]

I'm almost certain that this bug has been fixed in 5.5.

Unfortunately, I don't know of any other workaround. The problem has to do with the fact that PostgreSQL's Large Object facility means that BLOB's are stored in 2 tables, as opposed to one (in other databases). The table that you would normally expect to to contain the BLOB contains a pointer to a the Large Object facility table, that contains the actual table.

Because 2 tables are involved instead of one, a transaction is required for both reading and writing (instead of simply writing). This is the cause of the exception.

I'm fairly certain that we fixed this in 5.x once we ran into this by adding a transaction around the the read operations. I think the exception is probably happening around (human) task operations? (Although, you're referencing the SSCS (SingleSessionCommandSession), so that would imply ksession operations).

The other possibility here is that you're managing transactions in your code, instead of letting jBPM deal with them. This is very hard to do correctly and could otherwise be the cause of your problem.