Re: Duplicate primary keys/rows

CSN <cool_screen_name90001(at)yahoo(dot)com> writes:
> oid | ctid | xmin | cmin | xmax | cmax | id
> --------+-----------+---------+------+---------+------+-----
> 125466 | (2672,11) | 1445346 | 0 | 1481020 | 0 | 985
> 125466 | (2745,50) | 1481020 | 0 | 1682425 | 2 | 985

Hmm. The fact that the dup rows have the same OID indicates pretty
strongly that they are actually two versions of the same row, and
not two independently inserted rows. Furthermore we can see that xact
1481020 deleted the first version and inserted the second (note I took
the liberty of rearranging your output to make the rows appear in
chronological order).

So the index hasn't screwed up, exactly; the problem is that both rows
appear as good at the same time. But why?

It's really highly annoying that we can't see the contents of the
infomasks for the rows. Would you be willing to grab a copy of
pg_filedump and dump out these two data pages so we can see the
complete tuple headers?

(If you don't have a compiler then you'd need to find a precompiled
copy of pg_filedump for Windows. I don't know if anyone's made one
available.)

Given that you say the machine has been crashing, my bet is that a crash
caused the loss of pg_clog status for xid 1481020 at a time when
2745,50's xmin had been marked committed good, but 2672,11's xmax had
not been similarly marked. We have sufficient defenses against this
sort of thing *if the disk drive does not lie about write complete*.
(Unfortunately the vast majority of el-cheapo PCs are configured to lie
with abandon, which means that we can't guarantee data consistency
across power failures on such hardware.) It'd be nice to get direct
confirmation of that theory though.

regards, tom lane