Обсуждение: dupes for PK and other UNIQUE indexes

Hi all;

I have a client with a table which has an exeedingly large amount of bloat, so
we plan to rebuild the table.  However in setting up a test box I've found
that both the PK and another UNIQUE index column have duplicate values.

In theory the INDEX/PK should refuse to build if dupes exist on the data, and
subsequent data should refuse to load if the PK/UNIQUE index already exists.

Anyone have any Idea how this could happen?

Thanks in advance

Kevin Kempter <kevink@consistentstate.com> writes:
> Anyone have any Idea how this could happen?

Corrupt indexes.  What PG version are we talking about?

            regards, tom lane

Kevin Kempter <kevink@consistentstate.com> writes:
> On Sunday 01 November 2009 15:50:20 you wrote:
>> Kevin Kempter <kevink@consistentstate.com> writes:
>>> Anyone have any Idea how this could happen?
>>
>> Corrupt indexes.  What PG version are we talking about?

> version 8.3.8

Well, there aren't any known causes of btree index corruption in 8.3.x,
but that doesn't mean you haven't found something new.  Does this
installation have any nonstandard history?  (I'm wondering about things
like it having been a WAL standby at one time, or part of a Slony
setup, or something like that.)  Can you provide any other data that
might help somebody reproduce the problem?

BTW, please keep your responses cc'd to the list.

            regards, tom lane

On Sunday 01 November 2009 16:25:58 Tom Lane wrote:
> Kevin Kempter <kevink@consistentstate.com> writes:
> > On Sunday 01 November 2009 15:50:20 you wrote:
> >> Kevin Kempter <kevink@consistentstate.com> writes:
> >>> Anyone have any Idea how this could happen?
> >>
> >> Corrupt indexes.  What PG version are we talking about?
> >
> > version 8.3.8
>
> Well, there aren't any known causes of btree index corruption in 8.3.x,
> but that doesn't mean you haven't found something new.  Does this
> installation have any nonstandard history?  (I'm wondering about things
> like it having been a WAL standby at one time, or part of a Slony
> setup, or something like that.)  Can you provide any other data that
> might help somebody reproduce the problem?
>
> BTW, please keep your responses cc'd to the list.
>
>             regards, tom lane
>

I'll do some digging and see what I can find. They are using Ruby on Rails,
that's all I have so far.