On 11/16/05, Rod Taylor <pg@rbt.ca> wrote:
> On Mon, 2005-11-14 at 14:51 -0500, Tom Lane wrote:
> > 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 :-(
>
> I realize this doesn't help much but I have found some recently which
> are from updates. The duplicates were on a table which rarely has an
> insert or delete (maybe one of each per day, but tens of thousands of
> updates).
>
> Vacuum every 30 minutes.
I can't prove it (yet) but looking at the phantom rows strongly
indicates the same: xmin | xmax | id | lastmod |
prl_combined_key | prl_seq_no
-----------+------+----------+-------------------------+------------------+-----------584412869 | 0 | 15077217 |
2005-09-1509:31:48.163 | 00136860 | 19584527952 | 0 | 15082475 | 2005-09-15 11:17:17.062 | 00136860
| 20584412245 | 0 | 15077227 | 2005-09-15 09:31:35.381 | 00136860 | 20584527955 | 0 |
15082479| 2005-09-15 11:17:17.109 | 00136860 | 21
The way my application works, rows are only ever inserted at the end
of the sequence number range. If a row is deleted, the rows are
resequenced in place by updating the enitre record minus the p-key.
Seq# 20 and 21 have xmin and ID very close suggesting phantom was
generated during resequencing operation. This is all updates of
course, except for the highest seq# which is deleted. Can confirm
that as of yet but am putting auditing controls in which will catch it
next time.
Merlin
