6

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?

Community
  • 1
  • 1
Mitch Talmadge
  • 4,638
  • 3
  • 26
  • 44
  • 1
    "I feel like I am approaching the task of storing image data in the completely wrong way." correct. Images should not be in the database at all – e4c5 Jul 28 '16 at 22:57
  • @e4c5 In my case, I don't see another option. This application will be scaled horizontally across multiple different WildFly installations. If I store the images on the local disk, it will not be accessible by all users. The only way I know how to combat this is by using the database. The images are no larger than 1MB each. – Mitch Talmadge Jul 28 '16 at 23:49
  • 1
    You don't have to store on local disk. You can store on S3, Google files or any number of CDNs. You will get a huge performance boost – e4c5 Jul 29 '16 at 00:32
  • "to create an entity solely for storing the data and use a @OneToOne relationship" that is a commonly suggested work around to get a form of "property" level lazy loading working in Hibernate. If you are using JPA you can try a different JPA implementation that supports lazy proper lazy loading – Volksman Oct 24 '17 at 01:11
  • Does this answer your question? [Large Objects may not be used in auto-commit mode](https://stackoverflow.com/questions/3164072/large-objects-may-not-be-used-in-auto-commit-mode) – Vadzim Dec 26 '21 at 21:51

2 Answers2

4

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.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • 1
    Although this doesn't solve the exception, it is currently the easiest alternative solution for me at the moment. Using `bytea` and removing the `@Lob` annotation is working well. – Mitch Talmadge Jul 30 '16 at 01:28
1

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
Stephan Berg
  • 124
  • 1
  • 3