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
>
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