CREATE TABLE t
( x integer,
CREATE TRIGGER t_buffer
FOR EACH ROW
EXECUTE PROCEDURE t_buffer();
The trigger function is extremely simple and it only displays the values:
CREATE OR REPLACE FUNCTION t_buffer()
RETURNS trigger AS
raise notice 'old.x = ',OLD.x,',old.y=',OLD.y;
raise notice 'old.x = ',NEW.x,',old.y=',NEW.y;
LANGUAGE plpgsql VOLATILE
I inserted the triple (1,1,1) into the table and then opened 2 sessions.
The 1st session executes like this:
Time: 0.108 ms
scott=# update t set x = x+1;
NOTICE: old.x = 1,old.y=1
NOTICE: new.x = 2,new.y=1
Time: 0.683 ms
Here I waited and opened another session.
Time: 16.187 ms
The problem occurs when I executed the 2nd transaction:
Time: 0.117 ms
scott=# update t set x = x+2 where x > 0;
The transaction was blocked until I didn't commit the 1st transaction.
NOTICE: old.x = 2,old.y=1
NOTICE: new.x = 4,new.y=1
Time: 7780.669 ms
Time: 28.557 ms
Where is the problem? The problem lies in the fact that the 2nd
transaction should have only seen the changes committed before it has
begun, ie, x=1. The 1st transaction has committed AFTER the 2nd
transaction has begun but the 2nd transaction still sees x=2. The only
logical explanation is that the 2nd transaction has restarted when it
discovered the changed block, but the trigger did not fire twice.
Anybody care to sched some light on this? If that looks familiar, here
is the motivation for such an exotic test:
The result is exactly the same, but the trigger behavior isn't.
Sr. Oracle DBA
New York, NY 10036
The Leader in Integrated Media Intelligence Solutions