Re: Transactional issue that begs for explanation - Mailing list pgsql-novice

On 10 September 2010 09:49, Mladen Gogala <mladen.gogala@vmsinfo.com> wrote:
> 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.

This is quite odd.  I've done exactly the same thing on 8.4.4 Gentoo
x64 and it returns the expected result so I haven't been able to
recreate it.  What does the table contain after that series of events?

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935