Received: from castle-smtp.amgen.com (firewall-user@ns2.amgen.com [138.133.17.7]) by hub.org (8.9.3/8.9.3) with ESMTP id MAA30972 for ; Wed, 23 Jun 1999 12:35:41 -0400 (EDT) (envelope-from mdalphin@amgen.com) Received: by castle-smtp.amgen.com; id JAA21276; Wed, 23 Jun 1999 09:35:39 -0700 (PDT) Received: from mailbag.amgen.com(138.133.10.78) by castle-smtp.amgen.com via smap (3.2) id xmaa21234; Wed, 23 Jun 99 09:35:19 -0700 Received: from amgen.com (mahunui [138.133.147.23]) by mailbag.amgen.com (8.8.5/8.8.5) with ESMTP id JAA06650 for ; Wed, 23 Jun 1999 09:33:11 -0700 (PDT) Sender: mdalphin@amgen.com Message-ID: <37710CDC.150C6ADE@amgen.com> Date: Wed, 23 Jun 1999 09:35:40 -0700 From: Mark Dalphin Organization: Amgen, Inc. X-Mailer: Mozilla 4.6C-SGI [en] (X11; U; IRIX64 6.5 IP30) X-Accept-Language: en MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: Re:Create table doesn't always respect atomicity of transactions Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit 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)