I'm running PostgreSQL 9.5.3.
I am trying to understand why I see a difference in behavior between the two routines below. I find this behavior counter-intuitive, but there may be a very good reason for it; I just want to know what it is if so.
Setting ISOLATION LEVEL REPEATABLE READ does not seem to take effect until after the first SELECT statement.
The only difference between the two routines is that in "Routine 2" I put in a superfluous SELECT 1 ; statement, whereas in "Routine 1" I did not do this. I got my desired results in "Routine 2".
See my (overly-lengthy) question that I posted earlier in which I incorrectly assumed the behavior I was seeing was something to do with what particular tables I was querying.
I've modified the routine from krokodilko's answer to demonstrate what I'm seeing. Thanks, krokodilko!
These are meant to be executed serially, in the order listed, switching back and forth between two separate sessions.
Routine 1
Session 1:
testdb=# CREATE TABLE t1( x int ) ;
CREATE TABLE
testdb=# INSERT INTO t1 VALUES (1),(2),(3) ;
INSERT 0 3
Session 2:
testdb=# START TRANSACTION ISOLATION LEVEL REPEATABLE READ ;
START TRANSACTION
Session 1:
testdb=# DELETE FROM t1 WHERE x = 2 ;
DELETE 1
Session 2:
testdb=# SELECT * FROM t1 ;
x
---
1
3
(2 rows)
(why am I seeing the effects from session 1 here?)
Session 2:
testdb=# COMMIT ;
COMMIT
Session 1:
testdb=# CREATE TABLE t1( x int ) ;
CREATE TABLE
testdb=# INSERT INTO t1 VALUES (1),(2),(3) ;
INSERT 0 3
Session 2:
testdb=# START TRANSACTION ISOLATION LEVEL REPEATABLE READ ;
START TRANSACTION
testdb=# SELECT 1 ;
?column?
----------
1
(1 row)
(why should I have to do this?)
Session 1:
testdb=# DELETE FROM t1 WHERE x = 2 ;
DELETE 1
Session 2:
testdb=# SELECT * FROM t1 ;
x
---
1
2
3
(3 rows)
(that's what I expected to see!)
Session 2:
testdb=# COMMIT ;
COMMIT
testdb=# SELECT * FROM t1 ;
x
---
1
3
(2 rows)
(that's also what I expected to see)
