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