Обсуждение: referential integrity

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

It seems that cascading across multiple tables does not work
correctly, when combining different action types. For example, given:

CREATE TABLE a (anum Integer PRIMARY KEY);
CREATE TABLE b (bnum Integer PRIMARY KEY,
        anum Integer REFERENCES a ON DELETE CASCADE);
CREATE TABLE c (cnum Integer PRIMARY KEY,
        bnum Integer REFERENCES b ON DELETE CASCADE,
        anum Integer REFERENCES a ON DELETE SET NULL);

INSERT INTO a (anum) VALUES (1);
INSERT INTO b (bnum, anum) VALUES (1,1);
INSERT INTO c (cnum, bnum, anum) VALUES (1,1,1);

This passes without an error:

delete from b where bnum = 1;
delete from a where anum = 1;

but this fails:

delete from a where anum = 1;

with this error:

ERROR:  <unnamed> referential integrity violation - key referenced from c
not found in b

Got any ideas? :o

Ian Turner
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.1 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQE5rvvXfn9ub9ZE1xoRAqEkAJ0aFgzuoaK23xdnzsE7ADGrCYErRQCfbEjy
X/J9RlqAPHOwnHGlQHTmFRE=
=BgK8
-----END PGP SIGNATURE-----

On Thu, 31 Aug 2000, Ian Turner wrote:

> It seems that cascading across multiple tables does not work
> correctly, when combining different action types. For example, given:
>
> CREATE TABLE a (anum Integer PRIMARY KEY);
> CREATE TABLE b (bnum Integer PRIMARY KEY,
>         anum Integer REFERENCES a ON DELETE CASCADE);
> CREATE TABLE c (cnum Integer PRIMARY KEY,
>         bnum Integer REFERENCES b ON DELETE CASCADE,
>         anum Integer REFERENCES a ON DELETE SET NULL);
>
> INSERT INTO a (anum) VALUES (1);
> INSERT INTO b (bnum, anum) VALUES (1,1);
> INSERT INTO c (cnum, bnum, anum) VALUES (1,1,1);
>
> This passes without an error:
>
> delete from b where bnum = 1;
> delete from a where anum = 1;
>
> but this fails:
>
> delete from a where anum = 1;
>
> with this error:
>
> ERROR:  <unnamed> referential integrity violation - key referenced from c
> not found in b
>
> Got any ideas? :o

I think I see what's happening.
It's doing the following order:
 Delete from b
 Update to c (which checks the keys and fails).
 [It would then do the delete from c but its already dead]

That could actually be a triggered data change violation actually since
the statement causes a row in c to be modified twice.  In fact, probably
any situation that could cause this sort of arrangement would fall into
this bracket, but there could be valid ones too.

The explicit cause is that the update is causing a check even though
the value isn't actually changed which might be bug in itself.  However,
I'm not sure that it's safe to change that, due to cases where if you
say did a ON DELETE SET DEFAULT, it should fail if you've deleted the
one value in the main table that is associated with that default
value (even if our value was the default before).

Ian Turner wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> It seems that cascading across multiple tables does not work
> correctly, when combining different action types. For example, given:
>
> CREATE TABLE a (anum Integer PRIMARY KEY);
> CREATE TABLE b (bnum Integer PRIMARY KEY,
>         anum Integer REFERENCES a ON DELETE CASCADE);
> CREATE TABLE c (cnum Integer PRIMARY KEY,
>         bnum Integer REFERENCES b ON DELETE CASCADE,
>         anum Integer REFERENCES a ON DELETE SET NULL);
>
> INSERT INTO a (anum) VALUES (1);
> INSERT INTO b (bnum, anum) VALUES (1,1);
> INSERT INTO c (cnum, bnum, anum) VALUES (1,1,1);
>
> This passes without an error:
>
> delete from b where bnum = 1;
> delete from a where anum = 1;
>
> but this fails:
>
> delete from a where anum = 1;
>
> with this error:
>
> ERROR:  <unnamed> referential integrity violation - key referenced from c
> not found in b
>
> Got any ideas? :o

    From the ri_triggers.c sourcecode in the area that checks for
    key existance on UPDATE:

        /* ----------
         * Note:
         * We cannot avoid the check on UPDATE, even if old and new
         * key are the same. Otherwise, someone could DELETE the PK
         * that consists of the DEFAULT values, and if there are any
         * references, a ON DELETE SET DEFAULT action would update
         * the references to exactly these values but we wouldn't see
         * that weired case (this is the only place to see it).
         * ----------
         */

    What happens in your case is that the DELETE FROM a fires two
    actions:

    -  DELETE the referencing row from b
    -  UPDATE the referencing row in c to NULL.

    Of  course,  the DELETE FROM b "queues" another DELETE FROM c
    because of the cascaded dependancy there. But at the time  of
    the  cascaded  UPDATE  that hasn't happened, while the DELETE
    FROM b has.

    So the constraint on c.anum does  an  UPDATE  ...  SET  NULL,
    which  in turn causes a check that c.anum and c.bnum exist in
    a and b (c.anum's constraint is satisfied because  it's  NULL
    now, but c.bnum's is violated).

    Even  if  we  wouldn't check because c.bnum didn't change, it
    shouldn't work. That's because if the  constraint  on  c.anum
    already did an UPDATE to the row, it is not allowed to DELETE
    it or UPDATE again in the same transaction. This  is  defined
    as "triggered data change violation" in the SQL99 specs.

    The  only  way to make it work is to add "INITIALLY DEFERRED"
    to the "anum" constraint of table "c".  In that case, the SET
    NULL  operation  is  delayed until transaction commit, and by
    then all the DELETES already  happened.  Therefore,  the  SET
    NULL action doesn't find any references to update.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #