I'm running a fairly recent CVS head server, but I think this bug
applies in all versions.
talluria=# delete from items;
ERROR: insert or update on table "players" violates foreign key constraint "players_accessory1_fkey"
DETAIL: Key (accessory1)=(90205) is not present in table "items".
CONTEXT: SQL statement "UPDATE ONLY "public"."players" SET "head" = NULL WHERE "head" = $1"
players
"users_pkey" PRIMARY KEY, btree (playerid) CLUSTER
"players_name_key" UNIQUE, btree (name)
"players_coord" btree (mapid, x, y)
"players_lastactive_key" btree (lastactive)
"players_username_lkey" btree (lower(name::text))
Foreign-key constraints:
"players_accessory1_fkey" FOREIGN KEY (accessory1) REFERENCES items(itemid) ON UPDATE CASCADE ON DELETE SET NULL
"players_accessory2_fkey" FOREIGN KEY (accessory2) REFERENCES items(itemid) ON UPDATE CASCADE ON DELETE SET NULL
"players_accessory3_fkey" FOREIGN KEY (accessory3) REFERENCES items(itemid) ON UPDATE CASCADE ON DELETE SET NULL
"players_feet_fkey" FOREIGN KEY (feet) REFERENCES items(itemid) ON UPDATE CASCADE ON DELETE SET NULL
"players_stylesheet_fkey" FOREIGN KEY (stylesheet) REFERENCES stylesheets(stylesheetid) ON UPDATE CASCADE ON DELETE
SETDEFAULT
"users_arm" FOREIGN KEY (arm) REFERENCES items(itemid) ON UPDATE CASCADE ON DELETE SET NULL
"users_belt" FOREIGN KEY (belt) REFERENCES items(itemid) ON UPDATE CASCADE ON DELETE SET NULL
"users_body" FOREIGN KEY (body) REFERENCES items(itemid) ON UPDATE CASCADE ON DELETE SET NULL
"users_head" FOREIGN KEY (head) REFERENCES items(itemid) ON UPDATE CASCADE ON DELETE SET NULL
"users_lefthand" FOREIGN KEY (lefthand) REFERENCES items(itemid) ON UPDATE CASCADE ON DELETE SET NULL
"users_leg" FOREIGN KEY (leg) REFERENCES items(itemid) ON UPDATE CASCADE ON DELETE SET NULL
"users_righthand" FOREIGN KEY (righthand) REFERENCES items(itemid) ON UPDATE CASCADE ON DELETE SET NULL
talluria=# \d items
Table "public.items"
Column | Type | Modifiers
-------------+---------+----------------------------------------------------
itemid | integer | not null default nextval('items_itemid_seq'::text)
itemdataid | integer | not null default 0
playerid | integer |
quantity | integer | not null default 1
elementflag | integer | not null default 0
shopid | integer |
map | integer |
x | integer |
y | integer |
price | integer |
Indexes:
"items_pkey" PRIMARY KEY, btree (itemid)
"items_coord" btree (map, x, y)
"items_playerid_idx" btree (playerid)
Foreign-key constraints:
"items_playerid_fkey" FOREIGN KEY (playerid) REFERENCES players(playerid) ON UPDATE CASCADE ON DELETE CASCADE
Triggers:
test_valid_item_trig BEFORE INSERT OR UPDATE ON items FOR EACH ROW EXECUTE PROCEDURE valid_item()
I'm told this:
<AndrewSN> I think the problem is that the cascading updates all run separately
<AndrewSN> which means that the one that does SET head = NULL, for example, fails because all the other fields still
havevalues pointing at the deleted row
<AndrewSN> or at _a_ deleted row
I made a testcase, but after some retrying (without the mistakes I made) I was unable to reproduce it anymore..
allan=# create table items (itemid serial, playerid int);
NOTICE: CREATE TABLE will create implicit sequence "items_itemid_seq1" for serial column "items.itemid"
CREATE TABLE
allan=# create table items (itemid serial, playerid int);
allan=# create table players (playerid serial, head int references items(itemid) on update cascade on delete set null,
bodyint references items(itemid) on update cascade on delete set null);
NOTICE: CREATE TABLE will create implicit sequence "players_playerid_seq1" for serial column "players.playerid"
ERROR: there is no unique constraint matching given keys for referenced table "items"
allan=# \d items
Table "public.items"
Column | Type | Modifiers
----------+---------+------------------------------------------------------------
itemid | integer | not null default nextval('public.items_itemid_seq1'::text)
playerid | integer |
allan=# create unique index
information_schema. pg_temp_1. plays_pkey public. videos_pkey
pg_catalog. pg_toast. plays_videoid_key videos_path_key
allan=# create unique index items_pkey on items using btree(itemid);
CREATE INDEX
allan=# create table players (playerid serial, head int references items(itemid) on update cascade on delete set null,
bodyint references items(itemid) on update cascade on delete set null);
NOTICE: CREATE TABLE will create implicit sequence "players_playerid_seq1" for serial column "players.playerid"
CREATE TABLE
allan=# insert into players
allan=# \d players
Table "public.players"
Column | Type | Modifiers
----------+---------+----------------------------------------------------------------
playerid | integer | not null default nextval('public.players_playerid_seq1'::text)
head | integer |
body | integer |
Foreign-key constraints:
"players_body_fkey" FOREIGN KEY (body) REFERENCES items(itemid) ON UPDATE CASCADE ON DELETE SET NULL
"players_head_fkey" FOREIGN KEY (head) REFERENCES items(itemid) ON UPDATE CASCADE ON DELETE SET NULL
allan=# insert into players default values;
INSERT 0 1
allan=# \d items
Table "public.items"
Column | Type | Modifiers
----------+---------+------------------------------------------------------------
itemid | integer | not null default nextval('public.items_itemid_seq1'::text)
playerid | integer |
Indexes:
"items_pkey" UNIQUE, btree (itemid)
allan=# insert into items (playerid) values ((select playerid from players limit 1));
INSERT 0 1
allan=# insert into items (playerid) values ((select playerid from players limit 1));
INSERT 0 1
allan=# update players set head=(select itemid from items order by itemid asc limit 1);
UPDATE 1
allan=# update players set body=(select itemid from items order by itemid desc limit 1);
UPDATE 1
allan=# select * from players;
playerid | head | body
----------+------+------
1 | 1 | 2
(1 row)
allan=# select * from items;
itemid | playerid
--------+----------
1 | 1
2 | 1
(2 rows)
allan=# begin;
BEGIN
allan=# delete from items;
DELETE 2
allan=# rollback;
ROLLBACK
allan=# select * from players;
playerid | head | body
----------+------+------
1 | 1 | 2
(1 row)
allan=# begin;
BEGIN
allan=# delete from items;
DELETE 2
allan=# select * from players;
playerid | head | body
----------+------+------
1 | NULL | NULL
(1 row)
allan=# select * from items;
itemid | playerid
--------+----------
(0 rows)
allan=# rollback;
ROLLBACK
allan=# select * from items;
itemid | playerid
--------+----------
1 | 1
2 | 1
(2 rows)
allan=# select * from players
allan-# ;
playerid | head | body
----------+------+------
1 | 1 | 2
(1 row)
allan=# update players set bo
allan=# begin;
BEGIN
allan=# update players set body=1;
UPDATE 1
allan=# delete from items;
ERROR: insert or update on table "players" violates foreign key constraint "players_body_fkey"
DETAIL: Key (body)=(1) is not present in table "items".
CONTEXT: SQL statement "UPDATE ONLY "public"."players" SET "head" = NULL WHERE "head" = $1"
allan=# rollback;l
ROLLBACK
allan-#
allan=# begin;
BEGIN
allan=# select * from items;
itemid | playerid
--------+----------
1 | 1
2 | 1
(2 rows)
allan=# select * from players;
playerid | head | body
----------+------+------
1 | 1 | 2
(1 row)
allan=# update players set head=2, body=1;
UPDATE 1
allan=# delete from items;
ERROR: insert or update on table "players" violates foreign key constraint "players_head_fkey"
DETAIL: Key (head)=(2) is not present in table "items".
CONTEXT: SQL statement "UPDATE ONLY "public"."players" SET "body" = NULL WHERE "body" = $1"
Allan Wang