Referential Integrity

This was originally logged under pgsql-sql, as "[SQL] Referential
Integrity", but after some discussion it seems that this is a bug.

I have the following scenario in pg7.3.1.:

CREATE TABLE alex (aid varchar(40) NOT NULL,
bid varchar(100) NOT NULL,
itemdesc varchar(100),
CONSTRAINT alex_pkey PRIMARY KEY (aid, bid));

CREATE TABLE alexette (aid varchar(40) NOT NULL,
bid varchar(100) NOT NULL,
vcode varchar(1),
CONSTRAINT alexette_pkey PRIMARY KEY (aid, bid));

ALTER TABLE alexette
ADD CONSTRAINT alexette_fk_1 FOREIGN KEY (aid,bid)
REFERENCES alex (aid,bid)
ON DELETE CASCADE;

INSERT INTO alex VALUES ('1','1','OneOne');

INSERT INTO alexette VALUES ('1','1','V');

CREATE FUNCTION test() RETURNS INT4 AS '
DECLARE
v_return INTEGER;
BEGIN
DELETE FROM alex
WHERE aid = ''1''
AND bid = ''1'';

INSERT INTO alex (aid,bid,itemdesc)
VALUES (''1'',''1'',''OneOne'');

INSERT INTO alexette (aid,bid,vcode)
VALUES (''1'',''1'',''V'');

RETURN 0;
END;
' LANGUAGE 'plpgsql';

Please could someone tell me why running the function test() causes the
following error:

"Cannot insert a duplicate key into unique index alexette_pkey"

The foreign key with "on delete cascade" should ensure that the delete
statement within this function should delete the records from both alex
and alexette. Indeed a simple "delete from alex" demonstrates that this
does indeed work. However, it appears that within the function this is
not happening or that the insert statements cannot see the full extent
of the changes made by the delete statement. Whichever, surely this is
wrong?

Regards,

Alexander Stanier.
mailto:alex(at)egsgroup(dot)com