Re: lost tables

"Josh Goldberg" <josh(at)4dmatrix(dot)com> writes:
> cms3=# drop table view_log;
> NOTICE: RelationBuildDesc: can't open view_log: No such file or directory
> ERROR: cannot open view_log: No such file or directory

You should be able to get to a state where you can drop the table by
doing

touch $PGDATA/base/dbnumber/filenumber

where filenumber is the pg_class.relfilenode entry for the table (19433
here), and dbnumber is the pg_database.oid entry for the database.

If there is still a symlink in that position, either remove it and do
the touch to make a plain file, or do a touch where the symlink points
instead.

You may need to repeat for each index used by the table, if you moved
those off too.

BTW, recent versions (7.3 at least, and I think 7.2) will allow a DROP
TABLE without insisting on finding an underlying file, for example:

regression=# create table z(f1 int);
CREATE TABLE
regression=# select relfilenode from pg_class where relname = 'z';
relfilenode
-------------
2204132
(1 row)

-- as postgres, rm the file 2204132

regression=# drop table z;
WARNING: cannot unlink 2066720/2204132: No such file or directory
DROP TABLE

regards, tom lane