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.

Burwell

if you don't want to persist data why do you have files mapped? Shouldn't it be @Transient? – Cabbala

@matt b I want to persist the files but only in the save operation not in the read one. That was why I didn't understand that the read should be in a transaction. – Burwell

You don't just use @Transactional when you want to save objects. A transaction is generally required to access the database - whether reading or writing. – Ashelman

After this exception, hibernate is unable to connect with databse throws: Caused by: org.hibernate.HibernateException: Unable to access lob stream, is there any database command or something to restore this database? – Ulcerative

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

Garter

I am not quite sure how that is an answer to the original question? The original poster doesn't want to write LOBs without a transaction, he wants to read them without one. – Deadradeadweight

By default LOBs are loaded lazy and that loading should happen in the same transaction as the parent objects are loaded. – Embassy

how is this an answer? – Laveta

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;

Powder

You're a life saver! – Undecided

Adding the @Type annotation was enough for me, thank you! – Nameplate

This should be the correct answer instead! All you need to do is to define the hibernate type of the LOB and Lazy fetch works fine. I intended to store a very large String and fixed the problem by using org.hibernate.type.StringType instead. – Ribonuclease

This solution returned an empty result in my case. – Poppy

Answer 3

Use @Transactional on your Repository Interface.

Fermentative

Show unstopper! Thanks. – Anthology

Answer 4

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

Ruhl

Thanks! Adding spring.datasource.hikari.auto-commit=false worked for me. – Depalma

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.

Part

Answer 6

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.

Stanleigh

If you don't need to retrieve the Lob on every query, then this is the best approach. It will incur some overhead, as an extra query needs to be dispatched when the object is accessed, but in some situations, this is ideal. – Unimposing

Answer 7

The compiler does not understand "type" as a parameter

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

Swum

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.

Smooth

Answer 9

You can try his :

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

Pandurate

Answer 10

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

//@Lob
private String description;

Yoicks

Answer 11

I'm using postgres TEXT type, and @Type(PostgreSQLCITextType.class) on the entity field solved for me.

Entity:

@Lob
@Column(name = "description", columnDefinition="TEXT",  nullable = true)
@Type(PostgreSQLCITextType.class)
private String description;

Origin