Thread: rule on update

Hallo,

I have a table with a timestamp-field, which should contain the date, when
the row was last-updated.
To maintain it, I tried to create a rule:

CREATE RULE rule_entries_update_self
AS ON UPDATE TO entries where new.ent_udate=old.ent_udate DO INSTEAD
 UPDATE entries SET ent_id=new.ent_id, ent_id_parent=new.ent_id_parent,
ent_name=new.ent_name, ent_desc=new.ent_desc, ent_type=new.ent_type,
ent_uname=CURRENT_USER, ent_udate=CURRENT_TIMESTAMP WHERE ent_id=new.ent_id;

But then, doing an update it get's me:
ERROR:  query rewritten 10 times, may contain cycles

It shouldn't show me this error, because the update invoked by the rule
doesn't fulfill the condition (new.ent_udate=old.ent_udate).

I just can't see, what's wrong here.
(it's postgresql 7.1.3)

Ciao,
 Gerhard

Gerhard Pfeiffer wrote:
> Hallo,
>
> I have a table with a timestamp-field, which should contain the date, when
> the row was last-updated.
> To maintain it, I tried to create a rule:
>
> CREATE RULE rule_entries_update_self
> AS ON UPDATE TO entries where new.ent_udate=old.ent_udate DO INSTEAD
>  UPDATE entries SET ent_id=new.ent_id, ent_id_parent=new.ent_id_parent,
> ent_name=new.ent_name, ent_desc=new.ent_desc, ent_type=new.ent_type,
> ent_uname=CURRENT_USER, ent_udate=CURRENT_TIMESTAMP WHERE ent_id=new.ent_id;
>
> But then, doing an update it get's me:
> ERROR:  query rewritten 10 times, may contain cycles
>
> It shouldn't show me this error, because the update invoked by the rule
> doesn't fulfill the condition (new.ent_udate=old.ent_udate).
>
> I just can't see, what's wrong here.
> (it's postgresql 7.1.3)

    First  you cannot guarantee that there will never be any rows
    holding a future timestamp in ent_udate! So how can  you  say
    that      after      rewriting      the      first      time,
    new.ent_udate=old.ent_udate will never be true?

    Second, the parsetree the rule system is working  on  is  too
    abstract  to  allow  such  conclusions. Read chapter 8 of the
    programmers manual to understand how the rule system works in
    detail.

    What  you  "want" to use here is a BEFORE UPDATE trigger that
    put's the current timestamp into NEW.


Jan

--

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



_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

"Gerhard Pfeiffer" <gp@bnbt.de> writes:
> It shouldn't show me this error, because the update invoked by the rule
> doesn't fulfill the condition (new.ent_udate=old.ent_udate).

Rules are macros rewritten at planning time, not at execution time,
so you cannot escape the rewrite cycle with an execution-time condition.

You will probably have better luck doing what you want to do with
a trigger.

            regards, tom lane