Re: Touch row ?

"Eric B.Ridge" <ebr(at)tcdi(dot)com> writes:
> On Jan 24, 2004, at 12:36 AM, Chris Travers wrote:
>> CREATE RULE touch_row AS ON UPDATE TO mytable DO
>> (UPDATE mytable SET last_updated = NOW() WHERE my_id = NEW.my_id);

> [ ... but that produces ]
> test=# update mytable set my_id = 1;
> ERROR: infinite recursion detected in rules for relation "mytable"

> I might have missed something in the docs (been awhile since I've read
> 'em), but I don't believe a rule command can reference its target.

The restriction is not that: the restriction is that you can't have an
infinite recursion in your rules. The above is infinitely recursive
because it says that for any UPDATE on mytable, you should also do an
UPDATE on mytable ... but then for that UPDATE you also need to do
another UPDATE on mytable ... etc. The bodies of rules are not exempt
from rule expansion.

It might be interesting to change that definition, so that a rule like
the above could be written that wouldn't recursively trigger itself.
This would need a lot of careful thought though. In most cases you *do*
want rule bodies to be rule-expanded.

A different tack that might be interesting to think about is to invent
a notion of an "update default" for a column, analogous to the existing
"insert default". The normal behavior is that the "update default" is
the old value, but if you could specify some computable expression to
use instead, this and related problems could be solved with a much
simpler mechanism than a rule.

regards, tom lane