Обсуждение: Error while vacuuming

I get this error when I run a VACUUM:

INFO:  vacuuming "pg_toast.pg_toast_100194"
vacuumdb: vacuuming of database "friend" failed: ERROR:  could not
open segment 1 of relation "pg_toast_100194_index" (target block
1226167840): No such file or directory

I'm assuming I can just delete and recreate the index in question,
although I'm not sure which index or table it's talking about.  Any
ideas?

Akash

Akash Garg <akash.garg@gmail.com> writes:
> I get this error when I run a VACUUM:
> INFO:  vacuuming "pg_toast.pg_toast_100194"
> vacuumdb: vacuuming of database "friend" failed: ERROR:  could not
> open segment 1 of relation "pg_toast_100194_index" (target block
> 1226167840): No such file or directory

> I'm assuming I can just delete and recreate the index in question,
> although I'm not sure which index or table it's talking about.  Any
> ideas?

This probably means a corrupt next-page link in some page of the index.
I'd suggest
    REINDEX TABLE pg_toast.pg_toast_100194;

You might want to do the usual song and dance about memory and disk
hardware checks, too.

            regards, tom lane

Sounds good -- I will try that.  Will this REINDEX lock any queries
that are currently running on the database?  And is there anway to
find out what table this toast table is related to?

Thanks,
Akash

On 7/18/05, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Akash Garg <akash.garg@gmail.com> writes:
> > I get this error when I run a VACUUM:
> > INFO:  vacuuming "pg_toast.pg_toast_100194"
> > vacuumdb: vacuuming of database "friend" failed: ERROR:  could not
> > open segment 1 of relation "pg_toast_100194_index" (target block
> > 1226167840): No such file or directory
>
> > I'm assuming I can just delete and recreate the index in question,
> > although I'm not sure which index or table it's talking about.  Any
> > ideas?
>
> This probably means a corrupt next-page link in some page of the index.
> I'd suggest
>        REINDEX TABLE pg_toast.pg_toast_100194;
>
> You might want to do the usual song and dance about memory and disk
> hardware checks, too.
>
>                        regards, tom lane
>

Akash Garg <akash.garg@gmail.com> writes:
> Sounds good -- I will try that.  Will this REINDEX lock any queries
> that are currently running on the database?  And is there anway to
> find out what table this toast table is related to?

IIRC, 100194 is the OID of the owning table.

            regards, tom lane

I believe the correct way is the following.

1. get the oid from pg_class for relname='pg_toast_100194'
2. lookup up the relname from pg_class where reltoastrelid = the oid from the first query.

so on my system
select oid from pg_class where relname='pg_toast_17070';
  oid
-------
 17072
(1 row)

select relname from pg_class where reltoastrelid=17072;
   relname
--------------
 sql_features
(1 row)


---------- Original Message -----------
From: Akash Garg <akash.garg@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-general@postgresql.org
Sent: Mon, 18 Jul 2005 10:35:40 -0700
Subject: Re: [GENERAL] Error while vacuuming

> Sounds good -- I will try that.  Will this REINDEX lock any queries
> that are currently running on the database?  And is there anway to
> find out what table this toast table is related to?
>
> Thanks,
> Akash
>
> On 7/18/05, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > Akash Garg <akash.garg@gmail.com> writes:
> > > I get this error when I run a VACUUM:
> > > INFO:  vacuuming "pg_toast.pg_toast_100194"
> > > vacuumdb: vacuuming of database "friend" failed: ERROR:  could not
> > > open segment 1 of relation "pg_toast_100194_index" (target block
> > > 1226167840): No such file or directory
> >
> > > I'm assuming I can just delete and recreate the index in question,
> > > although I'm not sure which index or table it's talking about.  Any
> > > ideas?
> >
> > This probably means a corrupt next-page link in some page of the index.
> > I'd suggest
> >        REINDEX TABLE pg_toast.pg_toast_100194;
> >
> > You might want to do the usual song and dance about memory and disk
> > hardware checks, too.
> >
> >                        regards, tom lane
> >
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
------- End of Original Message -------