PowerJob连接Postgres数据库出现org.springframework.orm.jpa.JpaSystemException: Unable to access lob stream; nested exception is org.hibernate.HibernateException: Unable to access lob stream

[pangzhili]

问题描述：

    PowerJob连接Postgres数据库出现 ：org.springframework.orm.jpa.JpaSystemException: Unable to access lob stream; nested exception is org.hibernate.HibernateException: Unable to access lob stream

具体详情：
使用Postgres连接PowerJob,任务添加正常，但是在任务管理中刷新任务列表失败，报错：org.springframework.orm.jpa.JpaSystemException: Unable to access lob stream; nested exception is org.hibernate.HibernateException: Unable to access lob stream，通过排查找到接口为com.github.kfcfans.powerjob.server.web.controller.JobController#listJobs ，定位到该类中的 jobInfoPage = jobInfoRepository.findByAppIdAndStatusNot(request.getAppId(), SwitchableStatus.DELETED.getV(), pageRequest);

问题解决：
通过debug发现，是返回值中的JobInfoDO实体中的
// 执行器信息（可能需要存储整个脚本文件）
@lob
@column
private String processorInfo;
这个字段导致的反序列化失败，可能是因为该字段的类型是TEXT类型，通过修改为
// 执行器信息（可能需要存储整个脚本文件）
@column(columnDefinition = "TEXT")
private String processorInfo;
即可正常查询任务列表。

经过使用并测试，希望能接受建议，并修改issue，谢谢。

[KFCFans]

感谢你那么细致的排查，并提供了恰当的解决方式。

PowerJob 对 PostgreSQL 的支持一直存在问题，正在找机会解决。

在后续版本会采纳你的建议并修复该问题。

再次感谢！

[QcoderWho]

还要加个自动提交事务的配置spring.datasource.hikari.auto-commit=true，不然会出现提示“org.postgresql.util.PSQLException: 大型对象无法被使用在自动确认事物交易模式”的问题

[KFCFans]

哈哈，你们这些 postgreSQL 用户可以提提 PR fix 下这个问题

[alanbeen]

还要加个自动提交事务的配置spring.datasource.hikari.auto-commit=true，不然会出现提示“org.postgresql.util.PSQLException: 大型对象无法被使用在自动确认事物交易模式”的问题

我加上你这个配置也报错的
我是在@lob上方加上
@org.hibernate.annotations.Type(type = "org.hibernate.type.TextType")
解决的

[QcoderWho]

还要加个自动提交事务的配置spring.datasource.hikari.auto-commit=true，不然会出现提示“org.postgresql.util.PSQLException: 大型对象无法被使用在自动确认事物交易模式”的问题

我加上你这个配置也报错的
我是在@lob上方加上
@org.hibernate.annotations.Type(type = "org.hibernate.type.TextType")
解决的

看看数据库里面这个字段的类型是不是text，可能是在创建过数据库后才加了这个注解

[Echo009]

对 PG 不是很了解，这个问题一直拖到了现在 🐶 ~

问题出现的原因 🤔

在 Postgresql 12.6 自动生成的表结构下 debug 了一下源码，发现问题出现在解析结果集的时候，在处理 @Lob 注解标记的列时，底层 PG 驱动的代码将其解析成了 PgClob 类型，PG 驱动在处理 PgClob 类型的列时，需要先获取一个输入流，再从流中读取信息。在获取输入流对象的过程中，需要先构造一个 LargeObject ，在初始化其中的成员变量 fd （当前所打开的大对象的描述符）时，底层调用了 org.postgresql.core.v3#fastpathCall 方法和 PG 服务端通信，PG 服务端返回了一个异常信息 large object 1 does not exist 。

起初有点难以理解，这是什么奇怪的操作 👀 ？结果集里明明就有现成的结果了，为啥还和得服务端通信？这个错误信息又是什么意思？

后来查了一圈资料 + 翻了一波 Spring Data JPA （后面简称为 JPA，不过其实跟 Spring Data JPA 没啥关系，这里涉及到的内容都是 Hibernate 的源码）类型判断和建表的源码，大概是弄明白了 (〃´皿`)q

首先 PG 中没有 CLob 类型，不过也确实支持超大对象的存储，利用的是一种叫 TOAST（The Oversized-Attribute Storage Technique）的技术，基本原理是将实际的数据存放到单独的表（pg_largeobject）中，原字段只存储对应的 ID ，具体内容可以参考这篇博客 PostgreSQL Toast and Working with BLOBs/CLOBs Explained。

出现 large object 1 does not exist 这个错误信息的原因就是因为 pg_largeobject 表中确实没有这个大对象。

另外，在 PG 中 JPA 自动建的表中 @Lob 注解标记的列实际类型都为 text ，显然有点牛头不对马嘴。

临时解决方案

最直接的解决方案就是想办法让 Hibernate 不要将其识别为 Clob 类型，可以去掉 @Lob 注解或者加一个 @org.hibernate.annotations.Type(type = "org.hibernate.type.TextType") ，但这样会影响 Hibernate 在其他数据库下的行为，不够优雅。

而出现 “org.postgresql.util.PSQLException: 大型对象无法被使用在自动确认事物交易模式” 这个错误则是因为在 PG 中访问大对象必须开启事务。（参考 官方文档 ）

但其实只要使用了 @org.hibernate.annotations.Type(type = "org.hibernate.type.TextType") 注解，该属性就不会被识别为 Clob 类型，而是被识别成 LongVarchar ，LongVarchar 属于 JDBC 中定义的通用 SQL 数据类型之一，所以应该是还有其他字段被 @Lob 注解标记了，但没有使用 @org.hibernate.annotations.Type(type = "org.hibernate.type.TextType")注解。

后面看了下这位老哥的 PR 代码，其核心解决思路是提供了一个 PostgreSQL10Dialect 的子类，重写了 remapSqlTypeDescriptor 方法 ，判断如果是 Clob 类型的列则使用 LongVarchar 覆盖其原有类型。本质上和使用 @org.hibernate.annotations.Type(type = "org.hibernate.type.TextType") 注解没什么区别，不过要相对优雅一些，仅在 PG 数据库下定制这个类型覆盖的行为。

但同样也存在一些问题，首先即使重写，也应该优先重写 Dialect#getSqlTypeDescriptorOverride 方法。其次是这种方式需要显式指定数据库方言，违背了 JPA 的设计原则。另外因为继承的是 PostgreSQL10Dialect ，在 PG 10 以下的版本可能会存在兼容性问题。

问题出现的根本原因

其实问题核心应该是为什么 JPA 自动建表的时候会将其解析成 text 类型，而读取的时候为什么不能将其当成 text 类型？ 是使用姿势不对还是说 JPA 和 PG 的驱动有兼容性问题？

JPA 中自动建表的原理大致如下（源码流程太长，只讲关键内容）

JPA 在初始化会话工厂（ org.hibernate.internal.SessionFactoryImpl）的时候，会将当前 JPA 的上下文信息全部初始化到其成员变量 metamode (MetamodelImplementor ，元数据模型) 中，调用其 initialize 方法初始化表的基础信息。

并通过调用 org.hibernate.tool.schema.spi.SchemaManagementToolCoordinator 的 process 方法实现自动建表（line 73）。而 DDL 的生成，最终是委托给了 org.hibernate.tool.schema.internal.StandardTableExporter , 该类绑定了一个数据库方言。

在生成 DDL 的时候，列的定义最终是通过调用 org.hibernate.mapping.Column#getSqlType(Dialect dialect, Mapping mapping) 得到的，本质上就是查询对应数据库方言中 sqlTypeCode 对应的类型名称，这个信息存储在其成员变量 typeNames （ org.hibernate.dialect.TypeNames ）中。

在初始化对应的数据库方言的时候，会将对应的类型映射信息写入其中，比如在 PostgreSQL81Dialect 的构造函数中就显式的将 Clob 类型的列和 text 进行了绑定。

也就意味着，默认情况下，Hibernate 在 PG 环境对于 Clob 类型的列，在创建表的时候使用的列类型都是 text 。

这里再补充一下 JPA 中列类型识别的原理

JPA 在初始化模型元数据时，调用了 PropertyFactory.buildEntityBasedAttribute 来确定列的信息，最终会委托给 org.hibernate.mapping.SimpleValue 的 getType()方法。

如果其 TypeName 不为空，则会优先根据 TypeName 推导类型。而 TypeName 信息是在 org.hibernate.cfg.annotations.SimpleValueBinder#setType 中确定的，如果使用了 @Type 注解，则会优先读取 @Type 注解中的属性，直接使用属性指定的类型。比如使用了@org.hibernate.annotations.Type(type = "org.hibernate.type.TextType") 注解的会被解析成 LongVarchar 。（具体的映射关系可以从类的源码注释信息中获取）

如果没有使用 @Type 注解，则根据实际的数据类型进行推导，对于使用 @Lob注解标记的会特殊处理。比如使用 @Lob 注解标记，且返回值类型为 String 的会解析为 MATERIALIZED_CLOB 类型。

而MATERIALIZED_CLOB 类型对应的 SqlTypeDescriptor 就是 ClobTypeDescriptor，即对应 Clob 类型，映射到 PG 驱动提供的类型就是 PgClob。

综上两个原因就导致了建表的 “行为” 和 读取的 “行为” 不一致的情况（@lob 注解标记的 String 类型字段，在 PG 下，JPA 自动建表会使用 text 类型，而读取的时候却会被当成 Clob 对象处理）。

也就是说并非我们使用的姿势不对，而是 JPA 和 PG 的驱动存在兼容性问题。

终极解决方案

显然，终极解决方案就是让该字段的 JPA 自动建表行为 和 PG 驱动对该字段的处理保持 “一致”，即 JPA 自动建表时使用 oid 类型的列，或者 PG 驱动在处理时判断一下该列实际的存储类型（从结果集的元信息中获取）。从而保证不会出现能正常建表，却不能正常使用的情况。

那么问题来了，没人处理这个问题么？作为 “世界上最先进的开源数据库 ( The world's most advanced open source database )” 不应该才对 ! 🌚

搜了下，其实早在 6 年前就有人提过这个问题 ， Correct JPA Annotation for PostgreSQL's text type without Hibernate Annotations

Hibernate 的 Jira 上也有人报过问题，@Lob on String does not produce expected results for PostgreSQL

看了下相关内容，这个问题确实不是 Hibernate 的锅，而应该由 PG 的底层驱动支持。在 PG 的 JDBC 驱动项目搜了下相关 issue ，确实也有在处理只是还没发布，

feat: allow byte[] and String to be accessed as .getLob, and .getClob #611 （在解析 Clob 类型时增加了一个对实际数据类型的判断，代码见 PgResultSet.java#L464 ）

综上，PG 用户在 PG 官方没有修复这个问题之前，可以通过以下方式解决这个问题（三选其一）

1. 提供了一个 PostgreSQLDialect 的子类（注意选择自行合适的版本），重写其 Dialect#getSqlTypeDescriptorOverride 方法 ，判断如果是 Clob 类型的列则使用 LongVarchar 覆盖其原有类型 的方式来解决。（注意，需要显式指定数据库方言）
2. 将 PowerJob remote 实体类中 @Lob 注解去掉 （建完表之后）
3. 在所有使用 @Lob 注解标记的字段上增加 @org.hibernate.annotations.Type(type = "org.hibernate.type.TextType")注解

[KFCFans]

附录：4.2.1 版本server已默认提供方案1的实现，用户自行激活即可
tech.powerjob.server.persistence.config.dialect.PowerJobPGDialect

[daselang]

hi all,
append
--spring.datasource.core.auto-commit=false
to the startup options to fix it when using Pgsql as base database.