df.to_sql上传PostgreSQL时序库遇PendingRollbackError问题求助

• 检查异常行的时区时间戳值

  • 提取df2中53000行附近的带时区时间戳数据，重点排查是否存在格式错误、超出合法范围的时区偏移（PostgreSQL支持±14小时内的偏移）、或非标准IANA时区标识（比如自定义时区名）。
  • 用pd.to_datetime(df2['tz_time_col'], errors='raise')强制校验数据，避免pandas静默处理无效值导致写入数据库时触发隐性错误。

• 验证数据库时区配置兼容性

  • 执行PostgreSQL命令SHOW timezone;查看数据库时区设置，确认是否与DataFrame中时间戳的时区匹配。若数据库使用UTC，而数据包含非标准时区偏移，大批次转换时可能出现溢出或解析错误。
  • 核对PostgreSQL版本，旧版本（低于10）对带时区时间戳的批量插入存在已知bug，尤其是处理多时区偏移数据时。

• 排查SQLAlchemy的类型映射与批量逻辑

  • 明确指定to_sql的dtype参数：dtype={'tz_time_col': sqlalchemy.types.TIMESTAMP(timezone=True)}，避免pandas自动推断类型错误，大批次累积问题触发事务回滚。
  • 尝试关闭method='multi'（若开启），改用默认单条插入验证是否为批量SQL拼接时的时区字符串转义问题；或调整chunksize到临界值（如999、1000），定位是否为某一行的特定值导致整个批次失败。

• 捕获底层数据库错误信息

  • 给to_sql添加异常捕获，打印原始PostgreSQL错误：

    try:
        df2.to_sql('target_table', engine, chunksize=1000)
    except Exception as e:
        import traceback
        traceback.print_exc()
        if hasattr(e, 'orig'):
            print("原始数据库错误:", e.orig)
    

  • 查看PostgreSQL日志（pg_log目录下的日志文件），找到事务失败的具体原因（如invalid input syntax for type timestamp with time zone），定位到具体错误值。

• 测试时区数据转换逻辑

  • 将df2的时区时间戳统一转换为UTC后再上传：df2['tz_time_col'] = df2['tz_time_col'].dt.tz_convert('UTC')，验证是否因非标准时区偏移导致失败。
  • 绕过pandas，用psycopg2直接构造批量插入SQL语句，插入1000行带时区时间戳的数据，排查是pandas的问题还是数据库端的问题。

• 检查索引与事务锁冲突

  • 临时删除带时区时间戳列的索引（若该列是索引或主键的一部分），再尝试大批次插入，验证是否因索引更新的事务锁问题导致失败（小批次插入时锁竞争低，不易触发）。

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