Transactional issue that begs for explanation

Поиск
Список
Период
Сортировка
От Mladen Gogala
Тема Transactional issue that begs for explanation
Дата
Msg-id 4C89F12A.3000005@vmsinfo.com
обсуждение исходный текст
Ответы Re: Transactional issue that begs for explanation  (Thom Brown <thom@linux.com>)
Re: Transactional issue that begs for explanation  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-novice
I am having problems explaining the following course of events:

I created  table test_keys(key int,val varchar(10));  No PK, no indexes.
I populated the table using the following SQL:
insert into test_keys values(1,'Key1');
insert into test_keys values(2,'Key2');
insert into test_keys values(3,'Key3');
insert into test_keys values(4,'Key4');
insert into test_keys values(5,'Key5');
insert into test_keys values(6,'Key6');
insert into test_keys values(7,'Key7');
insert into test_keys values(8,'Key8');
insert into test_keys values(9,'Key9');
insert into test_keys values(10,'Key10');
insert into test_keys values(11,'Key11');
insert into test_keys values(12,'Key12');
insert into test_keys values(13,'Key13');
insert into test_keys values(14,'Key14');
insert into test_keys values(15,'Key15');
insert into test_keys values(16,'Key16');
insert into test_keys values(17,'Key17');
insert into test_keys values(18,'Key18');
insert into test_keys values(19,'Key19');
insert into test_keys values(20,'Key20');

To make the story more interesting, I added the following:
CREATE or REPLACE FUNCTION logtrg() RETURNS trigger AS $$
open(STDOUT,">>/tmp/logfile") or die("Cannot open log:$!\n");
$key=$_TD->{old}{key};
$val=$_TD->{old}{val};
print "Firing on: $key $val\n";
return;
$$ LANGUAGE plperlu;

CREATE TRIGGER log_upd BEFORE UPDATE on test_keys
FOR EACH ROW EXECUTE PROCEDURE logtrg();

Essentially, I added  trigger that records the values that the trigger
fires upon and puts those values into /tmp/logfile

Next, opened 2 sessions and executed the following:
Session 1:                                                   Session 2:
----------------
----------------
begin;                                                         begin;
update test_keys                                        update test_keys
set val='EVEN'                                          set val='DIV5'
where key%2=0;                                       where key%5=0;
rollback;                                                    commit;

Here is the content of my logfile:


root@ubuntu:~# tail -f /tmp/logfile
Firing on: 2 Key2
Firing on: 4 Key4
Firing on: 6 Key6
Firing on: 8 Key8
Firing on: 10 Key10
Firing on: 12 Key12
Firing on: 14 Key14
Firing on: 16 Key16
Firing on: 18 Key18
Firing on: 20 EVEN
Firing on: 5 Key5
Firing on: 10 Key10
Firing on: 15 Key15

Question: where did "EVEN" on the key 20 come from? The first
transaction was rolled back, the 2nd transaction shouldn't have seen any
changes made by the first transaction. I am using PgSQL 8.4.4 on Ubuntu
10, 32bit version (laptop).

I repeated the experiment several times, and this happens rather
consistently.

--
Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
www.vmsinfo.com
The Leader in integrated Media Intelligence Solutions


В списке pgsql-novice по дате отправления:

Предыдущее
От: Thom Brown
Дата:
Сообщение: Re: Postgresql & Deadlocks
Следующее
От: Thom Brown
Дата:
Сообщение: Re: Transactional issue that begs for explanation