Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 2C299632543 for ; Sat, 14 Feb 2009 04:26:22 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 50039-06 for ; Sat, 14 Feb 2009 04:26:19 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from exprod7og106.obsmtp.com (exprod7og106.obsmtp.com [64.18.2.165]) by mail.postgresql.org (Postfix) with SMTP id B7D14632552 for ; Sat, 14 Feb 2009 04:26:15 -0400 (AST) Received: from source ([66.249.92.174]) by exprod7ob106.postini.com ([64.18.6.12]) with SMTP ID DSNKSZaAJiJTtCYUG5S5OHsSTBSBNragyofy@postini.com; Sat, 14 Feb 2009 00:26:18 PST Received: by ug-out-1314.google.com with SMTP id z34so98234ugc.28 for ; Sat, 14 Feb 2009 00:26:13 -0800 (PST) Received: by 10.210.90.20 with SMTP id n20mr2371726ebb.72.1234599973385; Sat, 14 Feb 2009 00:26:13 -0800 (PST) Received: from ?80.223.212.7? (dsl-hkibrasgw2-fed4df00-7.dhcp.inet.fi [80.223.212.7]) by mx.google.com with ESMTPS id c9sm1614441nfi.34.2009.02.14.00.26.12 (version=TLSv1/SSLv3 cipher=RC4-MD5); Sat, 14 Feb 2009 00:26:12 -0800 (PST) Message-ID: <49968023.8030404@enterprisedb.com> Date: Sat, 14 Feb 2009 10:26:11 +0200 From: Heikki Linnakangas Organization: EnterpriseDB User-Agent: Mozilla-Thunderbird 2.0.0.19 (X11/20090103) MIME-Version: 1.0 To: Tom Lane CC: John Lister , pgsql-hackers@postgresql.org Subject: Re: Database corruption help References: <507152DDF8AB4DF093BD01A8BEC7C659@squarepi.com> <17205.1234200693@sss.pgh.pa.us> <4990840C.4090900@kickstone.com> <20151.1234208044@sss.pgh.pa.us> <49908D72.7040904@kickstone.com> <25753.1234224090@sss.pgh.pa.us> <4991338B.704@kickstone.com> <4994906B.6090408@kickstone.com> <17204.1234474784@sss.pgh.pa.us> <4994987F.1030204@kickstone.com> <17764.1234476783@sss.pgh.pa.us> <4994A45B.4030707@kickstone.com> <49952D33.3000707@kickstone.com> <3161.1234541940@sss.pgh.pa.us> In-Reply-To: <3161.1234541940@sss.pgh.pa.us> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200902/799 X-Sequence-Number: 134089 Tom Lane wrote: > Aside from the "how did this happen" puzzle, the real point of any > investigation of course ought to be whether we can make heap_page_prune > more robust. At the very least it's undesirable to be leaving the page > in a state where VACUUM FULL will decide it can't shrink. I'm as puzzled as you are on how it happened. The fundamental problem here is that we have HOT-updated tuples that are missing the root tuple. heap_prune_chain doesn't know how to remove the dead tuples from such chains, and neither does vacuum. What's worse is that there's no index pointer to the live tuple in the chain, and even VACUUM FULL doesn't fix that. Could we modify heap_page_prune so that it detects such orphaned HOT tuples, and clears the HOT_UPDATED flag for them? That would at least let you recover. I'm pretty wary of trying to "fix" things after corruption in general since you can't be sure what's correct and what's not, but clearing the HOT_UPDATED flag seems safe and more likely to help than hurt. Clearing the flag would let the tuple become indexed again in a VACUUM FULL or REINDEX, so it would fix the inconsistency that otherwise a sequential scan can see the tuple but an index scan can not. It would also allow the next heap_page_prune operation to remove the remaining dead tuples in the chain. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com