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?!
