PostgreSQL 7.4.2 allows foreign key violation - Mailing list pgsql-general

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 <twanger@bluetwanger.de>