Re: Referential Integrity corrupted sometimes by Rules

On Fri, 6 Jul 2001 pgsql-bugs(at)postgresql(dot)org wrote:

> 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.

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.