PSQLException:大型对象可能无法在自动提交模式下使用 [英] PSQLException: Large Objects may not be used in auto-commit mode

问题描述

我正在使用WildFly 10，Java EE，JPA和Hibernate.最近，我将应用程序从MySQL迁移到了PostgreSQL.使用MySQL时，我可以使用以下方式将图像存储在我的实体中:

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;

这很好用，MySQL使用LONGBLOB来存储数据.

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

切换到PostgreSQL后，列类型为OID，并且在保留图像时收到此错误:

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);

这是我的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.

一个建议是创建一个仅用于存储数据的实体，并使用@OneToOne关系，以便可以急切地获取存储数据的实体，但这丝毫没有改变结果.

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.

另一个建议是使用禁用Hibernate的自动提交功能

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?

推荐答案

我无法告诉您在Hibernate中是如何完成的，但是打开和读取/写入大对象必须在同一数据库事务中进行.

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.

但是我建议您根本不要使用大对象吗?
通常，使用bytea PostgreSQL数据类型要容易得多，它可以包含最大1GB的数据.除非您以块的形式存储和检索数据，否则大对象没有任何优势，而且我怀疑您是否仍可以通过ORM充分利用大对象功能.

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.

这篇关于PSQLException:大型对象可能无法在自动提交模式下使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！