Re: Touch row ? - Mailing list pgsql-general

Chris Travers wrote:

> 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.

One would not expect that if one would know how the rewriter works. It
does not evaluate the where clause at the time of rewriting (and how
could it ... there are no rows at hand at the time of rewriting ...
there is not even an execution plan at that time).


Jan

>
> 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
>>
>>


--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #