Thread: View's rule on delete problem

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: stas7775@i.com.ua

"Stanislaw Tristan" <stas7775@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

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-general/2001-06/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/