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

Re: Referential Integrity corrupted sometimes by Rules

From: Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com>
To: jmfernandez(at)cnb(dot)uam(dot)es, pgsql-bugs(at)postgresql(dot)org
Subject: Re: Referential Integrity corrupted sometimes by Rules
Date: 2001-07-06 19:28:04
Message-ID: Pine.BSF.4.21.0107061221490.75592-100000@megazone23.bigpanda.com (view raw or flat)
Thread:
Lists: pgsql-bugs
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.


In response to

pgsql-bugs by date

Next:From: José María Fernández GonzálezDate: 2001-07-06 19:29:09
Subject: Re: Referential Integrity corrupted sometimes by Rules
Previous:From: Tom LaneDate: 2001-07-06 18:49:26
Subject: Re: Referential Integrity corrupted sometimes by Rules

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