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 C7A5FD8357 for ; Thu, 8 Sep 2005 06:36:18 -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 36867-08 for ; Thu, 8 Sep 2005 09:36:13 +0000 (GMT) Received: from mailhub2.sghms.ac.uk (mailhub2.sghms.ac.uk [194.82.51.8]) by svr1.postgresql.org (Postfix) with ESMTP id BAA72D7AB1 for ; Thu, 8 Sep 2005 06:36:13 -0300 (ADT) Received: from [194.82.51.24] (helo=imail) by mailhub2.sghms.ac.uk with esmtp (Exim 4.24) id 1EDIoz-0008ET-VX; Thu, 08 Sep 2005 10:35:53 +0100 Received: from [172.16.20.3] (mrc1-003.sghms.ac.uk [172.16.20.3]) by imail.sghms.ac.uk (iPlanet Messaging Server 5.2 HotFix 2.06 (built Mar 28 2005)) with ESMTPA id <0IMH005OVRZT95@imail.sghms.ac.uk>; Thu, 08 Sep 2005 10:35:53 +0100 (BST) Date: Thu, 08 Sep 2005 10:32:41 +0100 From: Adam Witney Subject: Re: pg_dump error... Follow up In-reply-to: <28274.1126113140@sss.pgh.pa.us> To: Tom Lane , pgsql-admin , Michael Fuhr Message-id: MIME-version: 1.0 Content-type: text/plain; charset=US-ASCII Content-transfer-encoding: 7BIT User-Agent: Microsoft-Entourage/10.1.1.2418 X-MailScanner-Information: Please contact sysadmin at sghms.ac.uk for more information X-MailScanner-MH2: No Virues detected X-MailScanner-SpamCheck: not spam, SpamAssassin (score=-2.32, required 5, autolearn=not spam, ALL_TRUSTED -2.82, MY_NO_QU 0.50) X-MailScanner-From: awitney@sgul.ac.uk X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.013 required=5 tests=[AWL=0.013] X-Spam-Level: X-Archive-Number: 200509/72 X-Sequence-Number: 18809 On 7/9/05 6:12 pm, "Tom Lane" wrote: > 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. A REINDEX on the toast table did not fix the problem. Here is the output from your queries above: bugasbase2=# select chunk_id, chunk_seq, length(chunk_data) from pg_toast.pg_toast_134401982 where ctid = '(165390,4)'; chunk_id | chunk_seq | length -----------+-----------+-------- 144391872 | 0 | 1998 (1 row) bugasbase2=# select chunk_id, chunk_seq, length(chunk_data) from pg_toast.pg_toast_134401982 where ctid = '(165391,1)'; chunk_id | chunk_seq | length -----------+-----------+-------- 144391872 | 1 | 1998 (1 row) bugasbase2=# select chunk_id, chunk_seq, length(chunk_data) from pg_toast.pg_toast_134401982 where ctid = '(165391,2)'; chunk_id | chunk_seq | length -----------+-----------+-------- 144391872 | 2 | 1998 (1 row) bugasbase2=# select chunk_id, chunk_seq, length(chunk_data) from pg_toast.pg_toast_134401982 where ctid = '(165391,3)'; chunk_id | chunk_seq | length -----------+-----------+-------- 144391872 | 3 | 1998 (1 row) bugasbase2=# select chunk_id, chunk_seq, length(chunk_data) from pg_toast.pg_toast_134401982 where ctid = '(165391,4)'; chunk_id | chunk_seq | length -----------+-----------+-------- 144391872 | 4 | 1998 (1 row) bugasbase2=# select chunk_id, chunk_seq, length(chunk_data) from pg_toast.pg_toast_134401982 where ctid = '(165392,1)'; chunk_id | chunk_seq | length ----------+-----------+-------- 7625296 | 3292 | 24 (1 row) bugasbase2=# select chunk_id, chunk_seq, length(chunk_data) from pg_toast.pg_toast_134401982 where ctid = '(165392,2)'; chunk_id | chunk_seq | length ----------+-----------+-------- 7625297 | 3292 | 24 (1 row) bugasbase2=# select chunk_id, chunk_seq, length(chunk_data) from pg_toast.pg_toast_134401982 where ctid = '(165392,3)'; chunk_id | chunk_seq | length ----------+-----------+-------- 7625298 | 3292 | 24 (1 row) bugasbase2=# select chunk_id, chunk_seq, length(chunk_data) from pg_toast.pg_toast_134401982 where ctid = '(165392,4)'; chunk_id | chunk_seq | length ----------+-----------+-------- 7625299 | 3292 | 24 (1 row) Unfortunately the pg_filedump you requested gives an error: pg_filedump -R 165390 165392 134401986 ******************************************************************* * PostgreSQL File/Block Formatted Dump Utility - Version 3.0 * * File: 134401986.1 * Options used: -R 165390 165392 * * Dump created on: Thu Sep 8 10:33:47 2005 ******************************************************************* Error: Premature end of file encountered. (there is a .1 file also for this table, do I have to stick the two together or something?) Thanks again Adam -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean.