Re: delete and select with IN clause issues

On Fri, 3 Nov 2006, Tom Lane wrote:

> Jeff Frost <jeff(at)frostconsultingllc(dot)com> writes:
>> On Thu, 2 Nov 2006, Tom Lane wrote:
>>> This seems pretty darn weird. I am wondering about corrupt indexes ---
>>> can you find the indicated key in either table if you set
>>> enable_indexscan and enable_bitmapscan to 0?
>
> That's not actually the test case I asked for ...

hahah, I wondered why you wanted me to disable seqscan...turns out you didn't!
I just misread it. :-)

>
>> Nope, I guess I didn't \d visit..thought I did...it's below. I'm going to
>> guess that there is index corruption and a reindex page_view will fix it. Do
>> you want me to gather any information in case this is a reproducible bug
>> before I issue the reindex?
>
> I'm thinking the same --- please save a physical copy of
> page_view_visit_idx before you reindex it, and if that fixes the
> problem, please send me copies of both the before and after states
> of the index.

Well, interestingly, it seems to work now for some reason even though the
cleanup script has failed three nights in a row and I haven't reindexed yet.
Seems quite strange. I guess now I'll have to wait and see if it fails again
tonight.

BTW, just to make sure I get the right file to ship over if we have this
again, it would be: /var/lib/pgsql/data/base/9366228/16204210 yes?

select * from pg_stat_user_indexes where indexrelname = 'page_view_visit_idx';
relid | indexrelid | schemaname | relname | indexrelname |
idx_scan | idx_tup_read | idx_tup_fetch
---------+------------+------------+-----------+---------------------+----------+--------------+---------------
9366257 | 16204210 | public | page_view | page_view_visit_idx |
5652735 | 1540722403 | 1524420263
(1 row)

--
Jeff Frost, Owner <jeff(at)frostconsultingllc(dot)com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954