ok, that explains. so i go on with my test, which still doesn't turn out as i expected (PostgreSQL 8.1.10). why??
==in TTY1== (start.sql) create table test (id int4 primary key); --expect sucess insert into test (id) values (1); --expect success ==in TTY2== (tr1a.sql) begin; --expect success drop table test; --expect success create table test (id int4 primary key); --expect success insert into test (id) values (2); --expect success ==in TTY1== SELECT * FROM test; --1. expect no answer now, there's an exclusive lock on "test" from TTY2. ==in TTY2== SELECT * FROM test; --2. expect 1 record, value 2. ==in TTY3== (tr2a.sql) begin; --3. expect success drop table test; --4. expect no answer now, there's an exclusive lock on "test" from TTY2. create table test (id int4 primary key); --5. insert into test (id) values (3); --6. ==in TTY1== --7. expect no answer still, there's an exclusive lock on "test" from TTY2 ==in TTY2== SELECT * FROM test; --8. expect 1 record, value 2 (the exclusive lock is from here) ==in TTY2==(tr1b.sql) insert into test (id) values (4); --9. expect success commit;--10. expect success. transaction 1 (here in TTY2) will be committed, the SELECT (in TTY1) and transaction 2 (in TTY3) can continue. ==in TTY1== --11. expect result at last, value 2 only. (concurrent transaction 2 (in TTY3) completes after this, and will delete values 2 and 4 (added after select was issued) upon commit) --11. true result: ERROR: relation <large nr> deleted while still in use -- remark: I guess transaction2 was faster? This isn't right. the select statement should only see transactions that were committed before it was issued. -- wait, that can't be true, transaction 2 (in TTY3) was rolled back! ==in TTY2== SELECT * FROM test; --12. expect no answer now, there's an exlusive lock on "test" from TTY3, so let it wait --12. true result: 2 records, values 2 and 4. -- remark: transaction 2 was rolled back, so there is now only the result of transaction1 (in TTY2), which is, in itself, correct. ==in TTY3== --message: ERROR: tuple concurrently updated -- remark: ?? Huh? -- ==END OF MY EXERCISE DUE TO ERROR CONDITION== -- --actions as planned below-- SELECT * FROM test; --13. expect 1 record, value 3 (the exclusive lock is from here) ==in TTY3==(tr2b.sql) insert into test (id) values (5); --14. expect success commit;--15. expect success --transaction 2 has been committed,there are no more locks, 2 values remain: 3 and 5. ==TTY1== SELECT * FROM test; --16. expect 3 and 5 ==TTY2== SELECT * FROM test; --17. expect 3 and 5 ==TTY3== SELECT * FROM test; --18. expect 3 and 5
