Hi.
I have an odd behaviour on an update rule in postgresql 8.2 and i'd like to know if the behaviour is as expected or not.
The following sql statements prepare the table, view and rule.
create table main (
id integer not null primary key,
value integer not null
);
create view view_main (id,value) as select * from main;
create rule update_view_main as on update to view_main do instead update main set value=new.value where id = old.id;
insert into main values(1,1);
In table main we have only one record with id=1 and value=1.
Now we suppose that two clients connect simultaneously to the database and execute the following statements in parallel (CLIENT A first then CLIENT B).
CLIENT A: begin;
CLIENT B: begin;
CLIENT A: update view_main set value=value+1 where id=1;
CLIENT B: update view_main set value=value+1 where id=1; --waits for client A to commit changes
CLIENT A: commit;
CLIENT B: commit;
CLIENT A: select * from view_main;
--expected value = 2
--returned value = 2
CLIENT A: select * from view_main;
--expected value = 2
--returned value = 3
I would expect that the two updates behaves exactly as a direct update on main... (returned value=3) but this is not the case...
Is it the correct behaviour?
Thank you,
Denis