How do concurrent inserts work?

Hi. After reading this:

http://postgresql.nabble.com/Re-BUG-12330-ACID-is-broken-for-unique-constraints-td5832085.html

I've come to conclusion that I don't understand PostgreSQL transaction
isolation. :(

Here's the example:
> CREATE TABLE t(a INT PRIMARY KEY);
> INSERT INTO t VALUES(1);

-- Test number 1:
> START TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> SAVEPOINT a;
> INSERT INTO t VALUES(1);
-- This results in 'duplicate key' error, so I reason there is a row with
this value, check it:
> ROLLBACK TO SAVEPOINT a;
> SELECT * FROM t WHERE a = 1;
1 -- 1 row. So yes, there is such row.
> COMMIT; -- done with this test


-- Test number 2:
> START TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> SELECT * FROM t WHERE a = 1;
1 -- 1 row
> SAVEPOINT a;

In other session> INSERT INTO t VALUES(2);
-- Back to my session:
> INSERT INTO t VALUES(2);
-- This results in 'duplicate key' error, so I reason there is a row with
this value, check it:
> ROLLBACK TO SAVEPOINT a;
> SELECT * FROM t WHERE a = 2;
-- 0 rows
-- So, I reason... Stop, what? Error told me that there IS such row, but now
I see there ISN'T?!

Can you enlighten me?




-----
WBR, Yaroslav Schekin.
--
View this message in context: http://postgresql.nabble.com/How-do-concurrent-inserts-work-tp5832157.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.