Re: 8.0 -> 8.1 dump duplicate key problem?

On 11/16/05, Rod Taylor <pg(at)rbt(dot)ca> wrote:
> On Mon, 2005-11-14 at 14:51 -0500, Tom Lane wrote:
> > Merlin Moncure <mmoncure(at)gmail(dot)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-15 09:31:48.163 | 00136860
| 19
584527952 | 0 | 15082475 | 2005-09-15 11:17:17.062 | 00136860
| 20
584412245 | 0 | 15077227 | 2005-09-15 09:31:35.381 | 00136860
| 20
584527955 | 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