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 846E48BA0DC for ; Thu, 10 Feb 2005 00:07:45 +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 54841-04 for ; Thu, 10 Feb 2005 00:07:34 +0000 (GMT) Received: from mammoth.utopiasystems.net (meridian1.utopiasystems.net [66.150.45.40]) by svr1.postgresql.org (Postfix) with ESMTP id 41F7D8B9E01 for ; Thu, 10 Feb 2005 00:07:36 +0000 (GMT) Received: from 67.173.111.107 ([67.173.111.107]) by mammoth.utopiasystems.net ([172.16.1.40]) via Exchange Front-End Server mail.utopiasystems.net ([172.16.1.42]) with Microsoft Exchange Server HTTP-DAV ; Thu, 10 Feb 2005 00:04:54 +0000 User-Agent: Microsoft-Entourage/11.1.0.040913 Date: Wed, 09 Feb 2005 18:07:32 -0600 Subject: PG 7.4: duplicate rows in violation of unique constraint From: Steve Lane To: Cc: Jesse LaVere Message-ID: In-Reply-To: <420A41F1.5070007@lewczuk.com> Mime-version: 1.0 Content-type: text/plain; charset="US-ASCII" Content-transfer-encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.581 tagged_above=0 required=5 tests=FORGED_RCVD_HELO, RCVD_NUMERIC_HELO X-Spam-Level: * X-Archive-Number: 200502/74 X-Sequence-Number: 16242 All: Puzzling PG 7.4 behaviors. Began yesterday, running under RedHat ES 2.1. 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