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.

     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
     up by 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 outstanding trigger events. Or must the
     references follow exactly the above swap? Would be more
     tricky, but IMHO possible anyway.



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) #




Wed, 05 Jan 2000 01:20:02 +0100
Jan Wieck <wieck@debis.com>
Thomas! FOREIGN KEY problem!
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 referenced by
     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 inside the
     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 change while
     adding the event to the queue, he will suppress the real trigger
     call at all if the key's are equal. This saves 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) #




Thu, 06 Jan 2000 21:52:44 +0100
Jan Wieck <wieck@debis.com>
Re: [HACKERS] Thomas! FOREIGN KEY problem!