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

Referential Integrity corrupted sometimes by Rules

From: pgsql-bugs(at)postgresql(dot)org
To: pgsql-bugs(at)postgresql(dot)org
Subject: Referential Integrity corrupted sometimes by Rules
Date: 2001-07-06 18:08:55
Message-ID: 200107061808.f66I8te51876@hub.org (view raw or flat)
Thread:
Lists: pgsql-bugs
Jos Mara Fernndez Gonzlez (jmfernandez(at)cnb(dot)uam(dot)es) reports a bug with a severity of 1
The lower the number the more severe it is.

Short Description
Referential Integrity corrupted sometimes by Rules

Long Description
If you define an empty rule with a condition over a table which references to another table with ON DELETE CASCADE (or ON UPDATE CASCADE), referential integrity is violated letting entries in the referer table. Even more, referential integrity doesn't work even if the rule is erased. I was able to reproduce this bug until I got the next message on the psql console:

NOTICE:  InvalidateSharedInvalid: cache state reset
NOTICE:  InvalidateSharedInvalid: cache state reset

And then it just worked well again.

Sample Code
-- We suppose we are working with an
-- user called 'prueba'

-- Main table
create table ent (
	pri integer NOT NULL,
	primary key (pri)
);

-- Referenced table
create table rel (
	ref integer NOT NULL,
	FOREIGN KEY (ref) REFERENCES ent(pri) ON DELETE CASCADE ON UPDATE CASCADE
);

-- Inserting test values

insert into ent values (5);
insert into ent values (6);
insert into ent values (7);

insert into rel values (5);
insert into rel values (6);
insert into rel values (7);

select * from ent;

 pri
-----
   5
   6
   7

select * from rel;

 ref
-----
   5
   6
   7

delete from ent where pri=5;

SELECT * FROM ent;

 pri
-----
   6
   7

SELECT * FROM rel;

 ref
-----
   6
   7

-- Rule over the referenced table
-- which breaks referential integrity
create rule nodelrel
as on delete to rel
where old.ref > 3 and user = 'prueba'
do instead nothing;

delete from ent where pri=7;

SELECT * FROM ent;

 pri
-----
   6

SELECT * FROM rel;

 ref
-----
   6
   7

-- Dropping rule doesn't fix it
drop rule nodelrel;

delete from ent where pri=6;

select * from ent;

 ent
-----
(0 rows)

select * from rel;

 ref
-----
   6
   7


No file was uploaded with this report


Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2001-07-06 18:42:54
Subject: Re: Rules with conditions over views don't work
Previous:From: pgsql-bugsDate: 2001-07-06 16:59:53
Subject: Rules with conditions over views don't work

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