Thread: rule creating infinite recursion not sure why

I was trying to create a rule to set a column to false whenever another column was changed:

CREATE RULE...
ON UPDATE TO criterion
   WHERE new.pattern::text <> old.pattern::text DO UPDATE table SET flag = false
  WHERE id = old.id

pattern | id
12345  | 1

=> update criterion set pattern = '12345' where id = 1;
ERROR:  infinite recursion detected in rules for relation "criterion"

I'm not sure why it's detecting recursion in this case when the rule conditional should be false

Thanks for any help...
gene

Gene <genekhart@gmail.com> writes:
> I'm not sure why it's detecting recursion in this case when the rule
> conditional should be false

Rules are macros, which means that expansion has to terminate
statically, not dynamically.  For the particular purpose you seem to
have here, it'd be a lot more manageable and a lot more efficient
to use a BEFORE UPDATE trigger instead of a rule.

    if new.pattern <> old.pattern then
        new.flag = false;
    end if;
    return new;

Or are you trying to change some other row than the one being updated?

            regards, tom lane

Gene <genekhart@gmail.com> schrieb:

> I was trying to create a rule to set a column to false whenever another column
> was changed:
>
> CREATE RULE...
> ON UPDATE TO criterion
>    WHERE new.pattern::text <> old.pattern::text DO UPDATE table SET flag =
> false
>   WHERE id = _o_l_d_._i_d
>
> pattern | id
> 12345  | 1
>
> => update criterion set pattern = '12345' where id = 1;
> ERROR:  infinite recursion detected in rules for relation "criterion"

I think, you should better use a TRIGGER and modify the NEW.flag within
the Trigger. Your solution creates a new UPDATE when an UPDATE occur.


Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."    (unknow)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

Gene wrote:
> I was trying to create a rule to set a column to false whenever another
> column was changed:

Don't.  Use a BEFORE trigger, and instead of issuing a new UPDATE, just
change the NEW record that you return.  It's conceptually much simpler.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.