pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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

view thread (6+ messages)  latest in thread

Message-ID: <420AB834.5040606@commandprompt.com>
Permalink:  ../420AB834.5040606@commandprompt.com/
Also on:    postgresql.org/message-id/420AB834.5040606@commandprompt.com

 · 

reply

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