Re: BUG #3724: Duplicate values added to table despite unique index

On Nov 6, 2007 1:06 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> "Mason Hale" <masonhale(at)gmail(dot)com> writes:
> >> For that matter, do you still see dups if you prevent use of the index
> >> in the 8.2.5 query? Maybe it's that index that is corrupt.
>
> > Unfortunately, I'm not able to test that at this point.
> > To get our production db (the 8.2.5 instance) back in operation I
> deleted
> > the extra duplicate rows, so that the update statement would complete.
>
> Mph. I'm afraid the evidence is mostly gone then, and we probably won't
> be able to figure out what happened.

Sorry about that. But I had to get things back up and running.

> However, it would be worth
> checking two things:
>
> 1. Can you confirm that the rows that got duplicated were in fact
> present (in only one copy) in the 8.2.4 DB?

Yes, they are present:

prod_1=> select * from topic_version_page where topic_version_id = 263 and
page_id in (161335682, 194359108);
topic_version_id | page_id | link_score | created_at
| updated_at | is_entry_page | linking_entry_count
------------------+-----------+------------+----------------------------+----------------------------+---------------+---------------------
263 | 161335682 | 0 | 2007-10-13 02:40:49.864219 |
2007-11-01 15:58:57.268593 | f | 5
263 | 194359108 | 0 | 2007-10-25 13:34:20.654336 |
2007-11-04 01:01:50.512446 | f | 2
(2 rows)

prod_1=>

>
> 2. Can you check that there are still 1 (rather than 0) copies of the
> rows in the 8.2.5 DB? One possible theory about this is that what you
> had was (two instances of) two index entries pointing at the same heap
> row, in which case a DELETE that you thought removed only one copy would
> have deleted both.
>

Yes, we have 1 of each row (I kept the most recently updated version of
each):

prod_2=> select * from topic_version_page where topic_version_id = 263 and
page_id in (161335682, 194359108);
topic_version_id | page_id | link_score | created_at
| updated_at | is_entry_page | linking_entry_count
------------------+-----------+------------+----------------------------+----------------------------+---------------+---------------------
263 | 194359108 | 0 | 2007-10-25 13:34:20.654336 |
2007-11-04 13:08:03.011292 | f | 2
263 | 161335682 | 0 | 2007-10-13 02:40:49.864219 |
2007-11-06 16:01:36.393953 | f | 7
(2 rows)

prod_2=>

Mason