NestJS+PostgreSQL+TypeORM Upsert报ON CONFLICT重复更新错误排查

问题场景 #

定义了带有联合唯一约束的支付实体：

@Entity(DB_TABLES.PAYMENTS.NAME)
@Unique(["paidAmount", "paidDate", "centrelinkRef"])
export class PaymentEntity extends CommonEntity {

  @Column({ name: "actual_amount", type: "decimal", precision: 10, scale: 2, nullable: true })
  actualAmount: number;

  @Column({ name: "payment_status", type: "enum", enum: PaymentStatus, default: PaymentStatus.Paid })
  paymentStatus: PaymentStatus;

  @Column({ name: "paid_amount", type: "decimal", precision: 10, scale: 2, nullable: true, default: 0 })
  paidAmount: number;

  @Column({ name: "paid_date", type: "timestamptz", nullable: true })
  paidDate: Date;

  @Column({ name: "centrelink_ref", type: "varchar", nullable: true })
  centrelinkRef: string;

}

通过TypeORM的upsert方法批量添加支付记录：

async _addValidPayments(queryRunnerManager: EntityManager, payments: PaymentDto[]): Promise<ApiResponseDto> {
    const conflictPaths = ["paidAmount", "paidDate", "centrelinkRef"];
    const res = await queryRunnerManager.upsert(PaymentEntity, payments, {
      conflictPaths, skipUpdateIfNoValuesChanged: true, upsertType: "on-conflict-do-update"
    });
    console.log("PaymentService ~ _addValidPayments ~ res:", res);
    return { msg: APP_MESSAGE.CREATED_SUCCESSFULLY };
}

执行时抛出错误：

[Nest] 4320  - 02/19/2024, 3:04:06 PM   ERROR [ExceptionsHandler] 
ON CONFLICT DO UPDATE command cannot affect row a second time     
QueryFailedError: ON CONFLICT DO UPDATE command cannot affect row 
a second time
    at PostgresQueryRunner.query (E:\PROJECTS\Neha\orange_rentals\backend\src\driver\postgres\PostgresQueryRunner.ts:299:19)        
    at processTicksAndRejections (node:internal/process/task_queues:95:5)

错误原因 #

• 批量数据内部存在重复的冲突键组合：传入的payments数组中，存在多个条目在paidAmount、paidDate、centrelinkRef这三个字段的组合上完全一致。PostgreSQL执行UPSERT操作时，第一个匹配冲突约束的条目会触发对目标行的更新，后续相同的条目再次尝试修改同一行，就会触发这个错误——PostgreSQL不允许单次UPSERT操作中多次修改同一行。
• 联合约束与批量UPSERT的逻辑冲突：实体上的@Unique联合约束确保了数据库中不会存在重复的键组合，但批量输入的数据内部如果存在重复，TypeORM生成的UPSERT语句会试图多次处理同一行，违反PostgreSQL的执行规则。

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