Re: Multiple Rules :: Postgres Is confused !!

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