Skip site navigation (1) Skip section navigation (2)

Serializable access giving wrong error messages?

From: Mikko Vierula <mikko(dot)vierula(at)elektroniikkatyo(dot)fi>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Serializable access giving wrong error messages?
Date: 2001-12-21 09:41:57
Message-ID: 3C2303E5.D31016BE@elektroniikkatyo.fi (view raw or flat)
Thread:
Lists: 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.

Responses

pgsql-bugs by date

Next:From: NicolaiDate: 2001-12-21 10:36:30
Subject: Triggers in 6.5.3
Previous:From: Paul VixieDate: 2001-12-20 18:38:48
Subject: casting TEXT to CIDR (or to INET) has to be possible

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group