Re: Problem with multiple action rule on modifiable view - Mailing list pgsql-general

Hi,
  I'm trying to implement a modifiable view and have run into a road
block.  A DELETE rule attached to my view refuses to execute any commands
after the first delete on one of views the component tables. Examining the
output of EXPLAIN, it seems that the view is constructed for every action
in the rule, whether the action references the view (via OLD) or not.

Is this expected behavior? Is there a work around?

I realize that I'm probably missing something about the way rules work,
but nonetheless I'm confused. Naively, it seems to me that the view
should only be evaluated for an action if OLD is referenced.  Otherwise,
some strange behavior happens. Such is the example case below, the action
"DELETE FROM parent_child_view WHERE id=1;" results in only the first
action executing, but "DELETE FROM parent_child_view;" executes both
actions.

I'm using Postgres 9.0.3. Thanks for any help.  Below is the example case:

CREATE TABLE parent(
  id serial PRIMARY KEY,
  p_data integer NOT NULL UNIQUE
);
CREATE TABLE child(
  id serial PRIMARY KEY,
  parent_id integer NOT NULL REFERENCES parent(id),
  c_data integer NOT NULL
);
CREATE TABLE parent_child_view(
  id integer,
  p_data integer,
  c_data integer
);
CREATE RULE "_RETURN" AS ON SELECT TO parent_child_view DO INSTEAD
  SELECT child.id, p_data, c_data
      FROM parent JOIN child ON (parent_id=parent.id);
CREATE RULE child_view_delete AS ON DELETE TO child_view DO INSTEAD(
  DELETE FROM child WHERE id=OLD.id returning id;
  DELETE FROM parent WHERE id NOT IN (SELECT parent_id FROM child);
);

> EXPLAIN DELETE FROM parent_child_view WHERE id=1;

                                       QUERY
PLAN
-----------------------------------------------------------------------
 Delete
  ->  Nested Loop
        ->  Nested Loop
              ->  Index Scan using child_pkey on child
                    Index Cond: (id = 1)
              ->  Index Scan using child_pkey on child
                    Index Cond: (public.child.id = 1)
        ->  Index Scan using parent_pkey on parent
              Index Cond: (parent.id = public.child.parent_id)

 Delete
  ->  Nested Loop
        ->  Nested Loop
              ->  Index Scan using child_pkey on child
                    Index Cond: (id = 1)
              ->  Index Scan using parent_pkey on parent
                    Index Cond: (public.parent.id =
public.child.parent_id)
        ->  Seq Scan on parent
              Filter: (NOT (hashed SubPlan 1))
              SubPlan 1
                ->  Seq Scan on child
(21 rows)



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general