Обсуждение: is it a bug in rule system?

hi all,

see query below:

create table ruleTest(id integer, name text);

create or replace rule ruleTest_insert_rule AS on insert to ruleTest
where exists(select 1 from ruleTest where ruleTest.name = NEW.name)
do instead (update ruleTest set id = id+1 where ruleTest.name = NEW.name);

create unique index ruletest_name_uniq_idx on ruletest using btree (name);

laser=# insert into ruletest(id,name) values(1, 'laser0');
INSERT 0 1
laser=# select * from ruletest;
 id |  name
----+--------
  2 | laser0

I think the id should be 1, cause the rule condition should only affect
exist row,
but ISTM the rule act like a DO ALSO rule: insert the row first, then
update it.

any hints?

thanks and best regards

-laser

am  Wed, dem 28.05.2008, um 15:56:22 +0800 mailte laser folgendes:
> hi all,
>
> see query below:
>
> create table ruleTest(id integer, name text);
>
> create or replace rule ruleTest_insert_rule AS on insert to ruleTest
> where exists(select 1 from ruleTest where ruleTest.name = NEW.name)
> do instead (update ruleTest set id = id+1 where ruleTest.name = NEW.name);
>
> I think the id should be 1, cause the rule condition should only affect
> exist row,
> but ISTM the rule act like a DO ALSO rule: insert the row first, then
> update it.
>
> any hints?

Yes, use a TRIGGER instead a RULE for such tasks.


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

A. Kretschmer wrote:
> am  Wed, dem 28.05.2008, um 15:56:22 +0800 mailte laser folgendes:
>
>> hi all,
>>
>> see query below:
>>
>> create table ruleTest(id integer, name text);
>>
>> create or replace rule ruleTest_insert_rule AS on insert to ruleTest
>> where exists(select 1 from ruleTest where ruleTest.name = NEW.name)
>> do instead (update ruleTest set id = id+1 where ruleTest.name = NEW.name);
>>
>> I think the id should be 1, cause the rule condition should only affect
>> exist row,
>> but ISTM the rule act like a DO ALSO rule: insert the row first, then
>> update it.
>>
>> any hints?
>>
>
> Yes, use a TRIGGER instead a RULE for such tasks.
>
>

thanks, good to know it's a bug and we'll try TRIGGER approach.

thanks and best regards

-laser

On Wed, May 28, 2008 at 04:59:33PM +0800, laser wrote:
> >Yes, use a TRIGGER instead a RULE for such tasks.
>
> thanks, good to know it's a bug and we'll try TRIGGER approach.

It's not a bug, just your misunderstanding of how rules work. Rules
rewrite queries. What happen in your case is because of the condition
your query will be split into two: once with your INSERT with a NOT
EXISTS (subquery) and once as an UPDATE with the condition EXISTS
(subquery).

So the first query will insert with id=1 and then the update sees this
row and updates it to 2.

What this says is that rules are the wrong tool for what you're trying
to do. Conditional rules are powerful but not appropriate here.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.

>
> It's not a bug, just your misunderstanding of how rules work. Rules
> rewrite queries. What happen in your case is because of the condition
> your query will be split into two: once with your INSERT with a NOT
> EXISTS (subquery) and once as an UPDATE with the condition EXISTS
> (subquery).
>
> So the first query will insert with id=1 and then the update sees this
> row and updates it to 2.
>
> What this says is that rules are the wrong tool for what you're trying
> to do. Conditional rules are powerful but not appropriate here.
>
> Have a nice day,
>

Thanks to clarify, if it's a misunderstanding of rule, then I'll some how
confuse with DO INSTEAD vs. DO ALSO rule, isn't DO INSTEAD will
replace original INSERT with the one provided in CREATE RULE?
...after some rethinking, can I understand what happened as below step?

1, INSERT will be rewrite into a INSERT with a EXIST condition clause
and a UPDATE statement;
2, when NOT EXISTS, INSERT succeed, and the query tree in 1 proceed to
UPDATE;
3, the UPDATE saw the INSERT in 2, then UPDATE it;

if so, then I understand what happed there, and it's surly not a bug but
a mis-use of rule.

thanks and best regards

-laser

laser wrote:
>
>>
>> It's not a bug, just your misunderstanding of how rules work. Rules
>> rewrite queries. What happen in your case is because of the condition
>> your query will be split into two: once with your INSERT with a NOT
>> EXISTS (subquery) and once as an UPDATE with the condition EXISTS
>> (subquery).
>>
>> So the first query will insert with id=1 and then the update sees this
>> row and updates it to 2.
>>
>> What this says is that rules are the wrong tool for what you're trying
>> to do. Conditional rules are powerful but not appropriate here.
>>
>> Have a nice day,
>>
>
> Thanks to clarify, if it's a misunderstanding of rule, then I'll some how
> confuse with DO INSTEAD vs. DO ALSO rule, isn't DO INSTEAD will
> replace original INSERT with the one provided in CREATE RULE?
> ...after some rethinking, can I understand what happened as below step?
>
> 1, INSERT will be rewrite into a INSERT with a EXIST condition clause
> and a UPDATE statement;
> 2, when NOT EXISTS, INSERT succeed, and the query tree in 1 proceed to
> UPDATE;
> 3, the UPDATE saw the INSERT in 2, then UPDATE it;
>
> if so, then I understand what happed there, and it's surly not a bug but
> a mis-use of rule.
It took me a while to get my head around it but essentially the query
tree gets rewritten to

1. execute original statement where not rule.condition
2. execute rule body where rule.condition

In this case, part 1 changes the result of the rule condition for part 2
making both execute. I.E. rule condition is evaluated every time it is
referenced rather than being kept constant for both query tree entries.

To do it in a trigger, you would have to do a before insert trigger that
looks like

   update ruleTest set id = id+1 where ruleTest.name = NEW.name;
   if rowsaffected > 0 then
       return null;
   end if;
   return new;

klint.

--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350

Ph: 02 6773 3789
Fax: 02 6773 3266
EMail: kgore4@une.edu.au