PostgreSQL执行INSERT INTO...SELECT FROM时偶发数据丢失问题求助

你好，我看到你遇到了PostgreSQL分批插入时偶发数据丢失的问题，而且已经确认staging表的数据是完整正确的，这种偶发问题确实挺头疼的，咱们一步步来排查可能的原因：

可能的问题点及对应的解决方案 #

1. LIMIT + OFFSET 分批逻辑的隐患

你当前用OFFSET分批的方式看似没问题，但如果在分批插入过程中，staging_tbl 存在数据删除、更新或新增操作（哪怕你觉得不会发生，偶发问题往往和这类场景相关），就会导致某些行被跳过。比如：

• 假设第一次取了前400万行，之后staging表中前100行被删除，下一次OFFSET=400万时，原本的第400万+1行就变成了第3999901行，中间的99行直接被漏掉。

解决办法：
放弃OFFSET，改用基于有序列的分批方式（如果staging表没有主键，建议先加一个临时自增列），示例代码如下：

# 先给staging表添加临时自增列（如果没有可用的有序列）
cursor.execute("ALTER TABLE staging_tbl ADD COLUMN temp_id SERIAL PRIMARY KEY")
connection.commit()

# 基于temp_id分批插入
last_id = 0
batch_size = 4000000
while True:
    try:
        bigSql = f"""INSERT INTO final_tbl({sqlCols}) 
                     SELECT {sqlCols} FROM staging_tbl 
                     WHERE temp_id > {last_id} 
                     ORDER BY temp_id LIMIT {batch_size}"""
        cursor.execute(bigSql)
        inserted_rows = cursor.rowcount
        print('Inserted ', inserted_rows)
        if inserted_rows == 0:
            break
        # 获取本次插入的最大temp_id，作为下一批的起始点
        cursor.execute("SELECT MAX(temp_id) FROM staging_tbl WHERE temp_id > %s", (last_id,))
        last_id = cursor.fetchone()[0]
        connection.commit()
    except Exception as e:
        print(f"Batch failed: {str(e)}")
        connection.rollback()
        break

# 最后可删除临时列
cursor.execute("ALTER TABLE staging_tbl DROP COLUMN temp_id")
connection.commit()

这种方式不管staging表数据是否变动，都能保证不会漏行。

2. 约束冲突导致的行被跳过

你的final表有主键和索引，如果插入过程中遇到主键/唯一键冲突，默认情况下PostgreSQL会直接报错，但如果你的插入语句隐含了ON CONFLICT DO NOTHING逻辑，冲突的行会被静默跳过，此时cursor.rowcount会比预期少，你可能误以为是数据丢失。

排查&解决：

• 检查final表的约束规则，在INSERT语句中显式处理冲突并记录日志：

INSERT INTO final_tbl({sqlCols}) 
SELECT {sqlCols} FROM staging_tbl 
WHERE temp_id > {last_id} 
ORDER BY temp_id LIMIT {batch_size}
ON CONFLICT (your_primary_key_column) DO INSERT INTO conflict_log_table SELECT excluded.*;

• 查看PostgreSQL服务器日志（在Laravel Forge面板的PostgreSQL管理模块中可找到），搜索是否有duplicate key value violates unique constraint这类错误记录。

3. 事务提交失败未被捕获

你当前每批插入后执行connection.commit()，但如果遇到网络波动、服务器临时卡顿等情况导致提交失败，而代码没有捕获异常，这一批的数据就会丢失，但cursor.rowcount仍会返回插入行数，造成“数据已插入”的错觉。

解决办法：
给代码加上异常捕获和回滚逻辑，确保每批操作的可靠性：

import traceback

last_id = 0
batch_size = 4000000
while True:
    try:
        # 分批插入逻辑...
        connection.commit()
    except Exception as e:
        print(f"Error during batch insertion: {str(e)}")
        traceback.print_exc()
        connection.rollback()
        # 可添加重试逻辑或终止程序排查
        break

4. 服务器资源或锁问题

如果Azure VM的PostgreSQL服务器配置不足（如内存不够、连接数饱和），或者插入过程中final表被其他操作占用锁资源，可能会导致部分插入操作被中断，而代码未感知到。

排查方法：

• 在Laravel Forge面板查看服务器CPU、内存、磁盘IO的实时使用情况，确认插入时是否存在资源瓶颈；
• 查看PostgreSQL日志，排查是否有死锁、超时、内存不足等错误信息。

总结 #

最可能的原因是LIMIT+OFFSET的分批逻辑导致漏行，或是约束冲突静默跳过了数据。建议先改用基于有序列的分批方式，同时添加异常捕获和冲突日志，这样能更快定位并解决问题。

备注：内容来源于stack exchange，提问作者Harish Nandoliya