Re: using DROP in a transaction - Mailing list pgsql-general

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