Large Objects may not be used in auto-commit mode

Question

I have a Spring application which uses Hibernate on a PostgreSQL database. I'm trying to store files in a table of the database. It seems it stores the row with the file (I just use persist method on EntityManager), but when the object is loaded from the database I get the following exception:

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

To load the data I'm using a MultipartFile transient atribute and in its setter I'm setting the information I want to persist (byte[], fileName, size). The entity I'm persisting looks like this one (I've ommitted the rest of getters/setters):

@Entity
@Table(name="myobjects")
public class MyClass {

    @Id
    @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="sequence")
    @SequenceGenerator(name="sequence", sequenceName="myobjects_pk_seq", allocationSize=1)
    @Column(name="id")
    private Integer id;

    @Lob
    private String description;

    @Temporal(TemporalType.TIMESTAMP)
    private Date creationDate;

    @Transient
    private MultipartFile multipartFile;

    @Lob
    @Basic(fetch=FetchType.LAZY, optional=true)
    byte[] file;

    String fileName;

    String fileContentType;

    Integer fileSize;

    public void setMultipartFile(MultipartFile multipartFile) {
        this.multipartFile = multipartFile;
        try {
            this.file = this.multipartFile.getBytes();
            this.fileName = this.multipartFile.getOriginalFilename();
            this.fileContentType = this.multipartFile.getContentType();
            this.fileSize = ((Long) this.multipartFile.getSize()).intValue();
        } catch (IOException e) {
            logger.error(e.getStackTrace());
        }
    }
}

I can see that when it is persisted I have the data in the row but when I call this method it fails:

public List<MyClass> findByDescription(String text) {
    Query query = getEntityManager().createQuery("from MyClass WHERE UPPER(description) like :query ORDER BY creationDate DESC");
    query.setParameter("query", "%" + text.toUpperCase() + "%");
    return query.getResultList();
}

This method only fails when the result has objects with files. I've tried to set in my persistence.xml

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

but it doesn't solve the problem.

In general the application works well it only commit the data when the transaction is finished and it performs a rollback if something fails, so I don't understand why is this happening.

Any idea?

Thanks.

UPDATE

Looking at the link given by Shekhar it is suggested to include the call in a transation, so I've set the service call inside a transaction an it works (I've added @Transactional annotation).

@Transactional
public List<myClass> find(String text) {
    return myClassDAO.findByDescription(text);
}

the problem is that I don't want to persist any data so I don't understand why it should be include inside a transaction. Does it make any sense to make a commit when I've only loaded some data form the database?

Thanks.

Answer 1

A large object can be stored in several records, that's why you have to use a transaction. All records are correct or nothing at all.

https://www.postgresql.org/docs/current/static/largeobjects.html

Answer 2

Instead of using @Transactional, all I did for this issue was to update this column in PostgreSQL DB from oid type to bytea type and added @Type for the @Lob field.

i.e.

ALTER TABLE person DROP COLUMN image;
ALTER TABLE person ADD COLUMN image bytea;

And changed

@Lob
private byte[] image;

To

@Lob
@Type(type = "org.hibernate.type.ImageType")
private byte[] image;

Answer 3

Use @Transactional on your Repository Interface.

Answer 4

If you can, create a intermediate Entity between MyClass and file property for instance. Something like:

@Entity
@Table(name="myobjects")
public class MyClass {
    @OneToOne(cascade = ALL, fetch = LAZY)
    private File file;
}

@Entity
@Table(name="file")
public class File {
     @Lob
     byte[] file;
}

You can't use @Lob and fetch type Lazy. It doesnt work. You must have a a intermediate class.

Answer 5

Unless you need to store files large than 1GB I suggest you use bytea as the datatype instead of large object.

bytea is basically what BLOB is in other databases (e.g. Oracle) and it's handling is a lot more compatible with JDBC.

Answer 6

That's an old question but I will share what I've found for those who get here by a google search as I did.

This comment in an issue in JHipster project has a better explanation of the problem and also describe more options to solve it.

I will sumarize it here:

Spring boot app, "out of the box", uses HikariCP connection pool and with autocommit=true by default. As it seems, the PostgreSQL jdbc driver requires that the handling of anything that uses a Lob be done with autocommit=false or inside a proper transaction.

The solutions (and explanations) proposed by Julien Dubois in the link above are very reasonable:

• Mark the repository @transactional : I don't like it very much, because that gives more work to the service layer (which has to join the transaction, etc). So there's a little performance hit, for nothing much.

• Mark the REST endpoint with @transactional(readOnly=true) : I don't like having transactions in the view layer. There's one added benefit from the point above: you can mark your transaction as readonly, so you have a performance gain.

• Configure the connection pool to have autocommit = false. In fact, I thought we were already doing that, as the (older) connections pools I used were doing this. But that's not the case with HikariCP!!

That said, in my opinion, to turn off the autocommit in the connection pool seems as the better approach. It is more secure, it allows for one to work with read operations without the cost of a transaction and it is a global solution.

Just place this on the spring application.properties of your project:

spring.datasource.hikari.auto-commit=false

Answer 7

The compiler does not understand "type" as a parameter

@Type(type = "org.hibernate.type.ImageType")

Answer 8

You should check if auto-commit is really set false with method getAutoCommit() on your Connection.

In my case

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

did not work, because the type of my db connection required auto-commit to be true.

In my case the problem was in the JBoss Configuration. I was using an JTA-datasource and that caused the problem. With an XA-datasource it worked fine. See this post for more details.

Answer 9

You can try his :

@Column(name = "LONG_TEXT", columnDefinition="TEXT")
private String longText;

Answer 10

I removed @Lob from entity and it's working.

//@Lob
private String description;