PostgreSQL触发器异常：累加全部Cycle_Index而非各Test_id最大循环值

场景与需求 #

现有三张关联表：Batteries（电池基础信息）、Tests（测试记录）、Cycle_Data（循环测试原始数据），表结构如下：

CREATE TABLE "Batteries" (
  "Battery_id" int PRIMARY KEY,
  "Battery_Name" Varchar(255),
  "Brand" Varchar(255),
  "Cathode" Varchar(255),
  "Form_Factor" Varchar(255),
  "Capacity" Decimal(4,2),
  "Total_Cycle_Number" int,
  "Total_LifeTime" Decimal(4,2)
);

CREATE TABLE "Tests" (
  "Test_id" int PRIMARY KEY,
  "Test_Name" Varchar(255),
  "Battery_id" int,
  "Work_Group" Varchar(255),
  "Date" date,
  "Average_Temp" Decimal(5,2),
  "Max_SoC" Decimal(5,2),
  "Min_SoC" Decimal(5,2),
  "Charge_Rate" Decimal(5,2),
  "Discharge_Rate" Decimal(5,2)
);

CREATE TABLE "Cycle_Data" (
  "Mesure_id" int PRIMARY KEY,
  "Mesure_Name" varchar(255),
  "Test_id" int,
  "Test_Time" Decimal(12,3),
  "Cycle_Index" int,
  "Vmin" Decimal(5,3),
  "Vmax" Decimal(5,3),
  "Charge_Capacity" Decimal(5,3),
  "Discharge_Capacity" Decimal(5,3),
  "Charge_Energy" Decimal(5,3),
  "Discharge_Energy" Decimal(5,3)
);

ALTER TABLE "Tests" ADD FOREIGN KEY ("Battery_id") REFERENCES "Batteries" ("Battery_id");
ALTER TABLE "Cycle_Data" ADD FOREIGN KEY ("Test_id") REFERENCES "Tests" ("Test_id");

需求：通过Python导入CSV到Cycle_Data后，为每个新增的Test_id取最大Cycle_Index，将该值累加到对应Batteries的Total_Cycle_Number字段，最终Total_Cycle_Number为所有测试的最大循环值之和。

原触发器问题 #

原触发器采用FOR EACH ROW触发逻辑，代码如下：

CREATE OR REPLACE FUNCTION update_total_cycle_number()
RETURNS TRIGGER AS $$
DECLARE
    max_cycle_index INTEGER;
BEGIN
    SELECT max("Cycle_Index") INTO max_cycle_index
    FROM "Cycle_Data"
    WHERE "Test_id" = NEW."Test_id";

    UPDATE "Batteries"
    SET "Total_Cycle_Number" = "Total_Cycle_Number" + max_cycle_index
    WHERE "Battery_id" = (SELECT "Battery_id" FROM "Tests" WHERE "Test_id" = NEW."Test_id");

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE TRIGGER update_total_cycle_trigger
AFTER INSERT ON "Cycle_Data"
FOR EACH ROW
EXECUTE FUNCTION update_total_cycle_number();

问题原因：FOR EACH ROW会对插入的每一行数据执行一次触发器函数。假设同一个Test_id插入100行记录，每次执行都会取出该Test_id的最大Cycle_Index（比如100）并累加，最终Total_Cycle_Number会被累加100次100，而非仅累加一次100。

修复方案 #

将触发器改为语句级触发器（FOR EACH STATEMENT），每次插入操作（无论插入多少行）仅执行一次函数，批量处理新增的Test_id，确保每个Test_id只累加一次最大循环值：

CREATE OR REPLACE FUNCTION update_total_cycle_number()
RETURNS TRIGGER AS $$
BEGIN
    -- 从本次插入的Cycle_Data记录中提取唯一Test_id，并计算每个Test_id的最大Cycle_Index
    WITH new_test_max_cycles AS (
        SELECT "Test_id", MAX("Cycle_Index") AS max_cycle
        FROM NEW
        GROUP BY "Test_id"
    ),
    test_battery_mapping AS (
        SELECT ntmc."Test_id", ntmc.max_cycle, t."Battery_id"
        FROM new_test_max_cycles ntmc
        JOIN "Tests" t ON ntmc."Test_id" = t."Test_id"
    )
    -- 批量更新对应电池的总循环次数
    UPDATE "Batteries" b
    SET "Total_Cycle_Number" = b."Total_Cycle_Number" + tbm.max_cycle
    FROM test_battery_mapping tbm
    WHERE b."Battery_id" = tbm."Battery_id";

    RETURN NULL; -- 语句级触发器无需返回NEW/OLD
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE TRIGGER update_total_cycle_trigger
AFTER INSERT ON "Cycle_Data"
FOR EACH STATEMENT
EXECUTE FUNCTION update_total_cycle_number();

方案说明 #

1. 语句级触发：FOR EACH STATEMENT确保每次CSV导入操作（无论插入多少行）仅执行一次函数，避免重复累加。
2. 批量处理：通过NEW表获取本次插入的所有记录，分组计算每个Test_id的最大Cycle_Index，避免重复处理同一Test_id。
3. 关联更新：通过Tests表关联Test_id与Battery_id，批量更新Batteries表的Total_Cycle_Number，保证效率与准确性。

额外注意事项 #

如果存在同一Test_id多次导入数据的场景（比如后续补充该测试的新循环数据），当前方案会累加新的最大循环值。若需确保每个Test_id仅贡献一次最大循环值，可新增一张中间表记录已处理的Test_id及其贡献的循环值，在触发器中判断是否已处理过该Test_id，仅累加差值或跳过重复处理。

内容的提问来源于stack exchange，提问作者Martí Aguilar Vallverdú