Re: Duplicate values found when reindexing unique index

Trolling through my server log I found this error:
2007-12-30 20:02:08 CST (10.11.199.136) PANIC: right sibling's left-link
doesn't match
2007-12-30 20:02:08 CST (10.11.199.136) STATEMENT: update bdu.entry set
title=$1, author=$2, description_type=$3, description_length=$4,
description=$5, published_at=$6, republished_at=$7, link=$8,
link_page_id=$9, link_count=$10, enclosure=$11, enclosure_page_id=$12,
enclosure_count=$13 where id=$14
2007-12-30 20:02:08 CST () LOG: server process (PID 30004) was terminated
by signal 6
2007-12-30 20:02:08 CST () LOG: terminating any other active server
processes

This seems related to the entry table -- so I wonder if it is related to
this problem?

On Dec 30, 2007 8:23 PM, Mason Hale <masonhale(at)gmail(dot)com> wrote:

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