Skip site navigation (1) Skip section navigation (2)

BUG #3696: FK integrity check bypassed using rules.

From: "Pierre-yves Strub" <pierre(dot)yves(dot)strub(at)gmail(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #3696: FK integrity check bypassed using rules.
Date: 2007-10-25 13:28:29
Message-ID: 200710251328.l9PDSTPg091717@wwwmaster.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged online:

Bug reference:      3696
Logged by:          Pierre-yves Strub
Email address:      pierre(dot)yves(dot)strub(at)gmail(dot)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.

Responses

pgsql-bugs by date

Next:From: Gregory StarkDate: 2007-10-25 14:18:04
Subject: Re: Yet another problem with ILIKE and UTF-8
Previous:From: Gergely BorDate: 2007-10-25 12:40:46
Subject: Yet another problem with ILIKE and UTF-8

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group