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.
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 |