Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.10.1/8.10.1) with ESMTP id e73F5aG59404 for ; Thu, 3 Aug 2000 11:05:36 -0400 (EDT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id LAA08039; Thu, 3 Aug 2000 11:05:10 -0400 (EDT) To: Carolyn Lu Wong cc: "pgsql-sql@postgresql.org" Subject: Re: What is happening? In-reply-to: <3988FF7C.F023BCC@kss.net.au> References: <3988FF7C.F023BCC@kss.net.au> Comments: In-reply-to Carolyn Lu Wong message dated "Thu, 03 Aug 2000 15:13:32 +1000" Date: Thu, 03 Aug 2000 11:05:09 -0400 Message-ID: <8036.965315109@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200008/23 Carolyn Lu Wong writes: > I was trying to dump data, drop then recreate table, and import data > back to the table. There were errors during the process and the > transaction was not committed. > ERROR: mdopen: couldn't open accounts: No such file or directory. Rolling back a "drop table" doesn't work at the moment :-(, because the physical table file is deleted at the moment of DROP. When you aborted the transaction, the system-table rows for the table came back to life, but the file didn't. You can get back into a consistent state by creating a dummy table file by hand, eg do touch .../data/base/yourdb/accounts and then you will be able to do the DROP TABLE. After that you can recreate and reload the table from the data file (which I hope you kept...) One of the things on the to-do list is to postpone physical delete of table files till COMMIT, so that a DROP can be rolled back safely. (Not likely for 7.1, but maybe for 7.2.) In the meantime, it's probably best not to do DROP inside a transaction. 7.0 will emit a notice warning you about this, but earlier versions don't. BTW, ALTER TABLE RENAME in a transaction is equally dangerous for the same kind of reason. regards, tom lane