Re: using DROP in a transaction

this is really bugging me.
am i doing something stupid?

On Fri, Feb 8, 2008 at 2:18 PM, Willy-Bas Loos <willybas(at)gmail(dot)com> wrote:

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