Cascading updates run seperately - Mailing list pgsql-bugs

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