Thread: Problem with referential integrity within functions (bug?)
Hi all! I have the following table, which actually represents a tree: ------------------------------------------------ CREATE TABLE Forum ( ForumId INTEGER CONSTRAINT Forum_PK PRIMARY KEY, ParentId INTEGER CONSTRAINT Forum_FK1 REFERENCES Forum (ForumId) ON DELETE SET NULL, LeftId INTEGER, RightId INTEGER, Ranking INTEGER DEFAULT 0, Name VARCHAR(100), Description TEXT ); ------------------------------------------------ I have filled the table with some data: ------------------------------------------------ > SELECT forumId, parentId FROM Forum; forumid | parentid ---------+---------- 0 | 0 1 | 0 2 | 0 3 | 0 4 | 3 (5 rows) ------------------------------------------------ Now the interesting part: see the following function which does only two things, deleting a row and doing an update afterwards (presumably on the ramaining rows. Dont take it too seriously, this is only for demonstration purposes): ------------------------------------------------ CREATE OR REPLACE FUNCTION DropTest() RETURNS BOOLEAN AS ' DELETE FROM Forum WHERE ForumId = 0; UPDATE Forum SET leftId = 0; SELECT true; ' LANGUAGE 'sql'; ------------------------------------------------ and now I get the following: ------------------------------------------------ => select DropTest(); ERROR: forum_fk1 referential integrity violation - key referenced from forum not found in forum ------------------------------------------------ The funny thing is, that if I do these two statements outside of the function, there is no violation: ------------------------------------------------ => DELETE FROM Forum WHERE ForumId = 0; UPDATE Forum SET leftId = 0; DELETE 1 UPDATE 4 ------------------------------------------------ Am I missing something !? I'm using 7.2 on linux ... so long Peter
On Wed, 3 Apr 2002, Peter Sojan wrote: > > Hi all! > > I have the following table, which actually represents a tree: > > ------------------------------------------------ > CREATE TABLE Forum ( > ForumId INTEGER CONSTRAINT Forum_PK PRIMARY KEY, > ParentId INTEGER CONSTRAINT Forum_FK1 REFERENCES Forum (ForumId) > ON DELETE SET NULL, > LeftId INTEGER, > RightId INTEGER, > Ranking INTEGER DEFAULT 0, > Name VARCHAR(100), > Description TEXT > ); > ------------------------------------------------ > > I have filled the table with some data: > > ------------------------------------------------ > > SELECT forumId, parentId FROM Forum; > forumid | parentid > ---------+---------- > 0 | 0 > 1 | 0 > 2 | 0 > 3 | 0 > 4 | 3 > (5 rows) > ------------------------------------------------ > > Now the interesting part: see the following function which does only two > things, deleting a row and doing an update afterwards (presumably on the > ramaining rows. Dont take it too seriously, this is only for demonstration > purposes): > > ------------------------------------------------ > CREATE OR REPLACE FUNCTION DropTest() RETURNS BOOLEAN AS ' > DELETE FROM Forum WHERE ForumId = 0; > UPDATE Forum SET leftId = 0; > SELECT true; > ' LANGUAGE 'sql'; > ------------------------------------------------ > > and now I get the following: > > ------------------------------------------------ > => select DropTest(); > ERROR: forum_fk1 referential integrity violation - key referenced from forum not found in forum > ------------------------------------------------ > > The funny thing is, that if I do these two statements outside of the function, > there is no violation: > > ------------------------------------------------ > => DELETE FROM Forum WHERE ForumId = 0; UPDATE Forum SET leftId = 0; > DELETE 1 > UPDATE 4 > ------------------------------------------------ > > Am I missing something !? > I'm using 7.2 on linux ... You may wish to try the fk patch I sent to -patches a couple of weeks ago which may fix the issue.
On Tue, Apr 02, 2002 at 03:48:29PM -0800, Stephan Szabo wrote: > > You may wish to try the fk patch I sent to -patches a couple of weeks > ago which may fix the issue. With the help of Neil Conway on IRC I managed to apply this patch to 7.1.2. The error messages are gone ... THX :) so long Peter