Re: Problem with multiple action rule on modifiable view

Just a few days ago, I ran into the same thing.

As I understand it: the idea behind the whole RULE system is that rules
become added / replaced in the query tree BEFORE it is submitted to the
optimizer. So, there is no quarantee queryies will be executed in the exact
form and order as you wrote them as rules...

Something that is not fed to the optimizer, are statements within a stored
procedure... :)) Check with Depesz' post:
http://www.depesz.com/index.php/2010/10/16/waiting-for-9-1-triggers-on-views/

PostgreSQL 9.1 is now in Alpha. Find the Release Notes here:
http://www.depesz.com/index.php/2010/10/16/waiting-for-9-1-triggers-on-views/
(esspecially section E.1.4.7.2)

With triggers on VIEWs, VIEWs can now finally *truely* behave like
interfaces to complex structures behind it. This functionality is essential
for implementing Object/Relational mappings server-side. Rules are still
useful, but Triggers and Rules are totally different things... And Triggers
on VIEWs are soooo welcome! :))

Just curious: anyone else on this list planning to (not to) use them?
Thoughts? Judgements? Considerations?

Cheers,
Rob

2011/4/5 Chris Oldfield <cjoldfield(at)gmail(dot)com>

> 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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>