Обсуждение: rule creating infinite recursion not sure why

Поиск
Список
Период
Сортировка

rule creating infinite recursion not sure why

От
Gene
Дата:
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

Re: rule creating infinite recursion not sure why

От
Tom Lane
Дата:
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

Re: rule creating infinite recursion not sure why

От
Andreas Kretschmer
Дата:
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°

Re: rule creating infinite recursion not sure why

От
Alvaro Herrera
Дата:
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.