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.
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
