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 5A6348B9F75 for ; Thu, 10 Feb 2005 01:46:47 +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 59516-10 for ; Thu, 10 Feb 2005 01:46:28 +0000 (GMT) Received: from mammoth.utopiasystems.net (meridian1.utopiasystems.net [66.150.45.40]) by svr1.postgresql.org (Postfix) with ESMTP id 0455D8B9F31 for ; Thu, 10 Feb 2005 01:46:31 +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 01:43:50 +0000 User-Agent: Microsoft-Entourage/11.1.0.040913 Date: Wed, 09 Feb 2005 19:46:29 -0600 Subject: Re: PG 7.4: duplicate rows in violation of unique From: Steve Lane To: "Joshua D. Drake" Cc: , Jesse LaVere Message-ID: In-Reply-To: <420AB834.5040606@commandprompt.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.569 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO, RCVD_NUMERIC_HELO X-Spam-Level: * X-Archive-Number: 200502/76 X-Sequence-Number: 16244 Sorry -- 7.4.5 -- sgl > From: "Joshua D. Drake" > Date: Wed, 09 Feb 2005 17:26:12 -0800 > To: Steve Lane > Cc: , Jesse LaVere > Subject: Re: [ADMIN] PG 7.4: duplicate rows in violation of unique constraint > > 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 >