469,090 Members | 1,222 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,090 developers. It's quick & easy.

Multiple Rules :: Postgres Is confused !!

Hi all i am running PostgreSQL 7.3.2,

i have a VIEW for which i implemented multiple RULES on UPDATE.

The weird think is that the Update Query corresponding to one of the rules is updating MULTIPLE ROWS even though it should only update one ROW !!

THE WEIRDEST is that when i remove 2 of the update Rules on the VIEW The Update Query Works FINE !!!!
WHY IS THAT HAPPENNING ??

I can provide more details if anyone is ready to help ...
Nov 23 '05 #1
8 1413
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 ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 23 '05 #2

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

Nov 23 '05 #3
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

Nov 23 '05 #4

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

Nov 23 '05 #5
> > 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 <kl*****@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.


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFBWqg8Y5Twig3Ge+YRAmpTAKDXVbTrFVTxc4/8PNIJiCHpZ5r9hQCgpyVi
AjUgIcQ+iiYAAjB7+qs+smY=
=TwGj
-----END PGP SIGNATURE-----

Nov 23 '05 #6
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

Nov 23 '05 #7
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 ma*******@postgresql.org)

Nov 23 '05 #8
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

Nov 23 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by Oksana Yasynska | last post: by
22 posts views Thread by Christopher Murtagh | last post: by
reply views Thread by Mujdat Pakkan | last post: by
4 posts views Thread by Dave [Hawk-Systems] | last post: by
3 posts views Thread by Gaetano Mendola | last post: by
reply views Thread by kglaser89 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.