By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,176 Members | 984 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,176 IT Pros & Developers. It's quick & easy.

View's rule on delete problem

P: n/a

CREATE RULE "new_rule2" AS ON DELETE TO "public"."klients_view"
DO INSTEAD (

DELETE
FROM klients
WHERE (klients.klient_id = old.klient_id);

DELETE
FROM klient_services
WHERE (klient_services.klient_id = old.klient_id);
);

It's 2 commands, but executing only first. Why?

Stanislaw Tristan
Kyiv, Ukraine
E-mail: st******@i.com.ua
Nov 23 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
"Stanislaw Tristan" <st******@i.com.ua> writes:
It's 2 commands, but executing only first. Why?


You didn't show us the view, but I suppose it's an inner join of the two
tables? As soon as you delete the row from the first table, there's no
longer any matching row in the view, so the second command finds no OLD
row to join against.

Consider making the view a LEFT JOIN and being sure to delete from the
righthand table first.

regards, tom lane

---------------------------(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 #2

P: n/a
On Thu, Oct 28, 2004 at 04:32:21AM +0300, Stanislaw Tristan wrote:

CREATE RULE "new_rule2" AS ON DELETE TO "public"."klients_view"
DO INSTEAD (

DELETE
FROM klients
WHERE (klients.klient_id = old.klient_id);

DELETE
FROM klient_services
WHERE (klient_services.klient_id = old.klient_id);
);

It's 2 commands, but executing only first. Why?


Tom Lane described the problem in another thread a few years ago:

http://archives.postgresql.org/pgsql...6/msg00559.php

"...OLD is essentially a macro for the view. As soon as you delete
a row from foo, there's no longer any such row in the view, so the
delete from bar doesn't find anything to delete."

"What you probably want instead is to make bar reference foo as a
foreign key with ON DELETE CASCADE; then the rule for foobar only
needs to delete from foo explicitly, and the additional delete from
bar is done implicitly by the foreign key trigger."

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 23 '05 #3

P: n/a
"Stanislaw Tristan" <st******@i.com.ua> writes:
It's 2 commands, but executing only first. Why?


You didn't show us the view, but I suppose it's an inner join of the two
tables? As soon as you delete the row from the first table, there's no
longer any matching row in the view, so the second command finds no OLD
row to join against.

Consider making the view a LEFT JOIN and being sure to delete from the
righthand table first.

regards, tom lane

---------------------------(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 #4

P: n/a
On Thu, Oct 28, 2004 at 04:32:21AM +0300, Stanislaw Tristan wrote:

CREATE RULE "new_rule2" AS ON DELETE TO "public"."klients_view"
DO INSTEAD (

DELETE
FROM klients
WHERE (klients.klient_id = old.klient_id);

DELETE
FROM klient_services
WHERE (klient_services.klient_id = old.klient_id);
);

It's 2 commands, but executing only first. Why?


Tom Lane described the problem in another thread a few years ago:

http://archives.postgresql.org/pgsql...6/msg00559.php

"...OLD is essentially a macro for the view. As soon as you delete
a row from foo, there's no longer any such row in the view, so the
delete from bar doesn't find anything to delete."

"What you probably want instead is to make bar reference foo as a
foreign key with ON DELETE CASCADE; then the rule for foobar only
needs to delete from foo explicitly, and the additional delete from
bar is done implicitly by the foreign key trigger."

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 23 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.