Thread: Rule WHERE condition problem

I have a table with a reference constraint and an ON DELETE SET NULL action.
When this action triggers, I also want to update another field in the table,
actually a timestamp which should be set to NOW().

After reading some documentation it would seem a rule is the easiest way to
accomplish this. However, I ran into a problem with this:

CREATE TABLE a ( id INT PRIMARY KEY
);
CREATE TABLE b ( id INT REFERENCES a ON DELETE SET NULL, time TIMESTAMP DEFAULT 'infinity'
);
INSERT INTO a VALUES (1);
INSERT INTO b VALUES (1);

CREATE RULE b_id_null AS ON UPDATE TO b WHERE new.time='infinity' AND old.id
IS NOT NULL AND new.id IS NULL DO UPDATE b SET time=NOW() where id=old.id;

DELETE FROM a WHERE id=1;

I would now expect a to by empty and b to contain a single row with id=NULL
and time=NOW(). However, this is what I get:

ERROR:  query rewritten 10 times, may contain cycles
ERROR:  query rewritten 10 times, may contain cycles

It would seem that my WHERE clause is not checked before the action is run.
Is this simply not implemented (yet, hopefully)?


Thanks.

---
Kristian

"Kristian Eide" <kreide@online.no> writes:
> ERROR:  query rewritten 10 times, may contain cycles

> It would seem that my WHERE clause is not checked before the action is run.

No, the WHERE clause is essentially transformed into part of the rule
query.  You can't ever write a rule of the formON UPDATE TO b DO UPDATE b ...
because it *will* be an infinite loop, condition or no condition.

Consider using a trigger instead.
        regards, tom lane

>> ERROR:  query rewritten 10 times, may contain cycles
>> It would seem that my WHERE clause is not checked before the action is run.
>No, the WHERE clause is essentially transformed into part of the rule
>query.  You can't ever write a rule of the form
>    ON UPDATE TO b DO UPDATE b ...
>because it *will* be an infinite loop, condition or no condition.

OK, perhaps the documentation should state that recursive rules are not 
supported.

>Consider using a trigger instead.

Yes, I have written a PL/pgsql function and a trigger for this, which seems to 
work perfectly. A rule would have been much simpler though :)


Thanks.

---
Kristian

Kristian Eide wrote:
> 
> I have a table with a reference constraint and an ON DELETE SET NULL action.
> When this action triggers, I also want to update another field in the table,
> actually a timestamp which should be set to NOW().
> 
> After reading some documentation it would seem a rule is the easiest way to
> accomplish this. However, I ran into a problem with this:
> 
> CREATE TABLE a (
>   id INT PRIMARY KEY
> );
> CREATE TABLE b (
>   id INT REFERENCES a ON DELETE SET NULL,
>   time TIMESTAMP DEFAULT 'infinity'
> );
> INSERT INTO a VALUES (1);
> INSERT INTO b VALUES (1);
> 
> CREATE RULE b_id_null AS ON UPDATE TO b WHERE new.time='infinity' AND old.id
> IS NOT NULL AND new.id IS NULL DO UPDATE b SET time=NOW() where id=old.id;
> 
> DELETE FROM a WHERE id=1;
> 
> I would now expect a to by empty and b to contain a single row with id=NULL
> and time=NOW(). However, this is what I get:
> 
> ERROR:  query rewritten 10 times, may contain cycles
> ERROR:  query rewritten 10 times, may contain cycles
> 
> It would seem that my WHERE clause is not checked before the action is run.
> Is this simply not implemented (yet, hopefully)?

The rule system does not evaluate anything before beeing done with the
rewriting. All it is looking at is "is this an UPDATE for b?. Since the
rewritten result is one again, it tries to apply the same rule to that
now, and again, and again.

This is not a bug. Without this recursive behaviour, views of views
would not be possible. 

You can implement the functionality you want with a custom trigger.


Jan

-- 

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