Thread: Corrupted table

In a Perl script I ended up trying to UPDATE a row in my database with a
value that was out of the range. Now whenever I try to do something with
the table I get the error:

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

I can't even drop the table, despite the fact that it shows up when I
enter '\dt' in psql. I could probably run destroydb on the
database that contains the table, but I would prefer not too (although if
it comes to it, it wouldn't be too devastating). Is there anyway to fix
this problem.

Note: The type of the field that was out of range is int4. When I
recreate the table I'm planning to make it of type int8, but would like
to know if there is a type for unsigned integers?

Thanks,
Rob Mosher

Robert Mosher <mosher@andrews.edu> writes:
> In a Perl script I ended up trying to UPDATE a row in my database with a
> value that was out of the range. Now whenever I try to do something with
> the table I get the error:

>     ERROR:  mdopen: couldn't open tablename: No such file or directory

It seems unlikely that the one led to the other.  The known ways to
cause that type of failure involve other things, like trying to roll
back a DROP TABLE.

How old is your Postgres?

> I can't even drop the table, despite the fact that it shows up when I
> enter '\dt' in psql. I could probably run destroydb on the
> database that contains the table,

If it's called "destroydb" and not "dropdb", then the answer to my
question is "too old".  Consider an update, soon.

            regards, tom lane

> Robert Mosher <mosher@andrews.edu> writes:
> > In a Perl script I ended up trying to UPDATE a row in my database with a
> > value that was out of the range. Now whenever I try to do something with
> > the table I get the error:
>
> >     ERROR:  mdopen: couldn't open tablename: No such file or directory
>
> It seems unlikely that the one led to the other.  The known ways to
> cause that type of failure involve other things, like trying to roll
> back a DROP TABLE.
>
> How old is your Postgres?

It's version 6.5.3 . Does this limit my solutions then?

>
> > I can't even drop the table, despite the fact that it shows up when I
> > enter '\dt' in psql. I could probably run destroydb on the
> > database that contains the table,
>
> If it's called "destroydb" and not "dropdb", then the answer to my
> question is "too old".  Consider an update, soon.
>

Rob Mosher

On Mon, 8 Oct 2001, Tom Lane wrote:

> Robert Mosher <mosher@andrews.edu> writes:
> > In a Perl script I ended up trying to UPDATE a row in my database with a
> > value that was out of the range. Now whenever I try to do something with
> > the table I get the error:
>
> >     ERROR:  mdopen: couldn't open tablename: No such file or directory
>
> It seems unlikely that the one led to the other.  The known ways to
> cause that type of failure involve other things, like trying to roll
> back a DROP TABLE.
>
> How old is your Postgres?
>
> > I can't even drop the table, despite the fact that it shows up when I
> > enter '\dt' in psql. I could probably run destroydb on the
> > database that contains the table,
>
> If it's called "destroydb" and not "dropdb", then the answer to my
> question is "too old".  Consider an update, soon.
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

Well I didn't kill it, though it did shut itself down at one
point after trying to access the corrupted database. Trying to insert the
out of range value was the last thing that happened before the problems
started, so I have no other ideas to what may have caused it though.

So no suggestions to recover things, or at least only kill the one table,
and not the entire database?

Thanks,
Rob Mosher

Robert Mosher <mosher@andrews.edu> writes:
> So no suggestions to recover things, or at least only kill the one table,
> and not the entire database?

You should be able to drop the broken table after doing

    touch $PGDATA/base/yourdb/yourtable

(make the obvious substitutions...)  Your immediate problem is you
have a pg_class entry for a table and no underlying file to match.

            regards, tom lane

Thanks. All is now well.

Rob Mosher