Message-ID: <3DAFB514.7080002@umr.edu>
Date: Fri, 18 Oct 2002 07:15:32 +0000
From: Brian Blaha <bblaha@umr.edu>
User-Agent: Mozilla/5.0 (Windows; U; Win98; en-US; rv:1.0.1) Gecko/20020815
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Apparent referential integrity bug in PL/pgSQL
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

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?