Обсуждение: Multiple Rules :: Postgres Is confused !!

Najib Abi Fadel wrote:
>
> WHY IS THAT HAPPENNING ??
>
> I can provide more details if anyone is ready to help ...

You will need to provide if anyone is to help.

One thing you need to consider is that rules are basically like macros,
with all the issues that can have.

--
   Richard Huxton
   Archonet Ltd

Details:

I have a table "transactions_sco" and a view "transactions_sco_v" defined as
:
create view transactions_sco_v as select * from transactions_sco;

I have the following Rules:

CREATE RULE transactions_sco_up1 AS ON
 UPDATE TO transactions_sco_v DO INSTEAD UPDATE transactions_sco SET traiter
= new.traiter WHERE
 (((((transactions_sco.cursus_id = old.cursus_id) AND
(transactions_sco.vers_id = old.vers_id))
AND (transactions_sco.traiter = old.traiter)) AND
(transactions_sco.code_type_academic = old.cod
e_type_academic)) AND (transactions_sco.cod_etu = old.cod_etu));

CREATE RULE transactions_sco_up2 AS ON
 UPDATE TO transactions_sco_v DO INSTEAD UPDATE transactions_sco SET traiter
= new.traiter WHERE
 ((((transactions_sco.cursus_id = old.cursus_id) AND
(transactions_sco.vers_id = old.vers_id)) A
ND (transactions_sco.traiter = old.traiter)) AND
(transactions_sco.code_type_academic = old.code
_type_academic));


CREATE RULE transactions_sco_up8 AS ON
 UPDATE TO transactions_sco_v DO INSTEAD UPDATE transactions_sco SET traiter
= new.traiter WHERE
 (transactions_sco.id = old.id);


Now look what is happening:

SELECT count(1) from transactions_sco where traiter='f';
count
-------
 17591

update transactions_sco_v set traiter='t' where id = 53597;
UPDATE 1

SELECT count(1) from transactions_sco where traiter='f';
 count
-------
 17589

AS YOU CAN SEE TWO ROWS WHERE UPDATED INSTEAD OF ONE !!
THE COMMON THINGS BETWEEN THE TWO ROWS  IS THAT THE FIELDS: cod_etu,
cursus_id,vers_id,code_type_academic  are the same

IF I REMOVE the rules transactions_sco_up1  and transactions_sco_up2  the
update works fine ...

Thx for any help.

Najib Abi Fadel wrote:
> Details:
>
> I have a table "transactions_sco" and a view "transactions_sco_v" defined as
> :
> create view transactions_sco_v as select * from transactions_sco;
>
> I have the following Rules:
>
> CREATE RULE transactions_sco_up1 AS ON
>  UPDATE TO transactions_sco_v DO INSTEAD UPDATE transactions_sco SET traiter
> = new.traiter WHERE
>  (((((transactions_sco.cursus_id = old.cursus_id) AND
> (transactions_sco.vers_id = old.vers_id))
> AND (transactions_sco.traiter = old.traiter)) AND
> (transactions_sco.code_type_academic = old.cod
> e_type_academic)) AND (transactions_sco.cod_etu = old.cod_etu));
>
> CREATE RULE transactions_sco_up2 AS ON
>  UPDATE TO transactions_sco_v DO INSTEAD UPDATE transactions_sco SET traiter
> = new.traiter WHERE
>  ((((transactions_sco.cursus_id = old.cursus_id) AND
> (transactions_sco.vers_id = old.vers_id)) A
> ND (transactions_sco.traiter = old.traiter)) AND
> (transactions_sco.code_type_academic = old.code
> _type_academic));

OK, so upd1 compares:
  (cursus_id, vers_id, traiter, code_type_academic, cod_etu)
upd2 compares:
  (cursus_id, vers_id, traiter, code_type_academic)

This means upd1 is redundant since any rows affected by upd1 *must* be
affected by upd2.

> CREATE RULE transactions_sco_up8 AS ON
>  UPDATE TO transactions_sco_v DO INSTEAD UPDATE transactions_sco SET traiter
> = new.traiter WHERE
>  (transactions_sco.id = old.id);

OK, this one just compares "id", which is presumably the primary key and
unique.

> Now look what is happening:
>
> SELECT count(1) from transactions_sco where traiter='f';
> count
> -------
>  17591
>
> update transactions_sco_v set traiter='t' where id = 53597;
> UPDATE 1
>
> SELECT count(1) from transactions_sco where traiter='f';
>  count
> -------
>  17589
>
> AS YOU CAN SEE TWO ROWS WHERE UPDATED INSTEAD OF ONE !!
> THE COMMON THINGS BETWEEN THE TWO ROWS  IS THAT THE FIELDS: cod_etu,
> cursus_id,vers_id,code_type_academic  are the same

Because that's what you asked upd1/2 to do for you. To see what is
happening, try selecting row id=53597 then manually running each rule
yourself, substituting in the OLD.foo from your selected row. You should
find that there are two rows that match 53597 on (cursus_id, vers_id,
traiter, code_type_academic) - itself and one other.

--
   Richard Huxton
   Archonet Ltd

> This means upd1 is redundant since any rows affected by upd1 *must* be
> affected by upd2.
OK

> > CREATE RULE transactions_sco_up8 AS ON
> >  UPDATE TO transactions_sco_v DO INSTEAD UPDATE transactions_sco SET
traiter
> > = new.traiter WHERE
> >  (transactions_sco.id = old.id);
>
> OK, this one just compares "id", which is presumably the primary key and
> unique.
Right "id" is the primary key


> > Now look what is happening:
> >
> > SELECT count(1) from transactions_sco where traiter='f';
> > count
> > -------
> >  17591
> >
> > update transactions_sco_v set traiter='t' where id = 53597;
> > UPDATE 1
> >
> > SELECT count(1) from transactions_sco where traiter='f';
> >  count
> > -------
> >  17589
> >
> > AS YOU CAN SEE TWO ROWS WHERE UPDATED INSTEAD OF ONE !!
> > THE COMMON THINGS BETWEEN THE TWO ROWS  IS THAT THE FIELDS: cod_etu,
> > cursus_id,vers_id,code_type_academic  are the same
>
> Because that's what you asked upd1/2 to do for you. To see what is
> happening, try selecting row id=53597 then manually running each rule
> yourself, substituting in the OLD.foo from your selected row. You should
> find that there are two rows that match 53597 on (cursus_id, vers_id,
> traiter, code_type_academic) - itself and one other.

Sorry, I didn't understand the manuel test procedure

What is happening here? I am doing an update and the condition is on the ID
and it is corresponding to the last Rule so why should the other rules
interfer.

Thanx for your help
Najib.

> > Because that's what you asked upd1/2 to do for you. To see what is
> > happening, try selecting row id=53597 then manually running each rule
> > yourself, substituting in the OLD.foo from your selected row. You should
> > find that there are two rows that match 53597 on (cursus_id, vers_id,
> > traiter, code_type_academic) - itself and one other.
>
> Sorry, I didn't understand the manuel test procedure
>
> What is happening here? I am doing an update and the condition is on the ID
> and it is corresponding to the last Rule so why should the other rules
> interfer.

Here you misunderstand. You've got an UPDATE on that table set to
trigger a RULE. *All* the rules. Postgresql is not just going to pick
one based on what it thinks you might mean. Since you've got 3 rules
for UPDATE on that table, I imagine all three rules are getting fired.
With DO INSTEAD I expect either the first one or the last one to win, I
don't know enough about the specifics.

Hope this helps,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

Najib Abi Fadel wrote:
>>>
>>>AS YOU CAN SEE TWO ROWS WHERE UPDATED INSTEAD OF ONE !!
>>>THE COMMON THINGS BETWEEN THE TWO ROWS  IS THAT THE FIELDS: cod_etu,
>>>cursus_id,vers_id,code_type_academic  are the same
>>
>>Because that's what you asked upd1/2 to do for you. To see what is
>>happening, try selecting row id=53597 then manually running each rule
>>yourself, substituting in the OLD.foo from your selected row. You should
>>find that there are two rows that match 53597 on (cursus_id, vers_id,
>>traiter, code_type_academic) - itself and one other.
>
>
> Sorry, I didn't understand the manuel test procedure
>
> What is happening here? I am doing an update and the condition is on the ID
> and it is corresponding to the last Rule so why should the other rules
> interfer.

ALL rules get executed. Conditions get combined (actually, parse trees
get merged).

=== BEGIN rule_test.sql ===
CREATE TABLE foo (a int4 PRIMARY KEY, b text);

COPY foo FROM stdin;
1    aaa
2    bbb
3    ccc
4    aaa
5    bbb
6    ccc
\.

CREATE VIEW foo_v AS SELECT * FROM foo WHERE b='bbb';

CREATE RULE foo_v_upd1 AS ON UPDATE TO foo_v DO INSTEAD
UPDATE foo SET b = 'z' || NEW.b WHERE a = OLD.a;

SELECT * FROM foo ORDER BY a;

UPDATE foo_v SET b='xxx';

SELECT * FROM foo ORDER BY a;
=== END rule_test.sql ===

This will update 2 rows (those with b='bbb') since we impose no WHERE in
our update but the view does. The OLD/NEW refer to target rows
before/after the change.

Does that make things clearer?
--
   Richard Huxton
   Archonet Ltd

> ALL rules get executed. Conditions get combined (actually, parse trees
> get merged).
>
> === BEGIN rule_test.sql ===
> CREATE TABLE foo (a int4 PRIMARY KEY, b text);
>
> COPY foo FROM stdin;
> 1 aaa
> 2 bbb
> 3 ccc
> 4 aaa
> 5 bbb
> 6 ccc
> \.
>
> CREATE VIEW foo_v AS SELECT * FROM foo WHERE b='bbb';
>
> CREATE RULE foo_v_upd1 AS ON UPDATE TO foo_v DO INSTEAD
> UPDATE foo SET b = 'z' || NEW.b WHERE a = OLD.a;
>
> SELECT * FROM foo ORDER BY a;
>
> UPDATE foo_v SET b='xxx';
>
> SELECT * FROM foo ORDER BY a;
> === END rule_test.sql ===
>
> This will update 2 rows (those with b='bbb') since we impose no WHERE in
> our update but the view does. The OLD/NEW refer to target rows
> before/after the change.
>
> Does that make things clearer?
> --

If i am getting this right the update command: "UPDATE foo_v SET b='xxx';"
will first get the "a" values (2 and 5)  from the view and then execute the
update on this rows.
?

So im my case, when i call the update : "update transactions_sco_v set
traiter='t' where id = 53597;"
IF
    select cursus_id, vers_id, traiter, code_type_academic, cod_etu from
transactions_sco_v where id = 53597;
Returns
 -[ RECORD 1 ]------+-------
cursus_id          | 62
vers_id            | 6
traiter            | f
code_type_academic | ECT
cod_etu            | 041400

this will execute the 3 update corresponding to the 3 rules i defined for
the values of cursus_id, vers_id, traiter, code_type_academic, cod_etu
returned above !!!
and i will have the following 3 updates executes !

UPDATE transactions_sco SET traiter = 't' WHERE cursus_id = 62 AND vers_id =
6 traiter = 'f' AND code_type_academic = 'ECT' and cod_etu = '041400';

UPDATE transactions_sco SET traiter = 't' WHERE cursus_id = 62 AND vers_id =
6 traiter = 'f' AND code_type_academic = 'ECT';


UPDATE transactions_sco SET traiter = 't' WHERE id = 53597;


I AM GETTING THIS RIGHT ??

THANX AGAIN FOR YOUR HELP.

Najib Abi Fadel wrote:
>>ALL rules get executed. Conditions get combined (actually, parse trees
>>get merged).

> If i am getting this right the update command: "UPDATE foo_v SET b='xxx';"
> will first get the "a" values (2 and 5)  from the view and then execute the
> update on this rows.
> ?
>
> So im my case, when i call the update : "update transactions_sco_v set
> traiter='t' where id = 53597;"
> IF
>     select cursus_id, vers_id, traiter, code_type_academic, cod_etu from
> transactions_sco_v where id = 53597;
> Returns
>  -[ RECORD 1 ]------+-------
> cursus_id          | 62
> vers_id            | 6
> traiter            | f
> code_type_academic | ECT
> cod_etu            | 041400
>
> this will execute the 3 update corresponding to the 3 rules i defined for
> the values of cursus_id, vers_id, traiter, code_type_academic, cod_etu
> returned above !!!
> and i will have the following 3 updates executes !
>
> UPDATE transactions_sco SET traiter = 't' WHERE cursus_id = 62 AND vers_id =
> 6 traiter = 'f' AND code_type_academic = 'ECT' and cod_etu = '041400';
>
> UPDATE transactions_sco SET traiter = 't' WHERE cursus_id = 62 AND vers_id =
> 6 traiter = 'f' AND code_type_academic = 'ECT';
>
>
> UPDATE transactions_sco SET traiter = 't' WHERE id = 53597;
>
>
> I AM GETTING THIS RIGHT ??

Sounds right to me. I'm posting an expanded example since it's a
complicated issue and others on the list might benefit.

--
   Richard Huxton
   Archonet Ltd