[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-general
Subject: [GENERAL] Odd behaviour in update rule
From: Denis Gasparin <denis () edistar ! com>
Date: 2010-07-07 9:32:53
Message-ID: 26416203.29386.1278495173324.JavaMail.root () mailserver ! edistar ! com
[Download RAW message or body]
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
[Attachment #3 (text/html)]
<html><head><style type='text/css'>p { margin: 0; }</style></head><body><div \
style='font-family: Arial; font-size: 12pt; color: #000000'>Hi.<br><br>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.<br><br>The following sql statements prepare the table, view \
and rule.<br><br style="font-family: Courier \
New,courier,monaco,monospace,sans-serif;"><font size="2"><span style="font-family: \
Courier New,courier,monaco,monospace,sans-serif;"> create table main \
(</span><br style="font-family: Courier \
New,courier,monaco,monospace,sans-serif;"><span style="font-family: Courier \
New,courier,monaco,monospace,sans-serif;"> id integer not null \
primary key,</span><br style="font-family: Courier \
New,courier,monaco,monospace,sans-serif;"><span style="font-family: Courier \
New,courier,monaco,monospace,sans-serif;"> value integer not \
null</span><br style="font-family: Courier \
New,courier,monaco,monospace,sans-serif;"><span style="font-family: Courier \
New,courier,monaco,monospace,sans-serif;"> );</span><br style="font-family: \
Courier New,courier,monaco,monospace,sans-serif;"><br><span style="font-family: \
Courier New,courier,monaco,monospace,sans-serif;"> create view view_main \
(id,value) as select * from main;</span><br style="font-family: Courier \
New,courier,monaco,monospace,sans-serif;"><br style="font-family: Courier \
New,courier,monaco,monospace,sans-serif;"><span style="font-family: Courier \
New,courier,monaco,monospace,sans-serif;"> create rule update_view_main as on \
update to view_main do instead update main set value=new.value where id = \
old.id;</span><br style="font-family: Courier \
New,courier,monaco,monospace,sans-serif;"><br style="font-family: Courier \
New,courier,monaco,monospace,sans-serif;"><span style="font-family: Courier \
New,courier,monaco,monospace,sans-serif;"> insert into main \
values(1,1);</span><br style="font-family: Courier \
New,courier,monaco,monospace,sans-serif;"></font><br>In table main we have only one \
record with id=1 and value=1.<br><br>Now we suppose that two clients connect \
simultaneously to the database and execute the following statements in parallel \
(CLIENT A first then CLIENT B).<br><br><br><font size="2"><span style="font-family: \
Courier New,courier,monaco,monospace,sans-serif;"></span><br style="font-family: \
Courier New,courier,monaco,monospace,sans-serif;"><span style="font-family: Courier \
New,courier,monaco,monospace,sans-serif;">CLIENT A: begin; \
\
</span><br style="font-family: Courier \
New,courier,monaco,monospace,sans-serif;"><span style="font-family: Courier \
New,courier,monaco,monospace,sans-serif;">CLIENT B: begin;</span><br \
style="font-family: Courier New,courier,monaco,monospace,sans-serif;"><span \
style="font-family: Courier New,courier,monaco,monospace,sans-serif;">CLIENT A: \
update view_main set value=value+1 where id=1; \
</span><br style="font-family: Courier \
New,courier,monaco,monospace,sans-serif;"><span style="font-family: Courier \
New,courier,monaco,monospace,sans-serif;">CLIENT B: update view_main set \
value=value+1 where id=1; --waits for client A to commit changes</span><br \
style="font-family: Courier New,courier,monaco,monospace,sans-serif;"><span \
style="font-family: Courier New,courier,monaco,monospace,sans-serif;">CLIENT A: \
commit; \
</span><br \
style="font-family: Courier New,courier,monaco,monospace,sans-serif;"><span \
style="font-family: Courier New,courier,monaco,monospace,sans-serif;">CLIENT B: \
commit;</span><br style="font-family: Courier \
New,courier,monaco,monospace,sans-serif;"><br style="font-family: Courier \
New,courier,monaco,monospace,sans-serif;"><span style="font-family: Courier \
New,courier,monaco,monospace,sans-serif;">CLIENT A: select * from \
view_main;</span><br style="font-family: Courier \
New,courier,monaco,monospace,sans-serif;"><span style="font-family: Courier \
New,courier,monaco,monospace,sans-serif;">--expected value = 2</span><br \
style="font-family: Courier New,courier,monaco,monospace,sans-serif;"><span \
style="font-family: Courier New,courier,monaco,monospace,sans-serif;">--returned \
value = 2<br><br></span></font><font size="2"><span style="font-family: Courier \
New,courier,monaco,monospace,sans-serif;">CLIENT A: select * from \
view_main;</span><br style="font-family: Courier \
New,courier,monaco,monospace,sans-serif;"> <span style="font-family: Courier \
New,courier,monaco,monospace,sans-serif;">--expected value = 2</span><br \
style="font-family: Courier New,courier,monaco,monospace,sans-serif;"> <span \
style="font-family: Courier New,courier,monaco,monospace,sans-serif;">--returned \
value = 3<br> </span></font><br><br>I would expect that the two updates behaves \
exactly as a direct update on main... (returned value=3) but this is not the \
case...<br>Is it the correct behaviour?<br><br>Thank \
you,<br>Denis<span></span><br></div></body></html>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic