Thread: 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