Re: BUG #2379: Duplicate pkeys in table

Tom Lane wrote:
> 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?)
Yes, every minute. Table has about 1500 rows and grows *very* fast due
to updates.

> 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?
>
The original insertion is probably not relevant (it happened months
ago); there are many places that update the table. And for the specific
row in question, it was probably inserted directly by psql. Other rows
exhibit this problem (less often), were usually inserted by a long pgsql
procedure.

Updates happen regularly from many sources, but the procedure that does
the most updates is a trigger. Do you want to see that?

> 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?
>
No. But when a new row is added, I do lock the table in exclusive mode:

Lock Table xxx In Exclusive Mode;

The specific row in these examples will never be deleted.

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

xmin | xmax | cmin | cmax | ctid
----------+----------+------+------+---------
32902771 | 33048159 | 20 | 20 | (72,27)