Re: PostgreSQL 7.4.2 allows foreign key violation

Markus Bertheau <twanger(at)bluetwanger(dot)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