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