Re: BUG #2379: Duplicate pkeys in table

Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> Philip Warner wrote:
>> Item 7 -- Length: 168 Offset: 3920 (0x0f50) Flags: USED
>> XMIN: 32902771 CMIN: 20 XMAX: 0 CMAX|XVAC: 32902872
>> Block Id: 0 linp Index: 7 Attributes: 34 Size: 36
>> infomask: 0x2913
>> (HASNULL|HASVARWIDTH|HASOID|XMIN_COMMITTED|XMAX_INVALID|UPDATED)
>> t_bits: [0]: 0x9f [1]: 0x80 [2]: 0x7e [3]: 0x84
>> [4]: 0x00

> Hmm, shouldn't we see the MOVED_OFF bit set also if the cmax/xvac field
> is actually xvac?

These are probably MOVED_IN not MOVED_OFF tuples; VACUUM FULL clears
their MOVED_IN flags at the end (see update_hint_bits()), which explains
the lack of any flag being set.

>> Item 27 -- Length: 168 Offset: 8024 (0x1f58) Flags: USED
>> XMIN: 32902771 CMIN: 20 XMAX: 33048159 CMAX|XVAC: 20
>> Block Id: 318 linp Index: 6 Attributes: 34 Size: 36
>> infomask: 0x2913
>> (HASNULL|HASVARWIDTH|HASOID|XMIN_COMMITTED|XMAX_INVALID|UPDATED)
>> t_bits: [0]: 0x9f [1]: 0x80 [2]: 0x7e [3]: 0x84
>> [4]: 0x00

> I'm confused -- the original report showed this tuple with ctid (72,27),
> but this seems to be in a different block?

The blockid/linp fields are pointing to the updated version of the row
that xid 33048159 tried to create. It's a plausible theory that the
reason 33048159 failed (as it evidently did, XMAX_INVALID) is that it
failed on insertion of a duplicate key into the pkey index.

It is interesting that the CMIN and CMAX are the same, but that probably
just says that 32902771 and 33048159 were instances of the exact same
SQL command pattern, and so their attempted updates both came 20
commands into the transaction.

Philip suggested to me off-list that the initial error may have been the
VACUUM FULL (xid 32902872) creating duplicate moved copies of a single
valid row. That seems plausible because VACUUM FULL suppresses
duplicate-index checks, and it's real hard to see any other way that a
single transaction could have inserted all of these tuples without
triggering the btree duplicate-key check (barring a completely corrupt
index anyway). I wouldn't be surprised in the least bit to find another
corner-case bug in the VACUUM FULL tuple chain moving code ... that bit
of spaghetti has been too complex since day one ...

regards, tom lane