Skip site navigation (1) Skip section navigation (2)

Re: Unable to drop index

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Oliver Duke-Williams <o(dot)w(dot)duke-williams(at)leeds(dot)ac(dot)uk>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: Unable to drop index
Date: 2005-04-30 16:19:30
Message-ID: 1935.1114877970@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-admin
Oliver Duke-Williams <o(dot)w(dot)duke-williams(at)leeds(dot)ac(dot)uk> writes:
> The table on which the index was built is no longer present (it should 
> have been dropped by my application after being used), but it would 
> appear that the index was not dropped at the same time as the table:

That's fairly strange; it should be impossible, in fact, because of the
pg_depend mechanism.

> drop index iext_41827440000_24 ;
> ERROR:  could not open relation with OID 147483811

> Looking in pg_class for an entry with this OID (147483811) returns 0 
> rows; an entry exists in pg_class for the index itself.

What is the OID of the index itself?  Are there any rows in pg_depend
matching either the index OID or 147483811 in either objid or refobjid?
How about pg_index (see indexrelid and indrelid respectively)?  How
about pg_attribute (see attrelid)?  Is the underlying file (named by
pg_class.relfilenode) still there?

> How can I safely remove the index?  The old copy of the index is 
> preventing the creation of a new table and associated index of the same 
> names.

You can probably just "rm" the underlying file and DELETE the pg_class
row plus any other rows you found above.  However I wonder what other
corruption may have occurred in whatever event produced this situation.
I'm thinking you might have lost a whole page of pg_class, for example.
It'd likely be a good idea to see if you can pg_dump and reload the
database.

			regards, tom lane

In response to

Responses

pgsql-admin by date

Next:From: Tom LaneDate: 2005-04-30 16:27:36
Subject: Re: Backing up users?
Previous:From: Bruno Wolff IIIDate: 2005-04-30 12:18:12
Subject: Re: Backing up users?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group