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 3FACC8BA058 for ; Thu, 10 Feb 2005 01:26:05 +0000 (GMT) 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 59150-01 for ; Thu, 10 Feb 2005 01:25:45 +0000 (GMT) Received: from hosting.commandprompt.com (128.commandprompt.com [207.173.200.128]) by svr1.postgresql.org (Postfix) with ESMTP id C57618BA070 for ; Thu, 10 Feb 2005 01:25:48 +0000 (GMT) Received: from [192.168.1.20] (clbb-248.saw.net [64.146.135.248]) (authenticated bits=0) by hosting.commandprompt.com (8.12.8/8.12.8) with ESMTP id j1A1Pi8p028961; Wed, 9 Feb 2005 17:25:47 -0800 Message-ID: <420AB834.5040606@commandprompt.com> Date: Wed, 09 Feb 2005 17:26:12 -0800 From: "Joshua D. Drake" User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Steve Lane Cc: pgsql-admin@postgresql.org, Jesse LaVere Subject: Re: PG 7.4: duplicate rows in violation of unique constraint References: In-Reply-To: Content-Type: multipart/mixed; boundary="------------010704070306000508020401" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.033 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200502/75 X-Sequence-Number: 16243 This is a multi-part message in MIME format. --------------010704070306000508020401 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Steve Lane wrote: >All: > >Puzzling PG 7.4 behaviors. Began yesterday, running under RedHat ES 2.1.' > > Which version of 7.4? >First symptom were many query failures, along with the message that a server >process had crashed, possibly corrupting shared memory. These messages kept >coming. I shut down the postmaster, and did ipcclean. > >Started postmaster again, but then found index problems with certain tables. >Records that we knew were there would not be found via index scan queries. >We then tried to reindex, and that failed as follows: > >DETAIL: Table contains duplicated values. > >This on a single column with a unique constraint, populated via a sequence! > >We searched for and found the duplicates and pulled them into a separate >table, deleted from the original table, then reindexed. > >Looking at the duplicates, they look for all the world like multiple copies >of the same row. Clearly the same record, but in each case the one with the >later timestamp had "more" data. So we picked the latest records by >timestamp and brought them back in. > >We've found similar instances on other indexes, but let me start there. > >Any ideas? Can MVCC "leak" stale rows back into the "live" space? > >-- sgl > > >-------- >Steve Lane >Vice President >Soliant Consulting, Inc. >(312) 850-3830 (V) >(312) 850-3930 (F) >slane@soliantconsulting.com > > >---------------------------(end of broadcast)--------------------------- >TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > > -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com PostgreSQL Replicator -- production quality replication for PostgreSQL --------------010704070306000508020401 Content-Type: text/x-vcard; charset=utf-8; name="jd.vcf" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="jd.vcf" begin:vcard fn:Joshua Drake n:Drake;Joshua org:Command Prompt, Inc. adr:;;PO Box 215 ;Cascade Locks;OR;97014;US email;internet:jd@commandprompt.com title:Consultant tel;work:503-667-4564 tel;fax:503-210-0334 x-mozilla-html:FALSE url:http://www.commandprompt.com version:2.1 end:vcard --------------010704070306000508020401--