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 SET DEFAULT
"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 have values 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, body int 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, body int 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


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

On Thu, 11 Aug 2005, Allan Wang wrote:

> I'm running a fairly recent CVS head server, but I think this bug
> applies in all versions.

It doesn't happen for me in 7.4.2 with the example below, although my
couple month old CVS server and an 8.0.x server do error. My first guess
is that there's some side effect of one of the trigger timing changes
that's causing this, but I haven't looked yet.

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

On Thu, 11 Aug 2005, Stephan Szabo wrote:

> On Thu, 11 Aug 2005, Allan Wang wrote:
>
> > I'm running a fairly recent CVS head server, but I think this bug
> > applies in all versions.
>
> It doesn't happen for me in 7.4.2 with the example below, although my
> couple month old CVS server and an 8.0.x server do error. My first guess
> is that there's some side effect of one of the trigger timing changes
> that's causing this, but I haven't looked yet.

I think I have a theory for why the timing change would have affected
this. The check in trigger.c to decide if the key has changed only abort
the check if the key has changed and the row was not made by this
transaction.

In the deferred case, you could have an insert / update combination where
the insert trigger sees the row it's checking is no longer live and
doesn't do any checks, and then if the update only checked changed keys
some might be missed entirely. I think for the case given in the example
if the constraint were deferred it would work because the second update
would have made the first update's check no longer live either, and so
only the final state is checked.

In the immediate case where the checks were deferred to end of outer
statement (7.4 and below), I believe the same applies. By the time the
first check caused by the first update is run, the second update has
happened, so the first check doesn't actually do anything.

In the immediate case where the checks run directly on the update run by
the constraint (8.0 and above), the check happens before the second update
so the first check (with the half changed key) runs on both keys which
fails.

I don't think we can simply change the immediate case behavior to
unconditionally check that the key has changed because that might break
for an update inside an after insert trigger that updates the same row (if
the update happened before the insert's check. Is there a way to detect
this case that wouldn't also catch two updates caused by separate on
updates for an action?


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings