Lists: | pgsql-bugs |
---|
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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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.
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Pierre-yves Strub" <pierre(dot)yves(dot)strub(at)gmail(dot)com> |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #3696: FK integrity check bypassed using rules. |
Date: | 2007-10-25 16:29:17 |
Message-ID: | 17523.1193329757@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
"Pierre-yves Strub" <pierre(dot)yves(dot)strub(at)gmail(dot)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
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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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.