Odd behaviour in update rule

From: Denis Gasparin <denis(at)edistar(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Odd behaviour in update rule
Date: 2010-07-07 09:32:53
Message-ID: 26416203.29386.1278495173324.JavaMail.root@mailserver.edistar.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Browse pgsql-general by date

  From Date Subject
Next Message Sreelatha G 2010-07-07 10:12:23 Re: [SQL] How to Get Column Names from the Table
Previous Message Jayadevan M 2010-07-07 09:29:46 Re: How to Get Column Names from the Table