rule creating infinite recursion not sure why

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.