[prev in list] [next in list] [prev in thread] [next in thread]
List: pgsql-bugs
Subject: Re: [BUGS] BUG #3696: FK integrity check bypassed using rules.
From: "Pierre-Yves Strub" <pierre.yves.strub () gmail ! com>
Date: 2007-10-25 16:55:03
Message-ID: 592647410710250955k98ebf33u23d90dc24d0f2ad1 () mail ! gmail ! com
[Download RAW message or body]
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.
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic