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 #
