The following bug has been logged online: Bug reference: 1266 Logged by: Aleksey Fedorchenko
Email address: [EMAIL PROTECTED] PostgreSQL version: 8.0 Beta Operating system: MS Windows 2003 Description: Improper unique constraint / MVCC activities within single transaction Details: The following problems were discovered under: 1. postgres (PostgreSQL) 8.0.0beta1 / psql (PostgreSQL) 8.0.0beta1 (native Win32 release) 2. postgres (PostgreSQL) 7.4.2 / psql (PostgreSQL) 7.4.2 (own CygWin 1.5.5 source based build) Test tables definition: /* ======== */ create table buggy_uq ( i integer unique not null ); create table buggy_uq_parent ( i integer primary key ); create table buggy_uq_child ( i integer unique references buggy_uq_parent on delete cascade ); /* ======== */ Test cases: /* ======== */ --case 1 prepare delete from buggy_uq; insert into buggy_uq values (1); insert into buggy_uq values (2); --case 1 test update buggy_uq set i = i + 1; select * from buggy_uq; --expect - SUCCESS --result - ERROR: duplicate key violates unique constraint "buggy_uq_i_key" --case 2 prepare delete from buggy_uq_child; delete from buggy_uq_parent; insert into buggy_uq_parent values (1); insert into buggy_uq_child values (1); --case 2 test delete from buggy_uq_parent; insert into buggy_uq_child values (1); select * from buggy_uq_child; --expect - ERROR: insert or update on table "buggy_uq_child" violates foreign key constraint "$1" --result - ERROR: duplicate key violates unique constraint "buggy_uq_child_i_key" --case 3 prepare delete from buggy_uq_child; delete from buggy_uq_parent; insert into buggy_uq_parent values (1); insert into buggy_uq_child values (1); delete from buggy_uq_child; delete from buggy_uq_parent; --case 3 test insert into buggy_uq_child values (1); select * from buggy_uq_child; --expect - ERROR: insert or update on table "buggy_uq_child" violates foreign key constraint "$1" --result - SUCCESS /* ======== */ Notes and description: As you could you see, the first one is related only to unique constraint itself while second and third are connected with MVCC. On case 1. The result is dependant on values insertion order. For example, if we insert a set of descendant values (e.g. 10,9,8,...) then it works fine othervise (e.g. 1,2,3,...) it fails. Due to the standard it sould work fine in both cases. On case 2 and 3. They are the variances of the single problem - it seems that unique constraint's implicit index is not updated in a moment of value deletion. In the second case we face with problem that values that have to be implicitly deleted from the child table with cascade option still exists in unique index and violates other operation (the shown example slightly differs from real application scenario). In the third case we face with problem that values that were explicitly deleted both from the parent and child tables doesn't exists by itself but still contained in indecies (success child insertion after deletion of parent/child records). It violates operation silently only on transaction commit and followed select returns empty record set. PS: Possibly i'm wrong and all mentioned isn't a bug but feature however in other RDBMS systems (Oracle / MSSQL) such scenarios works fine. ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
