From: Joshua D. Drake <jd@commandprompt.com>
To: Steve Lane <slane@soliantconsulting.com>
Cc: pgsql-admin@postgresql.org, Jesse LaVere <jlavere@soliantconsulting.com>
Subject: Re: PG 7.4: duplicate rows in violation of unique constraint
Date: Wed, 09 Feb 2005 17:26:12 -0800
Message-ID: <420AB834.5040606@commandprompt.com> (raw)
In-Reply-To: <BE3001E4.12B48%slane@soliantconsulting.com>
References: <BE3001E4.12B48%slane@soliantconsulting.com>
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
Attachments:jd.vcf (text/x-vcard, 283B) download
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: jd@commandprompt.com, slane@soliantconsulting.com, jlavere@soliantconsulting.com
Subject: Re: PG 7.4: duplicate rows in violation of unique constraint
In-Reply-To: <420AB834.5040606@commandprompt.com>
References: <BE3001E4.12B48%slane@soliantconsulting.com> <420AB834.5040606@commandprompt.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox