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"的异常就被消灭掉了，可以愉快的去吃午饭了😏😏😏