Re: Delete / F/K error

On Fri, Dec 30, 2005 at 02:38:48PM -0800, CSN wrote:
> I'm still confused what the problem was.

I think the problem is related to having multiple foreign key
constraints with ON DELETE SET NULL referencing the same target.
The triggers that enforce those constraints are fired one at a time
with a query like "UPDATE ONLY tbl SET col = NULL WHERE col = val".
Each update changes only one column; the other columns still have
their old values, so when the update checks those columns' foreign
key constraints you get an error because the referenced key has
already been deleted. Interestingly, this only appears to be a
problem if the delete takes place in the same (sub)transaction that
inserted the referencing row. Example:

test=> CREATE TABLE foo (
test(> id integer PRIMARY KEY
test(> );
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo"
CREATE TABLE
test=>
test=> CREATE TABLE bar (
test(> foo_id1 integer REFERENCES foo ON DELETE SET NULL,
test(> foo_id2 integer REFERENCES foo ON DELETE SET NULL
test(> );
CREATE TABLE
test=> BEGIN;
BEGIN
test=> INSERT INTO foo VALUES (1);
INSERT 0 1
test=> INSERT INTO bar VALUES (1, 1);
INSERT 0 1
test=> DELETE FROM foo WHERE id = 1;
ERROR: insert or update on table "bar" violates foreign key constraint "bar_foo_id2_fkey"
DETAIL: Key (foo_id2)=(1) is not present in table "foo".
CONTEXT: SQL statement "UPDATE ONLY "public"."bar" SET "foo_id1" = NULL WHERE "foo_id1" = $1"
test=> ROLLBACK;
ROLLBACK

But:

test=> BEGIN;
BEGIN
test=> INSERT INTO foo VALUES (1);
INSERT 0 1
test=> INSERT INTO bar VALUES (1, 1);
INSERT 0 1
test=> COMMIT;
COMMIT
test=> DELETE FROM foo WHERE id = 1;
DELETE 1

And:

test=> BEGIN;
BEGIN
test=> INSERT INTO foo VALUES (1);
INSERT 0 1
test=> INSERT INTO bar VALUES (1, 1);
INSERT 0 1
test=> SAVEPOINT x;
SAVEPOINT
test=> DELETE FROM foo WHERE id = 1;
DELETE 1
test=> COMMIT;
COMMIT

And:

test=> BEGIN;
BEGIN
test=> INSERT INTO foo VALUES (1);
INSERT 0 1
test=> SAVEPOINT x;
SAVEPOINT
test=> INSERT INTO bar VALUES (1, 1);
INSERT 0 1
test=> RELEASE x;
RELEASE
test=> DELETE FROM foo WHERE id = 1;
DELETE 1
test=> COMMIT;
COMMIT

Any developers following this? Is this behavior bogus or correct?
The above examples are in 8.1.1 from CVS.

--
Michael Fuhr