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@gmail.com> wrote:

>
>
> On Dec 30, 2007 12:09 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> > "Mason Hale" <masonhale@gmail.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
>