Re: Touch row ? - Mailing list pgsql-general

Interestingly enough, a where clause is insufficient to free yourself from
the infinite recursion.  For example

CREATE RULE ON mytable where old.mytimestamp != now() do update mytable set
timestamp = now() still infinitely recurses even though on the second
attempt one would expect the where clause to drop the row out on the second
recursion.  I can only assume that this would create a lot of work for the
planner (determining if the recursion is real or just possible) and too much
work to do at the moment.  Also one has the question of how many times a
rule should be allowed to recurse before considering it infinite.

Best Wishes,
Chris Travers

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Eric B.Ridge" <ebr@tcdi.com>
Cc: "Chris Travers" <chris@travelamericas.com>; "Jan Wieck"
<JanWieck@Yahoo.com>; "NTPT" <ntpt@centrum.cz>; "Mike Mascari"
<mascarm@mascari.com>; "PostgreSQL-general" <pgsql-general@postgresql.org>
Sent: Sunday, January 25, 2004 2:34 AM
Subject: Re: [GENERAL] Touch row ?


> "Eric B.Ridge" <ebr@tcdi.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
>
>