| 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: | Whole Thread | Raw Message | 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
| 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 |