Mailbox-Line: From kreide@online.no  Mon Jul  8 17:07:29 2002
Message-ID: <047101c226a7$36fb2da0$6b97f181@speed>
From: "Kristian Eide" <kreide@online.no>
To: <pgsql-sql@postgresql.org>
Subject: Rule WHERE condition problem
Date: Mon, 8 Jul 2002 19:45:14 +0200
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

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