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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-bugs by date

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