From: | Kevin Grittner <kgrittn(at)ymail(dot)com> |
---|---|
To: | "mv(dot)gonzalez(at)cir(dot)es" <mv(dot)gonzalez(at)cir(dot)es>, "pgsql-bugs(at)postgresql(dot)org" <pgsql-bugs(at)postgresql(dot)org> |
Subject: | Re: BUG #7924: Trigger update function don't take correct values |
Date: | 2013-03-08 19:03:33 |
Message-ID: | 1362769413.51869.YahooMailNeo@web162901.mail.bf1.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
"mv(dot)gonzalez(at)cir(dot)es" <mv(dot)gonzalez(at)cir(dot)es> wrote:
> 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.
It helps to provide a self-containted test case like this:
test=# CREATE TABLE table_a (id int PRIMARY KEY, situation text NOT NULL);
CREATE TABLE
test=# INSERT INTO table_a VALUES (23, 'Unknown');
INSERT 0 1
test=# CREATE TABLE table_b (id int NOT NULL, sitation text NOT NULL, "when" timestamptz NOT NULL);
CREATE TABLE
test=# CREATE FUNCTION log_func()
test-# RETURNS TRIGGER
test-# LANGUAGE plpgsql
test-# AS $$
test$# BEGIN
test$# set datestyle to 'sql';
test$# set datestyle to 'european';
test$# IF (TG_OP = 'UPDATE' and (OLD.situation!=NEW.situation)) THEN
test$# INSERT INTO table_B VALUES (OLD.id,NEW.situation,NOW());
test$# END IF;
test$# RETURN NULL;
test$# END;
test$# $$;
CREATE FUNCTION
test=# CREATE TRIGGER log_trig
test-# AFTER UPDATE ON table_a
test-# FOR EACH ROW EXECUTE PROCEDURE log_func();
CREATE TRIGGER
test=# update table_A set situation='Started' where id='23';
UPDATE 1
test=# select * from table_a;
id | situation
----+-----------
23 | Started
(1 row)
test=# select * from table_b;
id | sitation | when
----+----------+--------------------------------
23 | Started | 08/03/2013 13:01:08.169213 CST
(1 row)
test=# update table_A set situation='Waiting' where id='23';
UPDATE 1
test=# select * from table_a;
id | situation
----+-----------
23 | Waiting
(1 row)
test=# select * from table_b;
id | sitation | when
----+----------+--------------------------------
23 | Started | 08/03/2013 13:01:08.169213 CST
23 | Waiting | 08/03/2013 13:01:08.179754 CST
(2 rows)
How about showing us yours?
--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
From | Date | Subject | |
---|---|---|---|
Next Message | Peter Kroon | 2013-03-08 20:36:55 | Re: Nested xmlagg doesn't give a result 9.2.3 |
Previous Message | Pavel Stehule | 2013-03-08 16:58:42 | Re: [BUGS] BUG #7873: pg_restore --clean tries to drop tables that don't exist |