Обсуждение: Vacuum problem

Friends,
    I have a problem with my database: whenever I do a vacuum I get a message
that reads:

NOTICE:  RelationBuildDesc: can't open pg_temp_25807_9: No such file or
directory
ERROR:  _mdfd_getrelnfd: cannot open relation pg_temp_25807_9: No such file
or directory

    It seems that a temp table was dropped from the db without being deleted
from pg_class.

    My questions are these:  Is this an indication that there's something
horribly wrong with my db?  If I just delete the record from pg_class
(delete from pg_class where relname='pg_temp_25807_9';) will I cause more
harm to the db or fix the problem?  If I do have a big problem, what
suggestions do people have for resolving it?

Thanks,
Peter Darley

"Peter Darley" <pdarley@kinesis-cem.com> writes:
>     I have a problem with my database: whenever I do a vacuum I get a message
> that reads:

> NOTICE:  RelationBuildDesc: can't open pg_temp_25807_9: No such file or
> directory
> ERROR:  _mdfd_getrelnfd: cannot open relation pg_temp_25807_9: No such file
> or directory

>     It seems that a temp table was dropped from the db without being deleted
> from pg_class.

Hmm.  What PG version are you running?

>     My questions are these:  Is this an indication that there's something
> horribly wrong with my db?  If I just delete the record from pg_class
> (delete from pg_class where relname='pg_temp_25807_9';) will I cause more
> harm to the db or fix the problem?

I'd say delete it, and also delete the similarly-named row in pg_type.
If you wanted to be really rigorous you could try cleaning out the
related rows in pg_attribute and other system tables, but getting rid
of the named pg_class and pg_type rows is probably enough to forestall
any problems.

            regards, tom lane

Tom,
    Sorry, I always forget to post the version.  I'm running 7.2.1 on Linux.
    I deleted the records from pg_class and pg_type, and vacuum succeeded
without any errors.
Thanks a bunch,
Peter Darley


-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Monday, August 12, 2002 4:31 PM
To: Peter Darley
Cc: Pgsql-General
Subject: Re: [GENERAL] Vacuum problem


"Peter Darley" <pdarley@kinesis-cem.com> writes:
>     I have a problem with my database: whenever I do a vacuum I get a message
> that reads:

> NOTICE:  RelationBuildDesc: can't open pg_temp_25807_9: No such file or
> directory
> ERROR:  _mdfd_getrelnfd: cannot open relation pg_temp_25807_9: No such
file
> or directory

>     It seems that a temp table was dropped from the db without being deleted
> from pg_class.

Hmm.  What PG version are you running?

>     My questions are these:  Is this an indication that there's something
> horribly wrong with my db?  If I just delete the record from pg_class
> (delete from pg_class where relname='pg_temp_25807_9';) will I cause more
> harm to the db or fix the problem?

I'd say delete it, and also delete the similarly-named row in pg_type.
If you wanted to be really rigorous you could try cleaning out the
related rows in pg_attribute and other system tables, but getting rid
of the named pg_class and pg_type rows is probably enough to forestall
any problems.

            regards, tom lane

"Peter Darley" <pdarley@kinesis-cem.com> writes:
> Sorry, I always forget to post the version.  I'm running 7.2.1 on Linux.

Drat ... I was hoping it was old ;-)

>     I deleted the records from pg_class and pg_type, and vacuum succeeded
> without any errors.

I'm glad you got out of the problem, but you shouldn't have got into it.
There's a bug there somewhere, and I need more data to try to look for
it.  Have you got any ideas about what might have triggered this
situation --- for example, were there any database crashes recently?
System crashes?  Odd behavior of any kind?

            regards, tom lane

Tom,
    The machine did have a crash recently.  The scsi controller stopped
responding, and I was unable to shut down the machine correctly.  I
eventually just turned it off.  When it came back up and I started
Postgresql everything seemed OK.  I don't think that this problem occurred
then however.  I believe that it started after that, tho I can't be totally
sure.  I seem to remember doing a full vacuum after the machine came back up
and not getting any errors.
    I haven't been able to troubleshoot and replace any bad hardware yet.
Thanks,
Peter Darley

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Monday, August 12, 2002 6:51 PM
To: Peter Darley
Cc: Pgsql-General
Subject: Re: [GENERAL] Vacuum problem


"Peter Darley" <pdarley@kinesis-cem.com> writes:
> Sorry, I always forget to post the version.  I'm running 7.2.1 on Linux.

Drat ... I was hoping it was old ;-)

>     I deleted the records from pg_class and pg_type, and vacuum succeeded
> without any errors.

I'm glad you got out of the problem, but you shouldn't have got into it.
There's a bug there somewhere, and I need more data to try to look for
it.  Have you got any ideas about what might have triggered this
situation --- for example, were there any database crashes recently?
System crashes?  Odd behavior of any kind?

            regards, tom lane