Re: PostgreSQL 7.4.2 allows foreign key violation

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Markus Bertheau <twanger(at)bluetwanger(dot)de>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: PostgreSQL 7.4.2 allows foreign key violation
Date: 2004-08-06 14:54:52
Message-ID: 11822.1091804092@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Josué Maldonado 2004-08-06 15:28:53 Re: Insert into sintax
Previous Message Stephan Szabo 2004-08-06 14:43:50 Re: PostgreSQL 7.4.2 allows foreign key violation