Re: Cascades Failing in 8.0.x

Jake Stride wrote:
> I seem to be having some problems with cascading updates, I seem to
> remember that this worked in the database in 7.4 but seems to fail in 8,
> can anyone give me any pointer please as I seem to be able to find
> anything about this online.

> No when I try to do an update I get the following error:
> 
> update users set username='new' where username='old';
> ERROR:  insert or update on table "company" violates foreign key
> constraint "$5"
> DETAIL:  Key (alteredby)=(old) is not present in table "users".
> CONTEXT:  SQL statement "UPDATE ONLY "public"."company" SET "assigned" =
> $1 WHERE "assigned" = $2"
> 
> surely this should not fail because of the 'ON UPDATE CASCADE'?

Hmm - I can reproduce it on 8.0.2 on my Mac laptop, but the following 
works fine on version 7.4.7 which I also have here.

--
-- Begin test
--
CREATE TABLE foo (a int, b text, PRIMARY KEY (a));
CREATE TABLE bar (
   x int,
   y int NOT NULL REFERENCES foo(a) ON UPDATE CASCADE,
   z int NOT NULL REFERENCES foo(a) ON UPDATE CASCADE
);

INSERT INTO foo VALUES (1,'aaa');
INSERT INTO foo VALUES (2,'bbb');
INSERT INTO bar VALUES (101, 1, 1);
INSERT INTO bar VALUES (102, 2, 2);

UPDATE foo SET a=3 WHERE a=1;
--
-- End test
--

There is something similar mentioned on the bugs list - "Cascading 
updates run seperately". Does this look like your problem?

-- 
   Richard Huxton
   Archonet Ltd