Hi,
On PostgreSQL 7.4.2 I can create a situation in which a foreign key is
violated:
bug=# SELECT b.name, exists(SELECT 1 FROM a WHERE a.name = b.name) FROM b;
name | ?column?
------+----------
xxx | f
(1 ÐÐÐÐÑÑ)
bug=# \d+ b
ÐÐÐÐÐÑÐ "public.b"
ÐÐÐÐÐÐÐ | ÐÐÐ | ÐÐÐÐÑÐÐÐÑÐÑÑ | ÐÐÐÑÐÐÐÐ
----------------+--------+--------------------------+------------------
name | text | not null |
ÐÐÐÐÐÑÑ:
"b_pkey" ÐÐÑÑÐÐÐÐ ÐÐÐÐ, btree (name)
ÐÐÑÐÐÐÑÐÐÐÑ ÐÐ ÐÑÐÑÐÑÐÐÐÑ ÐÐÑÑÑ:
"$1" FOREIGN KEY (name) REFERENCES a(name) ON UPDATE CASCADE
ÐÑÐÐÐÐÐ:
b_rename AS ON UPDATE TO b DO INSTEAD UPDATE a SET name = new.name WHERE (a.name =
old.name)
I create the situation as follows:
CREATE TABLE a (name TEXT PRIMARY KEY);
INSERT INTO a VALUES ('xxx');
CREATE TABLE b (name TEXT PRIMARY KEY REFERENCES a(name) ON UPDATE CASCADE);
INSERT INTO b VALUES ('xxx');
CREATE RULE b_rename AS ON UPDATE TO b DO INSTEAD UPDATE a SET name = NEW.name WHERE
name = OLD.name;
UPDATE b SET name = 'yyy' WHERE name = 'xxx';
SELECT b.name, exists(SELECT 1 FROM a WHERE a.name = b.name) FROM b;
name | ?column?
------+----------
xxx | f
(1 ÐÐÐÐÑÑ)
Up to here I thought that the following was going on: The UPDATE b
statement was rewritten into a UPDATE a statement by the rule system.
The update on a triggers the foreign key update on b. This UPDATE gets
rewritten again by the rule system to update a instead. The update to a
triggers the foreign key again, which recognizes that it is already
running and does nothing. The outer foreign key is done and the update
to a is realized. b stays unchanged.
But then I discovered that if I update the row in a prior to creating
the rule, the rule works as expected:
CREATE TABLE a (name TEXT PRIMARY KEY);
INSERT INTO a VALUES ('xxx');
CREATE TABLE b (name TEXT PRIMARY KEY REFERENCES a(name) ON UPDATE CASCADE);
INSERT INTO b VALUES ('xxx');
UPDATE a SET name = 'zzz' WHERE name = 'xxx';
CREATE RULE b_rename AS ON UPDATE TO b DO INSTEAD UPDATE a SET name = NEW.name WHERE
name = OLD.name;
UPDATE b SET name = 'yyy' WHERE name = 'zzz';
SELECT b.name, exists(SELECT 1 FROM a WHERE a.name = b.name) FROM b;
name | ?column?
------+----------
yyy | t
(1 ÐÐÐÐÑÑ)
This somehow renders my theory invalid. Can someone comment?
I also tried the same rule without INSTEAD. That does what I want and it
is what I'm using in the application now. I wonder if that is The Right
WayÂ.
And should PostgreSQL allow foreign key violations like in the example
above?
Thanks
--
Markus Bertheau <[EMAIL PROTECTED]>
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
