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
Message-ID: <047101c226a7$36fb2da0$6b97f181@speed> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: kreide@online.no
Subject: Re: Rule WHERE condition problem
In-Reply-To: <047101c226a7$36fb2da0$6b97f181@speed>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox