pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
What is happening?
3+ messages / 3 participants
[nested] [flat]

* What is happening?
@ 2000-08-03 05:13 Carolyn Lu Wong <carolyn@kss.net.au>
  2000-08-03 11:41 ` Re: What is happening? The Hermit Hacker <scrappy@hub.org>
  2000-08-03 15:05 ` Re: What is happening? Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 2 replies; 3+ messages in thread

From: Carolyn Lu Wong @ 2000-08-03 05:13 UTC (permalink / raw)
  To: pgsql-sql

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.



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: What is happening?
  2000-08-03 05:13 What is happening? Carolyn Lu Wong <carolyn@kss.net.au>
@ 2000-08-03 11:41 ` The Hermit Hacker <scrappy@hub.org>
  1 sibling, 0 replies; 3+ messages in thread

From: The Hermit Hacker @ 2000-08-03 11:41 UTC (permalink / raw)
  To: Carolyn Lu Wong <carolyn@kss.net.au>; +Cc: pgsql-sql

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
...





^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: What is happening?
  2000-08-03 05:13 What is happening? Carolyn Lu Wong <carolyn@kss.net.au>
@ 2000-08-03 15:05 ` Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 3+ messages in thread

From: Tom Lane @ 2000-08-03 15:05 UTC (permalink / raw)
  To: Carolyn Lu Wong <carolyn@kss.net.au>; +Cc: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2000-08-03 15:05 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-08-03 05:13 What is happening? Carolyn Lu Wong <carolyn@kss.net.au>
2000-08-03 11:41 ` The Hermit Hacker <scrappy@hub.org>
2000-08-03 15:05 ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox