X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 514D853966 for ; Sat, 30 Apr 2005 13:19:44 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 66846-02 for ; Sat, 30 Apr 2005 16:19:39 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id AD706535DC for ; Sat, 30 Apr 2005 13:19:38 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j3UGJUrd001936; Sat, 30 Apr 2005 12:19:30 -0400 (EDT) To: Oliver Duke-Williams Cc: pgsql-admin@postgresql.org Subject: Re: Unable to drop index In-reply-to: <426E3057.6010804@leeds.ac.uk> References: <426E3057.6010804@leeds.ac.uk> Comments: In-reply-to Oliver Duke-Williams message dated "Tue, 26 Apr 2005 13:13:11 +0100" Date: Sat, 30 Apr 2005 12:19:30 -0400 Message-ID: <1935.1114877970@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/415 X-Sequence-Number: 17428 Oliver Duke-Williams 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