Обсуждение: Thomas! FOREIGN KEY problem!

Damned,

    while hacking down a little test suite for FOREIGN KEY    (just to have some script based checking while doing
thefile buffering of the event queue), I discovered    something looking wrong.
 
    Having the following table schema:

         CREATE TABLE t1 (             a    int4 PRIMARY KEY,             b    int4         );
         CREATE TABLE t2 (             c    int4,             d    int4,
             CONSTRAINT t2_d_t1_a FOREIGN KEY (d)                 REFERENCES t1 MATCH FULL                 ON UPDATE
CASCADE                DEFERRABLE INITIALLY IMMEDIATE         );
 
    I can do the following:

         BEGIN;         SET CONSTRAINTS ALL DEFERRED;         UPDATE t1 SET a = 99 WHERE a = 1;         UPDATE t1 SET a
=1  WHERE a = 2;         UPDATE t1 SET a = 2  WHERE a = 99;         COMMIT;
 
    to swap t1.a 1<->2.
    The result (due to my internal condensing of trigger    events) is, that all references to the OLD.a=1 will end
upby referencing to NEW.a=1. In fact, they should    point to 2. What I'm unable to figure out from the SQL3    specs
is,what is the correct behaviour in this case?
 
    The simple solution would be, to bomb out at the third    UPDATE with a "triggered data change violation"
exception.Rows, resulting from the first UPDATE    (identified by XMIN) are subject to change again, and    there are
outstandingtrigger events. Or must the    references follow exactly the above swap? Would be more    tricky, but IMHO
possibleanyway.
 



Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#========================================= wieck@debis.com (Jan Wieck) #

I wrote:

> Damned,
>
>      while hacking down a little test suite for FOREIGN KEY
>      (just to have some script based checking while doing
>      the file buffering of the event queue), I discovered
>      something looking wrong.
    After rereading the part of the SQL3 spec in question, I saw that    the checks I did for "triggered data change
violation"where    wrong.
 
    The just committed changes to the trigger manager and related    areas cause ANY second change of a value, possibly
referencedby    a foreign key, to bomb out with the above exception. So the    example below doesn't work any more.
 
    That means, that a row cannot get deleted, if it has been    inserted or possibly referenced attributes updated
insidethe    same transaction. Also, possibly referenced attributes cannot be    changed twice inside one and the same
transaction.The previous    "event condensing" is gone.
 
    The benefit is, that since the trigger manager now checks for    RI_FKey... triggers, if the referenced attributes
changewhile    adding the event to the queue, he will suppress the real trigger    call at all if the key's are equal.
Thissaves fetching back OLD    and NEW at the time, the checks have to be executed.
 

>      Having the following table schema:
>
>           CREATE TABLE t1 (
>               a    int4 PRIMARY KEY,
>               b    int4
>           );
>
>           CREATE TABLE t2 (
>               c    int4,
>               d    int4,
>
>               CONSTRAINT t2_d_t1_a FOREIGN KEY (d)
>                   REFERENCES t1 MATCH FULL
>                   ON UPDATE CASCADE
>                   DEFERRABLE INITIALLY IMMEDIATE
>           );
>
>      I can do the following:
>
>           BEGIN;
>           SET CONSTRAINTS ALL DEFERRED;
>           UPDATE t1 SET a = 99 WHERE a = 1;
>           UPDATE t1 SET a = 1  WHERE a = 2;
>           UPDATE t1 SET a = 2  WHERE a = 99;
>           COMMIT;
>
>      to swap t1.a 1<->2.



Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#========================================= wieck@debis.com (Jan Wieck) #