Re: PostgreSQL 7.4.2 allows foreign key violation

On Fri, 6 Aug 2004, Markus Bertheau wrote:

> 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?

Only for that session. The foreign key query is planned at the first
update so it doesn't see the rule until you get to a new session.

> 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.

Probably.

> And should PostgreSQL allow foreign key violations like in the example
> above?

Probably not.

It also looks like before triggers returning NULL can break them. I think
we'd been worried about the added cost of doing the check when the average
case doesn't have this problem but we should probably just eat it. In
practice I think it's one line of code per action function (on update set
default already does it).

Any opinions out there?