X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 6AA2FD6EF7 for ; Wed, 7 Sep 2005 14:12:25 -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 93254-04 for ; Wed, 7 Sep 2005 17:12:21 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 72FA4D7E85 for ; Wed, 7 Sep 2005 14:12:20 -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 j87HCKul028275; Wed, 7 Sep 2005 13:12:20 -0400 (EDT) To: Adam Witney , pgsql-admin , Michael Fuhr Subject: Re: pg_dump error... Follow up In-reply-to: <28205.1126112786@sss.pgh.pa.us> References: <28205.1126112786@sss.pgh.pa.us> Comments: In-reply-to Tom Lane message dated "Wed, 07 Sep 2005 13:06:26 -0400" Date: Wed, 07 Sep 2005 13:12:20 -0400 Message-ID: <28274.1126113140@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 required=5 tests=[AWL=0.006] X-Spam-Level: X-Archive-Number: 200509/65 X-Sequence-Number: 18802 I wrote > Adam Witney writes: >> I think I have found the offending row in measured_bioassay_base... The >> entry in its toast table looks like this >> bugasbase2=# select chunk_id, chunk_seq, length(chunk_data) from >> pg_toast.pg_toast_134401982 where chunk_id = 144391872; >> chunk_id | chunk_seq | length >> -----------+-----------+-------- >> 144391872 | 0 | 1998 >> 144391872 | 1 | 1998 >> 144391872 | 2 | 1998 >> 144391872 | 3 | 1998 >> 144391872 | 4 | 1998 >> 7625296 | 3292 | 24 >> 7625297 | 3292 | 24 >> 7625298 | 3292 | 24 >> 7625299 | 3292 | 24 >> 7625308 | 3292 | 19 > This is pretty wacko, because as far as I can see there is nothing wrong > with the index at all --- in particular, no gap in the chunk id/seq > series here, according to pg_filedump. Actually, it's also possible that the index is fine and the problem is in the underlying toast table ... which would be unfortunate, because it'd mean that there's no easy way out like a REINDEX or database restart. We can check this by seeing if the data looks the same using the CTIDs that we can see in the index. Please try the same query as above, ie, select chunk_id, chunk_seq, length(chunk_data) from pg_toast.pg_toast_134401982 where ... using these WHERE conditions: ctid = '(165390,4)' ctid = '(165391,1)' ctid = '(165391,2)' ctid = '(165391,3)' ctid = '(165391,4)' ctid = '(165392,1)' ctid = '(165392,2)' ctid = '(165392,3)' ctid = '(165392,4)' It'd also be interesting to look at pg_filedump data for pages 165390 through 165392 of the toast table itself (not the index) --- see -R option of pg_filedump to limit the range of pages. regards, tom lane