Re: BUG #2379: Duplicate pkeys in table

Philip Warner <pjw(at)rhyme(dot)com(dot)au> writes:
> mail=# set enable_indexscan=off;
> mail=# SELECT xmin, xmax, cmin, cmax,ctid FROM xxx where id = 24613;
> xmin | xmax | cmin | cmax | ctid
> ----------+----------+------+----------+---------
> 32902771 | 0 | 20 | 32902872 | (0,7)
> 32902771 | 0 | 20 | 32902872 | (2,27)
> 32902771 | 0 | 20 | 32902872 | (58,27)
> 32902771 | 0 | 20 | 32902872 | (60,28)
> 32902771 | 0 | 20 | 32902872 | (69,3)
> 32902771 | 33048159 | 20 | 20 | (72,27)
> (6 rows)

The "cmax" values in the first 5 rows are evidently really xvac values,
ie, these have all been moved by VACUUM FULL. (I assume you run VACUUM
FULL regularly on this table?) The thing that is striking though is
that the xmin/cmin values are all the same, indicating that all six
tuples were inserted by the same command. That seems pretty odd. Can
you show us the procedure by which rows are inserted in this table?

Also, the last tuple has either been deleted or locked-for-update by
transaction 33048159; if it were an attempted deletion we'd have to
conclude that 33048159 failed to commit. Do you use SELECT FOR UPDATE
on this table?

BTW, which of these rows is selected by an indexscan-enabled query,
ie, set enable_indexscan=on then repeat same query?

regards, tom lane