Carolyn Lu Wong <carolyn(at)kss(dot)net(dot)au> 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
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
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
In response to
pgsql-sql by date
|Next:||From: Tom Lane||Date: 2000-08-03 15:20:44|
|Subject: Re: a question about dates and timestamp |
|Previous:||From: Antti Linno||Date: 2000-08-03 13:49:34|
|Subject: Extracting data by months|