The following bug has been logged online:
Bug reference: 4019
Logged by: Jan Strube
Email address: js@deriva.de
PostgreSQL version: 8.3
Operating system: openSUSE Linux 10.2
Description: Comparison of user defined domain doesn't work
Details:
Hi,
I think when cascading a DELETE to a dependent table Postgres doesn't
compare the whole related fields when they are of an user defined DOMAIN
type.
I have two tables "z_base" and "z_ul". "z_base"'s primary key is of type
"isin" which is referenced by "z_ul". "isin" is a DOMAIN of type
CHARACTER(12). When I delete a record from "z_base", all records of "z_ul"
whose "isin" has the same first character are also deleted. On Postgres
8.2.6 it's working as intended but not on 8.3.
Here is a test case:
create schema test;
create domain test.isin as character(12);
create table test.z_base (id test.isin primary key);
create table test.z_ul (id test.isin references test.z_base(id) on update
cascade on delete cascade);
insert into test.z_base values ('a111');
insert into test.z_base values ('a222');
insert into test.z_base values ('x333');
insert into test.z_ul values ('a111');
insert into test.z_ul values ('a222');
insert into test.z_ul values ('x333');
select * from test.z_base join test.z_ul using (id) where z_base.id =
'a222';
-- gives a222, ok
delete from test.z_base where id = 'a222';
select * from test.z_ul;
-- on PG 8.2.6. gives ('a111', 'x333'), ok
-- on PG 8.3 gives just 'x333', so 'a111' has also been deleted!!!
Regard,
Jan Strube
