Re: [GENERAL] Multiple Rules :: Postgres Is confused !!
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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Multiple Rules :: Postgres Is confused !!
> 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. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Multiple Rules :: Postgres Is confused !!
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 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Multiple Rules :: Postgres Is confused !!
> > 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 <[EMAIL PROTECTED]> 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. pgpxGRwBvxvwt.pgp Description: PGP signature
Re: [GENERAL] Multiple Rules :: Postgres Is confused !!
> 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. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Multiple Rules :: Postgres Is confused !!
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. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] 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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
