Thread: BUG #3696: FK integrity check bypassed using rules.
The following bug has been logged online: Bug reference: 3696 Logged by: Pierre-yves Strub Email address: pierre.yves.strub@gmail.com PostgreSQL version: 8.2.5 / 8.3b Operating system: Linux 2.6 Description: FK integrity check bypassed using rules. Details: Hello. Here is a SQL script reproducing the error. CREATE SEQUENCE "sequence"; CREATE TABLE data ( id INTEGER PRIMARY KEY DEFAULT nextval('sequence'), ref_id INTEGER NULL REFERENCES data(id) ON DELETE CASCADE ); CREATE RULE data_delete_rule AS ON DELETE TO data WHERE OLD.ref_id IS NOT NULL DO INSTEAD NOTHING; INSERT INTO data (ref_id) VALUES (NULL); INSERT INTO data (ref_id) SELECT id FROM data LIMIT 1; DELETE FROM data WHERE ref_id IS NULL; SELECT * FROM data; The result of the last SELECT is: id | ref_id ----+-------- 2 | 1 (1 row) which shows that we have bypassed the foreign key integrity check. Regards, Pierre-yves.
"Pierre-yves Strub" <pierre.yves.strub@gmail.com> writes: > CREATE TABLE data ( > id INTEGER PRIMARY KEY DEFAULT nextval('sequence'), > ref_id INTEGER NULL REFERENCES data(id) ON DELETE CASCADE > ); > CREATE RULE data_delete_rule > AS ON DELETE TO data WHERE OLD.ref_id IS NOT NULL > DO INSTEAD NOTHING; Yes, a poorly designed rule can invalidate all kinds of expectations about behavior. This isn't a bug in my humble opinion. regards, tom lane
On 10/25/07, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Yes, a poorly designed rule can invalidate all kinds of expectations > about behavior. This isn't a bug in my humble opinion. Yes, this was my first impression. I was just surprised because of this: the script CREATE TABLE data ( id INTEGER PRIMARY KEY ); CREATE TABLE ref ( id INTEGER PRIMARY KEY, ref_id INTEGER NULL REFERENCES data(id) ON DELETE CASCADE ); CREATE RULE ref_delete_rule AS ON DELETE TO ref DO INSTEAD NOTHING; INSERT INTO data VALUES (1); INSERT INTO ref (id, ref_id) VALUES(2, 1); DELETE FROM data; gives for the DELETE statement: ERROR: referential integrity query on "data" from constraint "ref_ref_id_fkey" on "ref" gave unexpected result HINT: This is most likely due to a rule having rewritten the query. But if I change the rule by adding a "WHERE True" to it: CREATE RULE ref_delete_rule AS ON DELETE TO ref WHERE True DO INSTEAD NOTHING; The integrity is violated. In this sense, this could reveal a bug (unless postgresql states clearly that it uses a best effort algorithm when dealing with rewrite rules that can potentially rewrites auto-generated statements) I agree, this is not a big deal.