BUG #1266: Improper unique constraint / MVCC activities within single transaction

The following bug has been logged online:

Bug reference: 1266
Logged by: Aleksey Fedorchenko

Email address: alexey_f(at)ukr(dot)net

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.