Re: Vacuum/visibility is busted

On Thu, Feb 7, 2013 at 10:48 PM, Jeff Janes <jeff.janes@gmail.com> wrote:
> On Thu, Feb 7, 2013 at 1:44 AM, Pavan Deolasee <pavan.deolasee@gmail.com> wrote:

>>
>> jjanes=# select *, xmin, xmax, ctid from foo where index IN (select
>> index from foo group by index having count(*) > 1 ORDER by index)
>> ORDER by index LIMIT 3;
>>  index | count |    xmin    | xmax |   ctid
>> -------+-------+------------+------+-----------
>>    219 |   353 | 2100345903 |    0 | (150,98)
>>    219 |   354 | 2100346051 |    0 | (150,101)
>>    219 |   464 | 2101601086 |    0 | (150,126)
>> (3 rows)
>
> The one where count=464 should be the correct one to be visible, and
> the other two are old tuples that were updated away.  (The test driver
> increases the count column monotonically for each any given value of
> index column.
>

Right. I don't have the database handy at this moment, but earlier in
the day I ran some queries against it and found that most of the
duplicates which are not accessible via indexes have xmin very close
to 2100345903. In fact, many of them are from a consecutive range.

Thanks,
Pavan
-- 
Pavan Deolasee
http://www.linkedin.com/in/pavandeolasee