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
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
