Re: PG 7.4: duplicate rows in violation of unique

Sorry -- 7.4.5

-- sgl

> From: "Joshua D. Drake" <jd(at)commandprompt(dot)com>
> Date: Wed, 09 Feb 2005 17:26:12 -0800
> To: Steve Lane <slane(at)soliantconsulting(dot)com>
> Cc: <pgsql-admin(at)postgresql(dot)org>, Jesse LaVere <jlavere(at)soliantconsulting(dot)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(at)soliantconsulting(dot)com
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 2: you can get off all lists at once with the unregister command
>> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)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(at)commandprompt(dot)com - http://www.commandprompt.com
> PostgreSQL Replicator -- production quality replication for PostgreSQL
>