Re: [SQL] Re:Create table doesn't always respect atomicity of transactions - Mailing list pgsql-sql
From | José Soares |
---|---|
Subject | Re: [SQL] Re:Create table doesn't always respect atomicity of transactions |
Date | |
Msg-id | 37722D3F.E6AC7D29@sferacarta.com Whole thread Raw |
In response to | Re:Create table doesn't always respect atomicity of transactions (Mark Dalphin <mdalphin@amgen.com>) |
Responses |
Re: [SQL] Re:Create table doesn't always respect atomicity of transactions
|
List | pgsql-sql |
There's the same problem in v6.5 using temporary tables: prova=> begin; BEGIN prova=> create temporary table prova(a text); CREATE prova=> insert into prova values('aa'); INSERT 225134 1 prova=> rollback; ABORT prova=> create table prova(a text); ERROR: Relation 'prova' already exists prova=> drop table prova; ERROR: cannot find attribute 1 of relation pg_temp.17407.0 prova=> Mark Dalphin ha scritto: > Hi, > > Running version 6.4.2 of Postgresql on IRIX, I see exactly this same behavior, > that is, when I create a table within a transaction and some later action in > that transaction generates an error, leaving the transaction in an abort state, > leaves me stuck with a ghost table. I can create a new table of the same name > because "Table already exists" and I can't delete the table because that table > "doesn't exist". > > I can, however, get around the problem by becoming the Postgres super-user and > going into the data/base/... > directory; there I find the file with the name of the ghost table (it is of size > zero) and I delete it. The problem then goes away. I assume this bug arises > from the order in which Postgres creates entries in its system tables and > creates the file on the disk. As I am not creating tables on the fly, the bug > only bites me when I am creating a new DB. > > In short, I can confirm the behavior you are seeing and I believe it occurs on > multiple platforms. I can send in code to re-create the problem is people wish. > > Mark > > ---------------------- > Zalman Stern wrote: > > > Date: Mon, 21 Jun 1999 22:21:48 -0700 (PDT) > > From: Zalman Stern > > Subject: Create table doesn't always respect atomicity of transactions. > > > > Off and on, I've been seeing a situation where the following code: > > begin; > > create table foo (name text); > > abort; leaves a file called "foo" in the database directory and > > further attempts to create a relation called foo or to select anything from > > it all fail. The database has been left in an inconsistent state. > > > > I filed a bug report on this earlier today as it seemed dead on repeatable. > > But then I recompiled with debug symbols to have a go at figuring out what > > was up and the problem went away. So I recompiled with full optimization > > again and the problem still doesn't occur now. I've been starting over each > > time with a fresh database so if it was some property of the database > > itself, then that state is lost. But this is not the first time I've seen > > this. Has any one else seen such a thing? Its rather troublesome 'cause when > > it does happen, the database is somewhat unuseable until I remove the file > > in question and I hate going in and removing files that are supposed to be > > under Postgres' control... > > > > > -- > Mark Dalphin email: mdalphin@amgen.com > Mail Stop: 29-2-A phone: +1-805-447-4951 (work) > One Amgen Center Drive +1-805-375-0680 (home) > Thousand Oaks, CA 91320 fax: +1-805-499-9955 (work) -- ______________________________________________________________ PostgreSQL 6.5.0 on i586-pc-linux-gnu, compiled by gcc 2.7.2.3 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ Jose'