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:
PostgreSQL 9.1 is now in Alpha. Find the Release Notes here:
(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@xxxxxxxxx>
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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
