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<http://www.postgresql.org/docs/8.1/interactive/transaction-iso.html>, but it doesn't explain the behaviour of DROP. Anything else i need to know? (OMG transactions are not pure magic ;P ) WBL
