Обсуждение: 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