Обсуждение: using DROP in a transaction

Hi,

We have a large set of simple queries, that can run in both PostgreSQL and BDE (Corel Paradox backend).
We want to be able to re-create some half-product tables (cache, sortof), while they are (possibly) being accessed by other users. Otherwise there would be a severe performance penalty. We were thinking about re-creating them in a transaction. Concurrent transactions may occur.
But it doesn´t seem to work out.

I tested it this way (in PostgreSQL 8.2.6 and in 8.1.10):
in TTY1, run:
  create table test (id int4 primary key);
  insert into test (id) values (1);

then in TTY2, run
  begin;
  drop table test;
  create table test (id int4 primary key);
  insert into test (id) values (2);

then, in TTY1:
  select * from test;

Actually, i thought of a longer test, but it stops here - there is no response, no prompt anaymore, until i cancel the query.
Is this correct behaviour of PostgreSQL? (i think not..)

The easiest solution would be to just empty the tables instead of dropping them i guess, but this made me wonder about transactions. I've read the chapter on Transaction Isolation, but it doesn't explain the behaviour of DROP.  Anything else i need to know? (OMG transactions are not pure magic ;P )


WBL

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

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

The table 'test' which tty1 was looking at (which was dropped in tty2)
doesn't exist any more.

Postgres doesn't look at the name, it looks at the id that is created
behind the scenes.

So in tty1, the id is 'x'.
Then you recreate the table in tty2 which gives it id 'y'.

So tty1 looking at id 'x' doesn't exist any more.

--
Postgresql & php tutorials
http://www.designmagick.com/