From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: Brian Blaha <bblaha@umr.edu>
Cc: pgsql-sql@postgresql.org
Subject: Re: Apparent referential integrity bug in PL/pgSQL
Date: Fri, 18 Oct 2002 08:05:46 -0700 (PDT)
Message-ID: <20021018080228.K23987-100000@megazone23.bigpanda.com> (raw)
In-Reply-To: <3DAFB514.7080002@umr.edu>
On Fri, 18 Oct 2002, Brian Blaha wrote:
> I have a function that operates on two tables A and B, such that B has a
> foreign key on A, as follows:
> INSERT INTO A (...) several times
> INSERT INTO B (...) several times, with foreign keys pointing to the new
> members of A
> DELETE FROM A (...), possibly including some of the newly added members
>
> Even though B's foreign key is defined ON DELETE CASCADE, I get a
> referential integrity
> violation when I run this function. If instead, I comment out the DELETE
> statement, start a
> transaction block, run the function, run the DELETE statement, and end
> the transaction, no
> errors occur. To run those statements with one function call, I need to
> split the INSERTs and
> DELETEs into separate functions, and call them separately from a third
> function. I am using
> version 7.2.2. Has this been corrected in the beta versions or can
> someone confirm this for me?
No it hasn't, but I see what's happening. The rows from b are being
checked before they are deleted by the trigger that runs after the check.
I think that to do this case right, all of the ref actions would need
to be done before any of the noaction or check triggers. This is
technically what the spec says to do afaict, but I don't think we'd seen a
case before where it matters.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: sszabo@megazone23.bigpanda.com, bblaha@umr.edu
Subject: Re: Apparent referential integrity bug in PL/pgSQL
In-Reply-To: <20021018080228.K23987-100000@megazone23.bigpanda.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox