Merlin Moncure <mmoncure@gmail.com> writes:
> esp=# select prl_combined_key, prl_seq_no, xmin, xmax, lastmod from
> parts_order_line_file where prl_combined_key = ' 00136860' and
> prl_seq_no in (20, 23);
> prl_combined_key | prl_seq_no | xmin | xmax | lastmod
> ------------------+------------+-----------+------+-------------------------
> 00136860 | 20 | 584527952 | 0 | 2005-09-15 11:17:17.062
> 00136860 | 20 | 584412245 | 0 | 2005-09-15 09:31:35.381
> 00136860 | 23 | 584527961 | 0 | 2005-09-15 11:17:17.187
> 00136860 | 23 | 584415243 | 0 | 2005-09-15 09:32:18.898
OK, so the fact that they all have xmax=0 proves that none are UPDATEd
versions of others, which leaves us with the presumption that there was
an outright failure of duplicate-key detection during INSERT :-(
The pairing seems very odd though: judging from the proximity of xmin
and lastmod, the first and third rows were inserted at almost the same
time, and they do *not* have equal keys; the rows they should have
conflicted with were inserted some time earlier.
Can you think of anything special about the client-side logic that might
have created unusual behavior in this situation? Not that it's not
clearly a backend-side bug, I'm just looking for a handle to attack it
with. (If you can determine the exact SQL commands that were issued to
create these records, it'd be great.)
One question that seems interesting is whether there might have been a
VACUUM working on the pkey index concurrently with the later insertions.
If you can either positively confirm or positively deny that, it'd be
useful information.
regards, tom lane