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"的異常就被消滅掉了，可以愉快的去吃午飯了