Re: duplicated values on primary key field on reindex

On Thu, 2006-07-06 at 17:30, Weerts, Jan wrote:
> Scott Marlowe wrote:
> > On Thu, 2006-07-06 at 16:36, Weerts, Jan wrote:
> >> Hi all!
> >>
> >> This was 8.1.3 and now is 8.1.4 running on Debian Sarge, locally
> >> compiled without any fancy options.
> >
> >>
> >> While the first answer seems much more valid (the primarkey is
> >> an artificially created number), the second answer seems to
> >> be the one being presented for all further invocations of the
> >> above query.
> >>
> >> I noted, that the second row does not fit the "order by" clause,
> >> so I tried a reindex of the db, but that led to a duplicate value
> >> error: # reindex index tw_blob_pkey;
> >> ERROR: could not create unique index
> >> DETAIL: Table contains duplicated values.
> >>
> >> Now that is something I don't understand at all.
> >>
> >> Since the backup for said server went postal too long ago
> >> unnoticed, I would prefer a "repair" solution. Any ideas?
> >
> > Can you get set of fields in that row to uniquely identify it by?
> >
> > If so, see if you can update that column to something else and
> > continue
>
> The only way would be to update by primarykey. But since the
> select on the primarykey field shows this "strange" ordering,
> I wonder, what effect an update would have. My guess would be,
> that the correct pk value should be 1636695, but seeing only
> 216305 on subsequent calls makes me think.
>
> I even have executed
> # select * from tw_blob where primarykey = 216305;
> and receive a single row, which I don't really trust to be
> the same one producing the error.

If there are no other fields you can use to uniquely identify that row,
then add a new row to the table and update it from a sequence...

create sequence deargodsaveme;
alter table brokentable add column emergencyint int;
update brokentable set emergencyint=nextval('deargodsaveme');

then use that new column, emergencyint to select it for an update.

Note that these kind of problem is generally a sign of faulty hardware,
so you'll need to be looking at your machine's hardware (memory, CPU,
etc..) and possible problems like faulty fsyncing etc... to make sure
it doesn't happen again.