Re: Problem with referential integrity within functions

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.