[BUGS] BUG #3696: FK integrity check bypassed using rules.

[Pierre-yves Strub]

The following bug has been logged online:

Bug reference:  3696
Logged by:  Pierre-yves Strub
Email address:  [EMAIL PROTECTED]
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.

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [BUGS] BUG #3696: FK integrity check bypassed using rules.

[Pierre-Yves Strub]

On 10/25/07, Tom Lane <[EMAIL PROTECTED]> 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.

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq