PostgreSQL. 异常“more than one owned sequence found”的解决方案

一、异常信息描述

执行数据库操作时,主键id没有自增,且报“more than one owned sequence found”的异常,造成数据没有insert进去,下面是详细的异常信息:

java.lang.reflect.InvocationTargetException
    at sun.reflect.GeneratedMethodAccessor613.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at com.manage.quartz.util.JobInvokeUtil.invokeMethod(JobInvokeUtil.java:61)
    at com.manage.quartz.util.JobInvokeUtil.invokeMethod(JobInvokeUtil.java:33)
    at com.manage.quartz.util.QuartzJobExecution.doExecute(QuartzJobExecution.java:17)
    at com.manage.quartz.util.AbstractQuartzJob.execute(AbstractQuartzJob.java:43)
    at org.quartz.core.JobRunShell.run(JobRunShell.java:202)
    at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:573)
Caused by: org.springframework.jdbc.UncategorizedSQLException: 
### Error updating database.  Cause: org.postgresql.util.PSQLException: 错误: more than one owned sequence found
### The error may involve com.manage.provided.mapper.DispatchHisMapper.insertDispatchHis-Inline
### The error occurred while setting parameters
### SQL: insert into t_dispatch_his          ( pump_id,             pump_name,             region_id,                          value,             date )           values ( ?,             ?,             ?,                          ?,             ? )
### Cause: org.postgresql.util.PSQLException: 错误: more than one owned sequence found
; uncategorized SQLException; SQL state [XX000]; error code [0]; 错误: more than one owned sequence found; nested exception is org.postgresql.util.PSQLException: 错误: more than one owned sequence found
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:89)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
    at org.mybatis.spring.MyBatisException

而且,使用 Navicat 客户端手动插入一条数据时,也是弹框报错“more than one owned sequence found”。

二、解决方案安排

1、首先,查询重复的 “SEQUENCE”,查询语句如下:
SELECT 'DROP SEQUENCE "'||c.relname||'";' FROM pg_class c WHERE c.relkind ='S'
ORDER BY c.relname

查询结果如下图

找到重复的““SEQUENCE””对象,然后使用该删除语句进行删除。如:DROP SEQUENCE "t_dispatch_hist_id_seq";

执行完这步,就可以插入数据了,但是id自动会从1重新开始,下面我们来重置下id自增的起始数字。

2、重置下id自增的起始数字

根据表名查询对应的"SEQUENCE",sql如下:

select pg_get_serial_sequence('t_dispatch_his', 'id');

这样,就可以根据表名称查询出来对应的"SEQUENCE",进行下一步的操作。

3、重新设置id主键自增

根据项目需求,重新设置id主键的自增起始,代码如下:

ALTER SEQUENCE public.t_dispatch_his_id_seq RESTART WITH 773;

ok,经过上面的一些列操作,"more than one owned sequence found"的异常就被消灭掉了,可以愉快的去吃午饭了😏😏😏

posted @ 2023-12-23 10:37  大雷小屋  阅读(359)  评论(0编辑  收藏  举报