Обсуждение: Infinite recursion detected... How do I prevent that?
I have a rule similar to this: CREATE RULE rule_branch_delete AS ON DELETE TO tree DO DELETE FROM tree WHERE ancestor_id IS NOT NULL AND OLD.child_id = ancestor_id; The data is oraganized like this: ancestor_id child_id ================================ NULL 1 1 2 1 3 1 4 1 5 1 6 1 7 1 8 2 4 2 5 2 6 3 7 3 8 etc. This is an optimization table that relates all nodes in a branch to all it's ancestor nodes; to it's parent, to it's grand parent to it's grand grand parent, etc. The tree is 3 to 5 levels deep. The intention is to delete all children of a branch that share the same ancestor when an ancestor gets deleted. This happens recursively, of course. If I try a delete on the tree table I get "Infinite recursion detected on rules on tree". I'm pretty sure it's not "infinite" in my case, how can I make it delete the records regardless this "infinity"? At the moment I use a trigger, but I would prefer a rule. Alban.
Martijn van Oosterhout wrote: > Sorry, RULEs are like macros, they essentially expand and transform > your original query. This also means the expansion does not depend on > the data in your database. So postgresql continaually expands the > query, leading to your infinite recursion error. I just found out. makes sense... > Why do you want a RULE anyway, trigger are much easier to understand. > It may be possile to setup some RULEs to avoid recursion but it won't > be easy... Well, actually the easiest way to do this turns out to be defining my foreign key constraint with "on delete cascade". That does exactly what I intend to do, and at the right time too :) Alban.
On Wed, Jan 19, 2005 at 02:57:47PM +0100, Alban Hertroys wrote: > I have a rule similar to this: > > CREATE RULE rule_branch_delete AS > ON DELETE TO tree > DO DELETE > FROM tree > WHERE ancestor_id IS NOT NULL > AND OLD.child_id = ancestor_id; <snip> > If I try a delete on the tree table I get "Infinite recursion detected > on rules on tree". I'm pretty sure it's not "infinite" in my case, how > can I make it delete the records regardless this "infinity"? > > At the moment I use a trigger, but I would prefer a rule. Sorry, RULEs are like macros, they essentially expand and transform your original query. This also means the expansion does not depend on the data in your database. So postgresql continaually expands the query, leading to your infinite recursion error. Why do you want a RULE anyway, trigger are much easier to understand. It may be possile to setup some RULEs to avoid recursion but it won't be easy... Hope this helps, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Вложения
# alban@magproductions.nl / 2005-01-19 14:57:47 +0100: > I have a rule similar to this: > > CREATE RULE rule_branch_delete AS > ON DELETE TO tree > DO DELETE > FROM tree > WHERE ancestor_id IS NOT NULL > AND OLD.child_id = ancestor_id; > If I try a delete on the tree table I get "Infinite recursion detected > on rules on tree". I'm pretty sure it's not "infinite" in my case, how > can I make it delete the records regardless this "infinity"? cover the table with a view, as in: CREATE TABLE _tree ( ancestor_id int, child_id int ); CREATE VIEW tree AS SELECT * FROM _tree; CREATE RULE rule_branch_delete AS ON DELETE TO tree DO INSTEAD ( DELETE FROM _tree ...; (the original DELETE redirected to _tree) DELETE FROM _tree WHERE ancestor_id IS NOT NULL AND OLD.child_id = ancestor_id; ); -- If you cc me or remove the list(s) completely I'll most likely ignore your message. see http://www.eyrie.org./~eagle/faqs/questions.html