Markus Bertheau <twanger@bluetwanger.de> writes:
> 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;
The difficulty here is that the CASCADE is implemented by generating an
"UPDATE b" command ... which is rewritten by your rule and thus fails to
affect table b at all.
It would probably be better if the RI implementation acted at a lower
level and wasn't affected by rules, but for the foreseeable future the
answer is "don't do that".
> But then I discovered that if I update the row in a prior to creating
> the rule, the rule works as expected:
Only for the moment --- you're depending on a cached plan for the
foreign-key update. Start a fresh backend and it's broken again.
regards, tom lane