On Apr 26, 2005, at 8:55 AM, Tom Lane wrote:
Well, they handle simple situations OK, but we keep seeing people get
burnt as soon as they venture into interesting territory. For instance,
if the view is a join, you can't easily make a rule that turns a delete
into deletions of both joined rows. And you'll get burnt if you try to
insert any volatile functions, because of the multiple-evaluation issue.
Etc.
sharky=# CREATE TABLE a ( sharky(# id int, sharky(# name text sharky(# ); CREATE TABLE sharky=# CREATE TABLE b ( sharky(# a_id int, sharky(# rank text sharky(# ); CREATE TABLE sharky=# sharky=# CREATE VIEW ab AS sharky-# SELECT id, name, rank sharky-# FROM a, b sharky-# WHERE a.id = b.a_id sharky-# ; CREATE VIEW sharky=# CREATE RULE delete_ab AS sharky-# ON DELETE TO ab DO INSTEAD ( sharky(# DELETE FROM b sharky(# WHERE a_id = OLD.id; sharky(# sharky(# DELETE FROM a sharky(# WHERE id = OLD.id; sharky(# ); CREATE RULE sharky=# sharky=# sharky=# insert into a values (1, 'test'); INSERT 597795 1 sharky=# insert into b values (1, 'sergeant'); INSERT 597796 1 sharky=# select * from ab; id | name | rank ----+------+---------- 1 | test | sergeant (1 row)
sharky=# delete from ab; DELETE 0 sharky=# select * from ab; id | name | rank ----+------+------ (0 rows)
sharky=# select * from a; id | name ----+------ 1 | test (1 row)
sharky=# select * from b; a_id | rank ------+------ (0 rows)
Ah, yes, you're right, that is...unexpected. Perhaps OLD can contain its values for the duration of the RULE's statements? I'm assuming that what's happening is that OLD.id is NULL after the first of the two DELETE statements...
Like I said, I don't have a better idea. Just a vague feeling of dissatisfaction.
I'd call it a bug. ;-)
Regards,
David
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
