using DROP in a transaction

From: "Willy-Bas Loos" <willybas(at)gmail(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: using DROP in a transaction
Date: 2008-02-07 23:53:59
Message-ID: 1dd6057e0802071553o79f0a158ub120452d377a230a@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Willy-Bas Loos 2008-02-07 23:58:16 Re: using DROP in a transaction
Previous Message Andy Colson 2008-02-07 22:50:36 Re: offline*