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

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

From: "Pierre-Yves Strub" <pierre(dot)yves(dot)strub(at)gmail(dot)com>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #3696: FK integrity check bypassed using rules.
Date: 2007-10-25 16:55:03
Message-ID: 592647410710250955k98ebf33u23d90dc24d0f2ad1@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-bugs
On 10/25/07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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.

In response to

pgsql-bugs by date

Next:From: MichaelDate: 2007-10-25 20:09:10
Subject: PostgreSQL crash on Freebsd 7
Previous:From: Tom LaneDate: 2007-10-25 16:39:01
Subject: Re: BUG #3697: utf8 issue: can not reimport a table that was successfully exported.

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