PSQLException: Large Objects may not be used in auto-commit mode

Question

I am using WildFly 10, Java EE, JPA, and Hibernate. Recently I migrated my application from MySQL to PostgreSQL. While using MySQL, I would store images in my entities using:

@Lob
@Basic(fetch = FetchType.LAZY)
private byte[] image;

This worked great, and MySQL used a LONGBLOB to store the data.

After switching to PostgreSQL, the column type is OID, and I receive this error when persisting the image:

Caused by: org.postgresql.util.PSQLException: Large Objects may not be used in auto-commit mode.
at org.postgresql.largeobject.LargeObjectManager.createLO(LargeObjectManager.java:308)
at org.postgresql.largeobject.LargeObjectManager.createLO(LargeObjectManager.java:296)
at org.postgresql.jdbc.PgPreparedStatement.createBlob(PgPreparedStatement.java:1202)
at org.postgresql.jdbc.PgPreparedStatement.setBlob(PgPreparedStatement.java:1243)
at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.setBlob(WrappedPreparedStatement.java:1157)
at org.hibernate.type.descriptor.sql.BlobTypeDescriptor$4$1.doBind(BlobTypeDescriptor.java:112)
at org.hibernate.type.descriptor.sql.BasicBinder.bind(BasicBinder.java:73)
at org.hibernate.type.AbstractStandardBasicType.nullSafeSet(AbstractStandardBasicType.java:257)
at org.hibernate.type.AbstractStandardBasicType.nullSafeSet(AbstractStandardBasicType.java:252)
at org.hibernate.type.AbstractSingleColumnStandardBasicType.nullSafeSet(AbstractSingleColumnStandardBasicType.java:39)
at org.hibernate.persister.entity.AbstractEntityPersister.dehydrate(AbstractEntityPersister.java:2598)
at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2883)
... 131 more

I am inserting in this way:

@PersistenceContext
EntityManager entityManager;

...

//Simple insert method...
this.entityManager.persist(entity);

And this is my persistence.xml:

<persistence version="2.1" xmlns="http://xmlns.jcp.org/xml/ns/persistence"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/persistence http://xmlns.jcp.org/xml/ns/persistence/persistence_2_1.xsd">
    <persistence-unit name="MyApp" transaction-type="JTA">
        <jta-data-source>java:jboss/datasources/PostgreSQLDS</jta-data-source>
        <shared-cache-mode>DISABLE_SELECTIVE</shared-cache-mode>
        <properties>
            <property name="hibernate.enable_lazy_load_no_trans" value="true"/>
            <property name="hibernate.cache.use_second_level_cache"
                      value="true"/>
            <property name="hibernate.cache.use_query_cache" value="true"/>
        </properties>
    </persistence-unit>
</persistence>

I have looked at multiple other questions regarding this exception, however I have been unable to solve the problem.

One suggestion was to create an entity solely for storing the data and use a @OneToOne relationship with it so that the entity storing data could be eagerly fetched, however this did not change the results in any way.

Another suggestion was to disable Hibernate's auto-commit with

<property name="hibernate.connection.autocommit" value="false"/>

...which also did nothing.

I am completely lost as to why this will not work. Seeming as how this does not appear to be a very common exception, I feel like I am approaching the task of storing image data in the completely wrong way. What can I do to fix this, or how should I be storing the data?

Answer 1

I cannot tell you how this is done in Hibernate, but opening and reading/writing a large object have to happen within the same database transaction.

Disabling autocommit mode should do the trick, maybe you did something wrong.

But may I suggest that you do not use large objects at all?
Usually it is much easier to use the bytea PostgreSQL data type, which can contain data up to 1GB of size. Unless you store and retrieve the data in chunks, large objects don't offer any advantage, and I doubt that you can fully exploit large object functionality with an ORM anyway.

Answer 2

I had the same problem and resolved it through a change in my JBOSS configuration file (standalone.xml or domain.xml).

Analysing the problem first I saw that auto commit was still true by logging

entityManager.unwrap(SessionImpl.class).connection().getAutoCommit()

I tried setting it to false with

entityManager.unwrap(SessionImpl.class).connection().setAutoCommit(false)

but this resulted in the exception "You cannot set autocommit during a managed transaction".

Researching for the exception message I found this article that explains why auto commit was always true.

We were using a datasource configured like this:

<datasource jta="true"  . . . >

The problem is that in a JTA datasource the auto-commit setting is always true and it can not be changed. I changed it to an xa-datasource and the problem did not occur anymore.

<xa-datasource . . . .>

Also em.unwrap(SessionImpl.class).connection().getAutoCommit() finally returned false.

To make it work I also had to set the parameter max_prepared_transaction to 500 in postgres.conf in my Postgres database to allow multiple database connection/connection pooling:

max_prepared_transactions = 500     # zero disables the feature