Re: Duplicate values found when reindexing unique index

On Dec 30, 2007 12:09 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> "Mason Hale" <masonhale(at)gmail(dot)com> writes:
> > Given my experience, the reliability of unique indexes is becoming
> somewhat
> > suspect. Please help. ;-)
>
> Well, as in the previous report, there is not enough information here to
> offer much chance of understanding what's going wrong.
>

Please just tell me what information you need and I will provide what I can.

> Have you tried reindexing that other index with the same columns in the
> other order? My guess is that there really are duplicate entries in the
> table; if so the other one should fail too. If so, please try to
> identify the duplicated values, along the lines of
>
> select guid, feed_id from entry group by 1,2 having count(*) > 1
>
> and show us the system columns (ctid,xmin,xmax,cmin,cmax) from the
> tuples having duplicate value(s). Note that you should probably disable
> indexscan and bitmapscan while doing this probing, so as not to have the
> queries use the suspect indexes.

I found a single pair of rows that were duplicated. Interestingly it was not
just the guid and feed_id that were duplicated but all columns were
indentical, including the primary key, except an update_at column which is
automatically populated via a trigger (BEFORE UPDATE on entry FOR EACH ROW).

The duplicate data included a created_at column which defaults to now() --
that the two duplicate rows have exactly the same values strongly hints to
me that the duplicates were created during the same transaction.

Here's the system column data you requested.

id | ctid | xmin | xmax | cmin | cmax
-----------+--------------+------+------+------+------
151341072 | (1508573,11) | 2 | 0 | 19 | 0
151341072 | (1818219,11) | 2 | 0 | 19 | 0
(2 rows)

> This is 8.2.5 right? Was the DB loaded fresh into 8.2.5, or was it
> inherited from previous 8.2.x release(s)?
>

It is 8.2.5. It was loaded from a pg_dump from an 8.2.3 database into a
fresh 8.2.5 database on new hardware.

> BTW, what are the datatypes of the index columns?
>

id integer not null (serial)
guid character varying not null ( no size limit defined )
feed_id integer not null

>
> regards, tom lane
>

thanks for the help Tom.

I do want to clear out one of the offending duplicates and reindex. But I'll
wait to do that until I get the okay from you.

Mason