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

On Nov 6, 2007 1:06 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> "Mason Hale" <masonhale@gmail.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