From: | "Willy-Bas Loos" <willybas(at)gmail(dot)com> |
---|---|
To: | "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: using DROP in a transaction |
Date: | 2008-02-08 13:18:01 |
Message-ID: | 1dd6057e0802080518q280bf72bpcb25bb58a0ecc8ea@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | luca.ciciriello | 2008-02-08 13:27:40 | hyperthreading and pqlib |
Previous Message | Harald Fuchs | 2008-02-08 10:48:41 | Re: 8.2/8.3 incompatibility |