Re: Why do I get a circular query when I insert tuples with this rule ?

xavier(dot)goddeeris(at)dds(dot)delphiauto(dot)com (Xavier Goddeeris) writes:
> Here is my rule:

> CREATE RULE old_error_codes AS ON insert to Passage
> WHERE erreur<>0 and ligne<>1 and erreur<500 DO INSTEAD
> insert into Passage(idinjecteur, ligne, datedesortie, estbon,
> nomcible, erreur, derniertest, nodupassage)
> values (new.idinjecteur, new.ligne, new.datedesortie, new.estbon,
> new.nomcible, new.erreur+500, new.derniertest, new.nodupassage);

This rule is circular: it takes an "INSERT INTO Passage" and generates
a new "INSERT INTO Passage", to which the same rule is applied, so
the system goes into an endless loop. (Or it would be endless, if
there weren't a check for this sort of problem.)

You can't accomplish what you want to do with a rule. You need to
use a trigger, instead.

> Once he has done for the first time, the action specified in the rule,
> Postgres doesn't have to re-execute the action in the rule, because
> the second time he checks the rule he finds that "erreur" is not < 500
> (the condition of the rule is false).

Nope. The expansion of the rule happens before execution, so whether
the condition actually applies at a particular tuple is irrelevant.
You've written an infinitely-recursive rule definition even if the
condition were *never* true. To put it another way: rules rewrite
the form of the query, but what you end up with is still a query.
The contents of the table haven't been examined yet.

Triggers are much easier to use for this sort of thing, because they
fire for each tuple that's actually inserted, updated, or deleted.
The notational cruft is worse than for a rule, but conceptually a
trigger is far simpler than a rule.

regards, tom lane