Thread: Cascades Failing
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.
I have several tables, but the two I am having issue with are:
\d users
Table "public.users"
Column | Type | Modifiers
------------------+-------------------+-----------
username | character varying | not null
password | character(32) | not null
lastcompanylogin | bigint |
Indexes:
"users_pkey" PRIMARY KEY, btree (username)
Foreign-key constraints:
"$1" FOREIGN KEY (lastcompanylogin) REFERENCES company(id) ON UPDATE
CASCADE ON DELETE CASCADE
\d company
Table "public.company"
Column | Type |
Modifiers
-----------------+-----------------------------+---------------------------------------------------------
id | bigint | not null default
nextval('public.company_id_seq'::text)
name | character varying | not null
accountnumber | character varying | not null
creditlimit | integer |
vatnumber | character varying |
companynumber | character varying |
www | character varying |
employees | integer |
companyid | bigint | not null
branchcompanyid | bigint |
owner | character varying | not null
assigned | character varying |
added | timestamp without time zone | not null default now()
updated | timestamp without time zone | not null default now()
alteredby | character varying |
Indexes:
"company_pkey" PRIMARY KEY, btree (accountnumber, companyid)
"company_accountnumber_key" UNIQUE, btree (accountnumber)
"company_id_key" UNIQUE, btree (id)
"company_accountnumber" btree (accountnumber)
"company_alteredby" btree (alteredby)
"company_assigned" btree (assigned)
"company_branchcompanyid" btree (branchcompanyid)
"company_companyid" btree (companyid)
"company_name" btree (name)
"company_owner" btree ("owner")
Check constraints:
"company_accountdetails" CHECK (name::text <> ''::text AND
accountnumber::text <> ''::text)
"company_branchcompanyid" CHECK (id <> branchcompanyid)
Foreign-key constraints:
"$1" FOREIGN KEY (companyid) REFERENCES company(id) ON UPDATE
CASCADE ON DELETE CASCADE
"$2" FOREIGN KEY (branchcompanyid) REFERENCES company(id) ON UPDATE
CASCADE ON DELETE CASCADE
"$3" FOREIGN KEY ("owner") REFERENCES users(username) ON UPDATE
CASCADE ON DELETE CASCADE
"$4" FOREIGN KEY (assigned) REFERENCES users(username) ON UPDATE
CASCADE ON DELETE SET NULL
"$5" FOREIGN KEY (alteredby) REFERENCES users(username) ON UPDATE
CASCADE ON DELETE SET NULL
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'?
Thanks
Jake
On Tue, 16 Aug 2005, 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. I think this is similar to the issue recently reported on -bugs. My theory there was that trigger timing changes between 7.4 and 8.0 seems to have caused the sequence of checks inside the trigger manager and triggers that prevented intermediate states from being checked to become broken.
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
Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> I think this is similar to the issue recently reported on -bugs. My
> theory there was that trigger timing changes between 7.4 and 8.0 seems to
> have caused the sequence of checks inside the trigger manager and triggers
> that prevented intermediate states from being checked to become broken.
Just looking at the example, I think that the issue is that we fire a
trigger for one of the FK constraints, it does an UPDATE to fix the
constraint it knows about, and then on the way out of that UPDATE
statement, check triggers for all of the FK constraints are executed
and the ones that haven't been fixed yet are unhappy. (The failure
occurs because two independent updates are needed on the same row of
the referencing table, and only one has been done yet.) So the problem
comes directly from the fact that FK triggers can fire at the ends of
nested statements, rather than only at the outer level as they did
before.
This suggests that we need a way to prevent immediate execution of
freshly queued triggers at the end of a command fired by an FK trigger.
If we could move them to the end of the trigger queue that the FK
operation itself is in, things would work reasonably well I think.
regards, tom lane