使用PL/pgSQL批量迁移分区数据时删除行数不符问题排查

问题背景 #

针对一张4亿条记录的表进行分区迁移，采用以下方案：

• 创建对应月份的子表作为待挂载分区
• 同一事务内将父表数据插入子表并删除父表对应数据
• 取消继承关系，将原子表挂载为新分区表

该方案本地小数据测试正常，但生产环境出现异常：部分批次中删除行数远少于插入行数，多次执行脚本导致子表出现重复数据（子表无约束）。需排查删除行数不符的原因，同时解决数据重复及父表清理问题。

核心原因排查 #

1. CTE未指定ONLY导致读取子表数据 #

脚本中两次CTE查询父表时未使用ONLY关键字：

SELECT * FROM parent_table -- 未加ONLY，会读取父表+所有继承子表的数据

由于父表和子表是继承关系，不带ONLY的查询会包含已迁移到子表的数据，导致：

• 插入时将子表中已存在的数据重复插入到目标子表
• 删除时使用DELETE FROM ONLY pod.parent_table，仅删除父表数据，子表中的重复数据不会被删除，最终插入行数远大于删除行数

2. 两次CTE查询的数据集不一致 #

插入和删除操作使用两个独立的CTE查询，中间无行锁保护：

• 若其他事务在插入后、删除前修改了父表中对应行的id或inserttime，会导致删除时无法匹配到对应行
• 无锁情况下可能出现幻读，两次查询的数据集不完全一致，导致删除行数少于插入行数

3. 时间范围不匹配 #

初始获取偏移量的查询条件是inserttime < ('2023-02-01')，但两次CTE的时间范围是inserttime < ('2023-02-12')，超出了目标迁移范围：

• 插入了不属于目标月份的数据，这些数据可能原本就不在父表（已被其他批次处理或属于其他子表），导致删除时无法找到对应行

修复方案 #

1. 统一使用ONLY关键字查询父表 #

所有查询父表的操作都加上ONLY，避免读取继承子表的数据：

SELECT * FROM ONLY pod.parent_table

2. 统一时间范围 #

将两次CTE的时间范围修正为与初始偏移量查询一致：

WHERE inserttime >= ('2023-01-01')
  AND inserttime < ('2023-02-01') -- 替换原来的2023-02-12

3. 复用同一数据集进行插入和删除 #

将待处理数据先查询出来并锁定，避免两次查询的数据集不一致，修改后的核心逻辑如下：

DO $$
    DECLARE
        batch_size INTEGER := 10000;
        off_set INTEGER := 0;
        rows_inserted INTEGER;
        rows_deleted INTEGER;
        -- 定义临时记录变量存储批次数据
        recs pod.parent_table[];
    BEGIN
        SELECT MIN(id) INTO off_set FROM ONLY pod.parent_table 
        WHERE inserttime >= ('2023-01-01') AND inserttime < ('2023-02-01');
        
        LOOP
            -- 一次性查询并锁定批次数据
            SELECT array_agg(t) INTO recs
            FROM ONLY pod.parent_table t
            WHERE inserttime >= ('2023-01-01')
              AND inserttime < ('2023-02-01')
              AND id >= off_set
            ORDER BY id ASC
            LIMIT batch_size
            FOR UPDATE; -- 锁定行，防止其他事务修改
            
            rows_inserted := COALESCE(array_length(recs, 1), 0);
            IF rows_inserted = 0 THEN
                RAISE info e'No more rows to insert from range (2023-01-01) - (2023-02-01) into table: [child_table]\n';
                EXIT;
            END IF;
            
            -- 插入数据
            INSERT INTO child_table SELECT (unnest(recs)).*;
            RAISE info 'Inserted [%] rows into [child_table]!', rows_inserted;
            
            -- 删除数据
            DELETE FROM ONLY pod.parent_table
            WHERE id = ANY (array(SELECT (unnest(recs)).id));
            GET DIAGNOSTICS rows_deleted = row_count;
            RAISE info 'Deleted [%] rows from [parent_table] in the range of (2023-01-01) - (2023-02-01)!', rows_deleted;

            RAISE info e'[%] rows were inserted and [%] were deleted in the batch ending at offset [%]\n', rows_inserted, rows_deleted, off_set + rows_inserted;

            COMMIT;

            off_set := off_set + batch_size;

            -- PERFORM pg_sleep(5);
        END LOOP;
    END;
$$;

4. 给子表添加唯一约束 #

为避免重复插入，给子表添加id字段的唯一约束：

ALTER TABLE child_table ADD CONSTRAINT child_table_id_unique UNIQUE (id);

临时数据清理方案 #

子表去重 #

删除子表中重复的id记录（保留最新插入的一条）：

DELETE FROM child_table c
USING (
    SELECT id, MAX(ctid) AS max_ctid
    FROM child_table
    GROUP BY id
    HAVING COUNT(*) > 1
) dup
WHERE c.id = dup.id AND c.ctid != dup.max_ctid;

清理父表已迁移数据 #

删除父表中已成功迁移到子表的数据：

DELETE FROM ONLY pod.parent_table p
WHERE EXISTS (
    SELECT 1 FROM child_table c WHERE c.id = p.id
)
AND p.inserttime >= ('2023-01-01') AND p.inserttime < ('2023-02-01');

内容的提问来源于stack exchange，提问作者Bylaw