pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRule WHERE condition problem
4+ messages / 3 participants
[nested] [flat]
* Rule WHERE condition problem
@ 2002-07-08 17:45 Kristian Eide <kreide@online.no>
2002-07-09 13:00 ` Re: Rule WHERE condition problem Jan Wieck <JanWieck@Yahoo.com>
2002-07-09 13:23 ` Re: Rule WHERE condition problem Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 4+ messages in thread
From: Kristian Eide @ 2002-07-08 17:45 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Rule WHERE condition problem
2002-07-08 17:45 Rule WHERE condition problem Kristian Eide <kreide@online.no>
@ 2002-07-09 13:00 ` Jan Wieck <JanWieck@Yahoo.com>
1 sibling, 0 replies; 4+ messages in thread
From: Jan Wieck @ 2002-07-09 13:00 UTC (permalink / raw)
To: Kristian Eide <kreide@online.no>; +Cc: pgsql-sql
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 #
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Rule WHERE condition problem
2002-07-08 17:45 Rule WHERE condition problem Kristian Eide <kreide@online.no>
@ 2002-07-09 13:23 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 4+ messages in thread
From: Tom Lane @ 2002-07-09 13:23 UTC (permalink / raw)
To: Kristian Eide <kreide@online.no>; +Cc: pgsql-sql
"Kristian Eide" <kreide@online.no> writes:
> ERROR: query rewritten 10 times, may contain cycles
> It would seem that my WHERE clause is not checked before the action is run.
No, the WHERE clause is essentially transformed into part of the rule
query. You can't ever write a rule of the form
ON UPDATE TO b DO UPDATE b ...
because it *will* be an infinite loop, condition or no condition.
Consider using a trigger instead.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Rule WHERE condition problem
@ 2002-07-09 13:41 Kristian Eide <kreide@online.no>
0 siblings, 0 replies; 4+ messages in thread
From: Kristian Eide @ 2002-07-09 13:41 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
>> ERROR: query rewritten 10 times, may contain cycles
>> It would seem that my WHERE clause is not checked before the action is run.
>No, the WHERE clause is essentially transformed into part of the rule
>query. You can't ever write a rule of the form
> ON UPDATE TO b DO UPDATE b ...
>because it *will* be an infinite loop, condition or no condition.
OK, perhaps the documentation should state that recursive rules are not
supported.
>Consider using a trigger instead.
Yes, I have written a PL/pgsql function and a trigger for this, which seems to
work perfectly. A rule would have been much simpler though :)
Thanks.
---
Kristian
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2002-07-09 13:41 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-07-08 17:45 Rule WHERE condition problem Kristian Eide <kreide@online.no>
2002-07-09 13:00 ` Jan Wieck <JanWieck@Yahoo.com>
2002-07-09 13:23 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-07-09 13:41 Re: Rule WHERE condition problem Kristian Eide <kreide@online.no>
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