Serializable access giving wrong error messages? - Mailing list pgsql-bugs

I have some problems in serializable access. Either I have miss
understood the documentation or there is a bug in PostgreSQL.

I have two examples. On both of them I have two psql windows open. I
start two transactions simultaniously. I shouldn't be able to see any
results (except the message 'ERROR: Can't serialize access due to
concurrent update') from the other transaction. At least this
is how I understood it by reading PostgreSQL 7.2 User's Guide's
chapter 9.4 (Serializable Isolation Level). But as you can see from
the two examples I can see changes in the table which are part of
another transaction.

Example one:

In the transaction one I first check that there isn't a duplicate row
in the table. Then I try to insert in it and I get 'ERROR:  Cannot
insert a duplicate key into unique index test_pkey'. This shouldn't be
possible.

Transaction 1 (window 1)         ! Transaction 2 (window 2)
---------------------------------------------------------------------------
testi=# create table test ( a    !
integer, primary key (a));       !
NOTICE:  CREATE TABLE / PRIMARY  !
KEY will create implicit index   !
'test_pkey' for table 'test'     !
CREATE                           !
testi=# begin;                   !
BEGIN                            !
testi=# set transaction isolation!
level serializable;              !
SET VARIABLE                     !
                                 ! testi=# begin;
                                 ! BEGIN
                                 ! testi=# set transaction isolation
                 ! level serializable;
                                 ! SET VARIABLE
testi=# select * from test;      !
 a                               !
---                              !
(0 rows)                         !
                                 ! testi=# insert into test (a)
                                 ! values(5);
                                 ! INSERT 121643 1
                                 ! testi=# commit;
                                 ! COMMIT
testi=# select * from test;      !
 a                               !
---                              !
(0 rows)                         !
testi=# insert into test (a)     !
values(5);                       !
ERROR:  Cannot insert a duplicate!
key into unique index test_pkey  !
testi=# commit;                  !
COMMIT                           !
testi=# drop table test;         !
DROP                             !


Example 2:

I first delete a row with a primary key '5'. Then I try to insert a
new row with the same primary key but get: 'ERROR: Cannot insert a
duplicate key into unique index test_pkey'.

Transaction 1 (window 1)         ! Transaction 2 (window 2)
---------------------------------------------------------------------------
testi=# create table test ( a    !
integer, primary key (a));       !
NOTICE:  CREATE TABLE / PRIMARY  !
KEY will create implicit index   !
'test_pkey' for table 'test'     !
CREATE                           !
testi=# begin;                   !
BEGIN                            !
testi=# set transaction isolation!
level serializable;              !
SET VARIABLE                     !
                                 ! testi=# begin;
                                 ! BEGIN
                                 ! testi=# set transaction isolation
                                 ! level serializable;
                                 ! SET VARIABLE
testi=# delete from test where   !
a=5;                             !
DELETE 0                         !
                                 ! testi=# insert into test (a)
                                 ! values(5);
                                 ! INSERT 149083 1
                                 ! testi=# commit;
                                 ! COMMIT
testi=# insert into test (a)     !
values(5);                       !
ERROR:  Cannot insert a duplicate!
key into unique index test_pkey  !
testi=# commit;                  !
COMMIT                           !
testi=# drop table test;         !
DROP                             !


I'm running PostgreSQL 7.2b4. I got similar results with 7.1.2. And I
know that on both cases the transaction should fail but the error
message is wrong.