On 11/16/05, Rod Taylor <[EMAIL PROTECTED]> wrote: > On Mon, 2005-11-14 at 14:51 -0500, Tom Lane wrote: > > Merlin Moncure <[EMAIL PROTECTED]> 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 ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly