Thread: What is happening?

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.

The table is there when I do '\d' and the table definition is there.
However, I can't drop table nor select from this table. I always get the
following error:

ERROR: mdopen: couldn't open accounts: No such file or directory.

On Thu, 3 Aug 2000, Carolyn Lu Wong wrote:

> 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.
> 
> The table is there when I do '\d' and the table definition is there.
> However, I can't drop table nor select from this table. I always get the
> following error:
> 
> ERROR: mdopen: couldn't open accounts: No such file or directory.

what version of PostgreSQL are you running, first off ... what I've done
in the past that has been successful is to go into the
data/base/<database> directory, do a 'touch accounts' so that there is a
file there for the postgres process to unlink, and then do the drop table
...

Carolyn Lu Wong <carolyn@kss.net.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
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