Обсуждение: Referential Integrity corrupted sometimes by Rules

José María Fernández González (jmfernandez@cnb.uam.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
ONUPDATE CASCADE), referential integrity is violated letting entries in the referer table. Even more, referential
integritydoesn't work even if the rule is erased. I was able to reproduce this bug until I got the next message on the
psqlconsole: 

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

pgsql-bugs@postgresql.org writes:
> -- 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;

This rule is preventing the foreign key triggers from performing the
DELETEs implied by your ON DELETE CASCADE command.  If we made the
triggers bypass the rules, we'd get other complaints about that.
At the moment, the rule wins.  If you want to propose a different
behavior, let's hear it.

            regards, tom lane

On Fri, 6 Jul 2001 pgsql-bugs@postgresql.org wrote:

> José María Fernández González (jmfernandez@cnb.uam.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.

The reason removing the rule doesn't work is because the plan gets
cached on first use (you'd see similar behavior in a plpgsql
function).

The question for working with this is, what is the correct behavior
in such a case if you've explicitly turned off deletes for a subset
of the table.  I'd guess it should error with a deleted row still
referenced, but I'm not sure.

Tom Lane wrote:
>
> pgsql-bugs@postgresql.org writes:
> > -- 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;
>

Hi Tom,
first of all, my congratulations to the PostgreSQL team, for this fantastic
ORDBMS. I'm working with it since 6.4.2 version, and each improvement since then
is placing PostgreSQL above the other (commercial and non-commercial) products.

> This rule is preventing the foreign key triggers from performing the
> DELETEs implied by your ON DELETE CASCADE command.  If we made the
> triggers bypass the rules, we'd get other complaints about that.
> At the moment, the rule wins.  If you want to propose a different
> behavior, let's hear it.
But, PostgreSQL makes an external (not internal) difference among triggers and
constraint triggers (described as special and for internal use in the reference
manual). Surely there are many reasons to have only one level of triggers (I'm
thinking on two or three now), but why don't two levels? The first level with
triggers, which can be bypassed by rules, and the second level with constraint
triggers, which cannot be bypassed by them.
Following with the two levels concept, when you define a trigger you could have
a parameter to set the 'unbypassable' behaviour (with the normal one set as
default), so the database designer could choose when she/he/it needs to tune a
complex design.
Best regards,    José María Fernández

--
José María Fernández González        e-mail: jmfernandez@cnb.uam.es
Tlfn:    (+34) 91 585 49 21        Fax:    (+34) 91 585 45 06
Grupo de Diseño de Proteinas        Protein Design Group
Centro Nacional de Biotecnología    National Center of Biotechnology
C.P.: 28049                Zip Code: 28049
Campus Universidad Autónoma. Cantoblanco, Madrid, Spain.