Postgresql 如何在 springJPA 中记录表和列名以防数据库错误

发布于03月17日

在单个服务中，我使用Spring data/JPA/Hibernate将整个对象层次 struct 持久化到PostgreSQL.

在持久化其中一个实例时，DB会引发错误，指出数据对于给定列定义来说太长:

ERROR: value too long for type character varying(5)

PG和Spring都不会记录表/实体名称或列名称.

日志(log)中唯一的提示是上述指向服务方法的消息和堆栈跟踪.

当然，我可以乏味地分析数据和表定义，并得出一个相当窄的可疑点列表，但我相信一定有更好的解决方案，一种配置PG或Spring/JPA为我记录的方法.

OFC我不想在全局范围内更改Spring data或Hibernate的日志(log)记录级别，以进行调试或跟踪.那会把我的日志(log)弄得乱七八糟.

我只需要这些简单的信息，以防插入/更新失败.

推荐答案

遗憾的是，似乎没有一种方法可以设置Spring或PostgreSQL来提供这些信息.

问题出在PostgreSQL.它不会记录(或返回给客户端)导致错误的表/列.

如果将client_min_messages和log_min_messages设置为最高级别，即DEBUG5:

set log_min_messages = 'DEBUG5';
set client_min_messages = 'DEBUG5';
insert into persons (name, title) values ('too long', 'Joe bloggs')

这将向pgadmin客户端返回以下内容:

DEBUG:  StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0

ERROR:  value too long for type character varying(4)
SQL state: 22001

并记录以下内容:

2023-03-17 11:15:57,995: ERROR  pgadmin:    Failed to execute query (execute_async) for the server #1 - CONN:8508187(Query-id: 916721):
Error Message:DEBUG:  StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0

ERROR:  value too long for type character varying(4)
SQL state: 22001

我建议您使用javax.validation.constraints.Size或jakarta.validation.constraints.Size(适用于春靴3.x/jdk17+)和spring-boot-starter-validation:

@Column(name = "title", length=4)
@Size(max = 4)
private String title;

这将提供更好的信息以供使用:

javax.validation.ConstraintViolationException: Validation failed for classes [com.example.demo.persistence.Person" during persist time for groups [javax.validation.groups.Default, "
List of constraint violations:[
    ConstraintViolationImpl{interpolatedMessage='size must be between 0 and 4', propertyPath=title, rootBeanClass=class com.example.demo.persistence.Person, messageTemplate='{javax.validation.constraints.Size.message}'}

为了完整起见，下面是Spring跟踪级别日志(log)的一个片段:

TRACE 16580  o.postgresql.core.v3.QueryExecutorImpl   :  FE=> Parse(stmt=null,query="BEGIN",oids={})
TRACE 16580  o.postgresql.core.v3.QueryExecutorImpl   :  FE=> Bind(stmt=null,portal=null)
TRACE 16580  o.postgresql.core.v3.QueryExecutorImpl   :  FE=> Execute(portal=null,limit=0)
TRACE 16580  o.postgresql.core.v3.QueryExecutorImpl   :  FE=> Parse(stmt=null,query="insert into persons (id, name, title) values (default, $1, $2)
1043})       
TRACE 16580  o.postgresql.core.v3.QueryExecutorImpl   :  FE=> Bind(stmt=null,portal=null,$1=<'joe bloggs'>,type=VARCHAR,$2=<'too long'>,type=VARCHAR)
TRACE 16580  o.postgresql.core.v3.QueryExecutorImpl   :  FE=> Describe(portal=null)
TRACE 16580  o.postgresql.core.v3.QueryExecutorImpl   :  FE=> Execute(portal=null,limit=0)
TRACE 16580  o.postgresql.core.v3.QueryExecutorImpl   :  FE=> Sync
TRACE 16580  o.postgresql.core.v3.QueryExecutorImpl   :  <=BE ParseComplete [null"
TRACE 16580  o.postgresql.core.v3.QueryExecutorImpl   :  <=BE BindComplete [unnamed"
TRACE 16580  o.postgresql.core.v3.QueryExecutorImpl   :  <=BE CommandStatus(BEGIN)
TRACE 16580  o.postgresql.core.v3.QueryExecutorImpl   :  <=BE ParseComplete [null"
TRACE 16580  o.postgresql.core.v3.QueryExecutorImpl   :  <=BE ErrorMessage(ERROR: value too long for type character varying(4))
TRACE 16580  o.postgresql.core.v3.QueryExecutorImpl   :  <=BE ReadyForQuery(E)
TRACE 16580  o.postgresql.core.v3.QueryExecutorImpl   :  FE marking setPortalDescribed(false) for query insert into persons (id, name, title) values (default, ?, ?)
             
TRACE 16580  o.postgresql.core.v3.QueryExecutorImpl   :  FE marking setPortalDescribed(false) for query SYNC
 WARN 16580  o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 0, SQLState: 22001
ERROR 16580  o.h.engine.jdbc.spi.SqlExceptionHelper   : ERROR: value too long for type character varying(4)
TRACE 16580  o.postgresql.core.v3.QueryExecutorImpl   :   simple execute, handler=org.postgresql.jdbc.PgConnection$TransactionCommandHandler@1e79986b, maxRows=0, fetchSize=0, flags=22
TRACE 16580  o.postgresql.core.v3.QueryExecutorImpl   :  FE=> Parse(stmt=S_1,query="ROLLBACK",oids={})
TRACE 16580  o.postgresql.core.v3.QueryExecutorImpl   :  FE=> Bind(stmt=S_1,portal=null)
TRACE 16580  o.postgresql.core.v3.QueryExecutorImpl   :  FE=> Execute(portal=null,limit=1)
TRACE 16580  o.postgresql.core.v3.QueryExecutorImpl   :  FE=> Sync
TRACE 16580  o.postgresql.core.v3.QueryExecutorImpl   :  <=BE ParseComplete [S_1"
TRACE 16580  o.postgresql.core.v3.QueryExecutorImpl   :  <=BE BindComplete [unnamed"
TRACE 16580  o.postgresql.core.v3.QueryExecutorImpl   :  <=BE CommandStatus(ROLLBACK)
TRACE 16580  o.postgresql.core.v3.QueryExecutorImpl   :  <=BE ReadyForQuery(I)