Re: Addled index

On 16 March 2013 01:21, Steve Crawford <scrawford(at)pinpointresearch(dot)com>wrote:

> On 03/15/2013 11:29 AM, Oleg Alexeev wrote:
>
>> We've faced with strange index problem.
>>
>> At some moment index became bad and queries does not return any data.
>>
>> For example, there are two tables - A (id, name) and B (id, name, a_id).
>> B.a_id is foreign key to A. Both name columns in tables contains identical
>> values for A.id = B.a_id. A.name column has unique constraint and
>> additional index by it.
>>
>> So, in some moment results for queries like [select id from A where name
>> = 'petya'] became empty (row with 'petya' name exist in A).
>>
>> But query [select a_id from B where name = 'petya'] returns A.id and
>> [select * from A where id = <found id>] returns row.
>>
>> This problem can be solved by index recreation only.
>>
>> How can we avoid such situation?
>>
>> What version??
>
>
The first one fail was on 9.1.? (table with at least 10 000 000 rows with
20% every day modifications)

Two day ago was another one fail on 9.2.3. (table with 120 000 rows with
less than 0.5% every day modifications)

--
Oleg V Alexeev
E:oalexeev(at)gmail(dot)com