记一次因数据库主键ID问题引发的莫名其妙的坑

引言：
前几天迁移服务器，碰到一个莫名其妙的问题，提示的错误见下图，排查了好久硬是没办法定位，后来在开发环境完完整整的模拟，才复现问题，在此记录一下。

 

环境：
    数据库：人大金仓
    缓存：Redis
    API：Java
    Portal：Nginx

 

其他环境运行正常，唯独在数据库迁移过程中，主键和序列都有问题。
首先是主键问题，竟然有好多张表达主键丢失了，为此专门写了一个脚本来解决此问题，后续贴上。
正是因为主键缺失，且序列重置，导致了新增的数据主键重复，一个表中已经有十几万条数据的表，主键重复了！！！
然后有段程序代码用到“saveOrUpdateBatch”方法，即有数据则修改无数据则新增，就报了上面那个莫名其妙的错误。
后来一查、一分析，原因是：主键重复，程序不知道该去改哪条数据，就报错了。
查找到原因，先重置序列，再将重复ID换掉，最后加上主键，程序重新运行，问题解决。

 

另记录两个脚本：
1、查询数据库中表使用自增id序列的检查

-- 检查并删除已存在的临时表
DROP TABLE IF EXISTS tmp_key_data;

-- 创建临时表存储结果
CREATE TEMP TABLE tmp_key_data (
    table_name TEXT,
    is_id_primary_key TEXT
);

INSERT INTO tmp_key_data
	WITH sequence_tables AS (
			SELECT 
					attrelid::regclass AS table_name
			FROM 
					pg_attribute att
			JOIN 
					pg_class cls ON att.attrelid = cls.oid
			JOIN 
					pg_namespace nsp ON cls.relnamespace = nsp.oid
			JOIN
					pg_attrdef ad ON ad.adrelid = att.attrelid AND ad.adnum = att.attnum
			WHERE 
					att.attname = 'id'                  -- 字段名为 id
					AND att.atthasdef = true            -- 字段有默认值
					AND nsp.nspname NOT LIKE 'pg_%'     -- 排除系统模式
					AND nsp.nspname <> 'information_schema'
					AND pg_get_expr(ad.adbin, ad.adrelid) LIKE 'nextval(%::regclass)' -- 默认值为序列
	)
	SELECT 
			st.table_name,
			EXISTS (
					SELECT 1
					FROM pg_constraint con
					JOIN pg_attribute key ON con.conrelid = key.attrelid AND key.attnum = ANY(con.conkey)
					WHERE 
							con.conrelid = st.table_name::regclass
							AND con.contype = 'p'  -- 主键约束
							AND key.attname = 'id' -- 字段名为 id
			) AS is_id_primary_key
	FROM 
			sequence_tables st
	ORDER BY st.table_name;

--第二列返回f的表示表里面有id，但是未设置主键


-- 查询临时表验证结果（可选）
SELECT * FROM tmp_key_data;

-- 查询表未设置主键的
SELECT * FROM tmp_key_data WHERE is_id_primary_key='false';

2、序列重置

--第一部分执行开始
-- 检查并删除已存在的临时表
DROP TABLE IF EXISTS tmp_sequence_data;

-- 创建临时表存储结果
CREATE TEMP TABLE tmp_sequence_data (
    table_name TEXT,
    sequence_name TEXT
);

-- 将查询结果插入临时表
INSERT INTO tmp_sequence_data
WITH sequence_tables AS (
    SELECT 
        attrelid::regclass AS table_name,
        pg_get_expr(ad.adbin, ad.adrelid) AS default_expr
    FROM 
        pg_attribute att
    JOIN 
        pg_class cls ON att.attrelid = cls.oid
    JOIN 
        pg_namespace nsp ON cls.relnamespace = nsp.oid
    JOIN
        pg_attrdef ad ON ad.adrelid = att.attrelid AND ad.adnum = att.attnum
    WHERE 
        att.atthasdef = true            -- 字段有默认值
        AND nsp.nspname NOT LIKE 'pg_%' -- 排除系统模式
        AND nsp.nspname <> 'information_schema'
        AND pg_get_expr(ad.adbin, ad.adrelid) LIKE 'nextval(%::regclass)' -- 默认值为序列
)
SELECT 
    table_name::text AS table_name,
    regexp_replace(
        regexp_replace(default_expr, '^nextval\(''', ''),
        '''::regclass\)$', ''
    ) AS sequence_name
FROM 
    sequence_tables
ORDER BY 
    table_name;

-- 查询临时表验证结果（可选）
SELECT * FROM tmp_sequence_data;
--第一部分执行结束


--第2部分执行开始
DO $$
DECLARE
    rec record;
    max_id bigint;
    sql_text text;
BEGIN
    -- 遍历临时表中的每一行数据
    FOR rec IN SELECT * FROM tmp_sequence_data LOOP
        BEGIN
            -- 获取表中id字段的最大值
            EXECUTE format('SELECT COALESCE(MAX(id), 0) FROM %s', rec.table_name)
            INTO max_id;
            
            -- 构造重置序列的SQL语句（序列值 = 表id最大值 + 1）
            sql_text := format('ALTER SEQUENCE %s RESTART WITH %s', 
                              rec.sequence_name, 
                              max_id + 1);
            
            -- 执行序列重置操作
            RAISE INFO '执行: %', sql_text;
            EXECUTE sql_text;
            
        EXCEPTION
            WHEN OTHERS THEN
                RAISE WARNING '处理表 % 时出错: %', rec.table_name, SQLERRM;
        END;
    END LOOP;
    
    RAISE INFO '序列重置完成!';
END $$;


 --第2部分执行结束