postgresql : Trigger leading to incorrect evaluation

Question

表和架构设置

---

考虑下表和触发器，

CREATE TABLE t1 (
  c1 int,
  c2 text,
  c3 text,
  m1 int,
  m2 int,
  primary key (c1,c2,c3)    
);



CREATE TABLE t2 (
  c1 int,
  c2 text,
  c3 text,
  aggm int,
  primary key (c1,c2,c3)
);  

CREATE OR REPLACE FUNCTION aggregation_of_t1() RETURNS TRIGGER AS $t1$
    BEGIN
        INSERT INTO t2(c1, c2,c3,aggm) 
          VALUES (new.c1, new.c2, new.c3, new.m1 + new.m2) 
            ON CONFLICT (c1,c2,c3) 
            DO UPDATE SET aggm = t2.aggm + new.m1 + new.m2;
        RETURN NULL;
    END;
$t1$ LANGUAGE plpgsql;

CREATE TRIGGER aggregate_trigger AFTER INSERT OR UPDATE ON t1 FOR EACH ROW EXECUTE PROCEDURE aggregation_of_t1();

---

t1:主表包含 m1、m2 列(标记)，用于 c1、c2、c3 标识符。
t2 :我希望此表是 t1 中所有唯一值(c1、c2、c3)的 m1 和 m2 的聚合。

---

问题

我看到 t2 中的聚合值(即 aggm)在我执行的每个插入操作中都翻了一番。

输出

root=> select * from t1;
 c1 | c2 | c3 | m1 | m2
----+----+----+----+----
(0 rows)

root=> select * from t2;
 c1 | c2 | c3 | aggm
----+----+----+------
(0 rows)

root=> insert into  t1 (c1,c2,c3,m1,m2) values (113,'URL-1','ID-1',23,22) ON CONFLICT (c1,c2,c3) DO UPDATE SET m1 = t1.m1+23, m2 = t1.m2 + 22;
INSERT 0 1
root=> select * from t1;
 c1  |  c2   |  c3  | m1 | m2
-----+-------+------+----+----
 113 | URL-1 | ID-1 | 23 | 22
(1 row)

root=> select * from t2;
 c1  |  c2   |  c3  | aggm
-----+-------+------+------
 113 | URL-1 | ID-1 |   45
(1 row)

root=> insert into  t1 (c1,c2,c3,m1,m2) values (113,'URL-1','ID-1',10,11) ON CONFLICT (c1,c2,c3) DO UPDATE SET m1 = t1.m1+10, m2 = t1.m2 + 11;
INSERT 0 1
root=> select * from t2;
 c1  |  c2   |  c3  | aggm
-----+-------+------+------
 113 | URL-1 | ID-1 |  111
(1 row)

root=> select * from t1;
 c1  |  c2   |  c3  | m1 | m2
-----+-------+------+----+----
 113 | URL-1 | ID-1 | 33 | 33
(1 row)  

问题:我不期望 t2 中的值 111。相反，我在这里期待 66(即 23 + 22(第一次插入)和 10 + 11(第二次插入))。
我认为在第二次插入时不知何故将其视为 45 + 45 + 21 = 111。

我在这里做错了什么？

Answer 1

new 是写入你的表的记录，其中m1 和m2 都是33。

所以 t2.aggm + new.m1 + new.m2 是 45 + 33 + 33 = 111。

为了得到你想要的结果(即 aggm = 45 + 10 + 11)，你应该将 AFTER UPDATE 案例移动到一个单独的触发器，包含这样的东西:

UPDATE t2 SET aggm = aggm + (new.m1 - old.m1) + (new.m2 - old.m2);