pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Steve Lane <slane@soliantconsulting.com>
To: Joshua D. Drake <jd@commandprompt.com>
Cc: pgsql-admin@postgresql.org
Cc: Jesse LaVere <jlavere@soliantconsulting.com>
Subject: Re: PG 7.4: duplicate rows in violation of unique
Date: Wed, 09 Feb 2005 19:46:29 -0600
Message-ID: <BE301915.12B5B%slane@soliantconsulting.com> (raw)
In-Reply-To: <420AB834.5040606@commandprompt.com>

Sorry -- 7.4.5

-- sgl


> From: "Joshua D. Drake" <jd@commandprompt.com>
> Date: Wed, 09 Feb 2005 17:26:12 -0800
> To: Steve Lane <slane@soliantconsulting.com>
> Cc: <pgsql-admin@postgresql.org>, Jesse LaVere <jlavere@soliantconsulting.com>
> 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
> 




view thread (6+ messages)  latest in thread

Message-ID: <BE301915.12B5B%slane@soliantconsulting.com>
Permalink:  ../BE301915.12B5B%25slane@soliantconsulting.com/
Also on:    postgresql.org/message-id/BE301915.12B5B%slane@soliantconsulting.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: slane@soliantconsulting.com, jd@commandprompt.com, jlavere@soliantconsulting.com
  Subject: Re: PG 7.4: duplicate rows in violation of unique
  In-Reply-To: <BE301915.12B5B%slane@soliantconsulting.com>
  References: <420AB834.5040606@commandprompt.com> <BE301915.12B5B%slane@soliantconsulting.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