Date: Mon, 7 Oct 2002 17:21:31 -0700 (PDT)
From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: Laurette Cisneros <laurette@nextbus.com>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: foreign key problem
In-Reply-To: <Pine.LNX.4.44.0210071656570.19182-100000@visor.corp.nextbus.com>
Message-ID: <20021007171922.N82652-100000@megazone23.bigpanda.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII


On Mon, 7 Oct 2002, Laurette Cisneros wrote:

>
> I have two tables:
>
> create table table1
>         ( vers                  integer,
>           table1_id             text NOT NULL,
>           desc                  text,
>         PRIMARY KEY (rev, table1)id)
>         );
>
> create table table2
>         ( vers                  integer,
>           othertble_id          text NOT NULL,
>           table1_id             text,
>         FOREIGN KEY (rev, othertable_id) REFERENCES othertable
>                         ON UPDATE CASCADE ON DELETE CASCADE,
>         FOREIGN KEY (rev, table1_id) REFERENCES table1
>                         ON UPDATE CASCADE ON DELETE CASCADE,
>         PRIMARY KEY (rev, othertable_id, table1_id)
>        );
>
> As you can see, table2 has a foreign key reference to table1 and also
> includes on delete cascade (and on update cascade).
>
> There has been a lot of activity in this database on these tables in
> particular in the last several days and somehow we've ended up with rows in
> table2 that have table1_ids that do not exist in table1.
>
> How is this possible?  I've tried to reproduce this, but haven't been able
> to yet.  This has happened to use several times.

The only thing apart from bugs I can think of would be triggers or rules
that forced the implicit deletes to have another behavior.  It'd be
helpful if you can get a representative sequence that reproduces it.