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