Re: [HACKERS] 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(at)debis(dot)com (Jan Wieck) #