Re: Duplicate values found when reindexing unique index

["Mason Hale" <masonhale(at)gmail(dot)com>]

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