Rule WHERE condition problem

From: "Kristian Eide" <kreide(at)online(dot)no>
To: <pgsql-sql(at)postgresql(dot)org>
Subject: Rule WHERE condition problem
Date: 2002-07-08 17:45:14
Message-ID: 047101c226a7$36fb2da0$6b97f181@speed
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Bruce Momjian 2002-07-08 18:40:39 Re:
Previous Message Richard Huxton 2002-07-08 16:49:48 Re: newbie question