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 6537653528 for ; Tue, 3 May 2005 08:03:33 -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 09319-05 for ; Tue, 3 May 2005 11:03:31 +0000 (GMT) Received: from mta10-winn.mailhost.ntl.com (smtpout18.mailhost.ntl.com [212.250.162.18]) by svr1.postgresql.org (Postfix) with ESMTP id 6F9D153527 for ; Tue, 3 May 2005 08:03:28 -0300 (ADT) Received: from aamta03-winn.mailhost.ntl.com ([212.250.162.8]) by mta10-winn.mailhost.ntl.com with ESMTP id <20050503110330.IFRJ6374.mta10-winn.mailhost.ntl.com@aamta03-winn.mailhost.ntl.com>; Tue, 3 May 2005 12:03:30 +0100 Received: from [80.1.105.125] by aamta03-winn.mailhost.ntl.com with ESMTP id <20050503110330.VELY1279.aamta03-winn.mailhost.ntl.com@[80.1.105.125]>; Tue, 3 May 2005 12:03:30 +0100 Message-ID: <42775A80.3090103@leeds.ac.uk> Date: Tue, 03 May 2005 12:03:28 +0100 From: Oliver Duke-Williams User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Tom Lane Cc: pgsql-admin@postgresql.org Subject: Re: Unable to drop index References: <426E3057.6010804@leeds.ac.uk> <1935.1114877970@sss.pgh.pa.us> In-Reply-To: <1935.1114877970@sss.pgh.pa.us> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.305 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200505/16 X-Sequence-Number: 17445 Tom Lane wrote: > >>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? The index has OID 149592817; there are two rows in pg_depend relating to the index as a object dependent on the table: zzwicid=# select * from pg_depend where objid = 149592817 or refobjid = 147483811; classid|objid|objsubid|refclassid|refobjid|refobjsubid|deptype 1259|149592817|0|1259|147483811|1|a 1259|149592817|0|1259|147483811|3|a The index was on two fields, so this is as expected. > How about pg_index (see indexrelid and indrelid respectively)? Yes > How about pg_attribute (see attrelid)? Yes - two rows corresponding to the index. > Is the underlying file (named by pg_class.relfilenode) still there? Yes > >>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. OK - will dump / reload the database and then try dropping the index manually. Thanks for your reply, Oliver