CODEWITHSUNDEEP
postgresqltriggers
kumar_m_kiran
kumar_m_kiran

Reputation: 4022

postgresql : Trigger leading to incorrect evaluation

Table and schema Setup

Consider the below tables and triggers, 

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 : Main table containing m1,m2 columns (marks) for c1,c2,c3 identifier.
t2 : I want this table to be aggregation of m1 and m2, for all unique values (c1,c2,c3) in t1.

Problem

I see that the aggregated value in t2 (i.e aggm) getting doubled for every insert I am doing.

Output 

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)

Question : I am not expecting value 111 in t2. Instead I am expecting 66 here (i.e 23 + 22 (1st insert) and 10 + 11 (2nd insert)).
I think somehow during second insert it is considering it as 45 + 45 + 21 = 111.

What is the mistake I am doing here?

Upvotes: 1

Views: 53

Answers (1)

Nick Barnes
Nick Barnes

Reputation: 21306

new is the record which was written to your table, in which m1 and m2 are both 33.

So t2.aggm + new.m1 + new.m2 is 45 + 33 + 33 = 111.

To get the result you want (i.e. aggm = 45 + 10 + 11), you should move the AFTER UPDATE case to a separate trigger, containing something like this:

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

Upvotes: 1

Related Questions