Thread: BUG #7924: Trigger update function don't take correct values

The following bug has been logged on the website:

Bug reference:      7924
Logged by:          Maria
Email address:      mv.gonzalez@cir.es
PostgreSQL version: Unsupported/Unknown
Operating system:   RedHat
Description:        =


Hi!

I have a trigger that when you update a value on a table, makes an insert
into another table.

The trigger looks like the following:

BEGIN
set datestyle to 'sql';
set datestyle to 'european';
IF (TG_OP =3D 'UPDATE' and (OLD.situation!=3DNEW.situation)) THEN
INSERT INTO table_B VALUES (OLD.id,NEW.situation,NOW());
END IF;
RETURN NULL;
END;

For exemple, If I do the next querys:
--> update table_A set situation=3D'Started' where id=3D'23';
On table_A appears:
  id    | situation =

--------+-----------
  23    | Started

On table_B appears:
  id    | situation | when
--------+-----------+---------------------
  23    | Started   | 2013-03-08 12:33:35


--> update table_A set situation=3D'Waiting' where id=3D'23';
On table_A appears:
  id    | situation =

--------+-----------
  23    | Waiting

On table_B appears:
  id    | situation | when
--------+-----------+---------------------
  23    | Started   | 2013-03-08 12:33:35
  23    | Started   | 2013-03-08 12:33:37

I don't understand why after the second update, the insert done on table_B
is incorrect, and has the same values that the first one. =


I've been looking for some kind of explanation like cache or something, but
I couldn't find anything.

Thanks in advance.

Maria

"mv.gonzalez@cir.es" <mv.gonzalez@cir.es> wrote:=0A=0A> On table_B appears:=
=0A>=A0=A0 id=A0=A0=A0 | situation | when=0A> --------+-----------+--------=
-------------=0A>=A0=A0 23=A0=A0=A0 | Started=A0 | 2013-03-08 12:33:35=0A>=
=A0=A0 23=A0=A0=A0 | Started=A0 | 2013-03-08 12:33:37=0A>=0A> I don't under=
stand why after the second update, the insert done on table_B=0A> is incorr=
ect, and has the same values that the first one.=0A>=0A> I've been looking =
for some kind of explanation like cache or something, but=0A> I couldn't fi=
nd anything.=0A=0AIt helps to provide a self-containted test case like this=
:=0A=0Atest=3D# CREATE TABLE table_a (id int PRIMARY KEY, situation text NO=
T NULL);=0ACREATE TABLE=0Atest=3D# INSERT INTO table_a VALUES (23, 'Unknown=
');=0AINSERT 0 1=0Atest=3D# CREATE TABLE table_b (id int NOT NULL, sitation=
 text NOT NULL, "when" timestamptz NOT NULL);=0ACREATE TABLE=0Atest=3D# CRE=
ATE FUNCTION log_func()=0Atest-#=A0=A0 RETURNS TRIGGER=0Atest-#=A0=A0 LANGU=
AGE plpgsql=0Atest-# AS $$=0Atest$# BEGIN=0Atest$#=A0=A0 set datestyle to '=
sql';=0Atest$#=A0=A0 set datestyle to 'european';=0Atest$#=A0=A0 IF (TG_OP =
=3D 'UPDATE' and (OLD.situation!=3DNEW.situation)) THEN=0Atest$#=A0=A0=A0=
=A0 INSERT INTO table_B VALUES (OLD.id,NEW.situation,NOW());=0Atest$#=A0=A0=
 END IF;=0Atest$#=A0=A0 RETURN NULL;=0Atest$# END;=0Atest$# $$;=0ACREATE FU=
NCTION=0Atest=3D# CREATE TRIGGER log_trig=0Atest-#=A0=A0 AFTER UPDATE ON ta=
ble_a=0Atest-#=A0=A0 FOR EACH ROW EXECUTE PROCEDURE log_func();=0ACREATE TR=
IGGER=0Atest=3D# update table_A set situation=3D'Started' where id=3D'23';=
=0AUPDATE 1=0Atest=3D# select * from table_a;=0A=A0id | situation=0A----+--=
---------=0A=A023 | Started=0A(1 row)=0A=0Atest=3D# select * from table_b;=
=0A=A0id | sitation |=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 when=A0=A0=A0=
=A0=A0=A0=A0=A0=A0=A0=A0 =A0=0A----+----------+----------------------------=
----=0A=A023 | Started=A0 | 08/03/2013 13:01:08.169213 CST=0A(1 row)=0A=0At=
est=3D# update table_A set situation=3D'Waiting' where id=3D'23';=0AUPDATE =
1=0Atest=3D# select * from table_a;=0A=A0id | situation=0A----+-----------=
=0A=A023 | Waiting=0A(1 row)=0A=0Atest=3D# select * from table_b;=0A=A0id |=
 sitation |=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 when=A0=A0=A0=A0=A0=A0=
=A0=A0=A0=A0=A0 =A0=0A----+----------+--------------------------------=0A=
=A023 | Started=A0 | 08/03/2013 13:01:08.169213 CST=0A=A023 | Waiting=A0 | =
08/03/2013 13:01:08.179754 CST=0A(2 rows)=0A=0AHow about showing us yours?=
=0A=0A--=0AKevin Grittner=0AEnterpriseDB: http://www.enterprisedb.com=0AThe=
 Enterprise PostgreSQL Company