Re: Addled index

On Mar 16, 2013, at 9:33, Oleg Alexeev <oalexeev(at)gmail(dot)com> wrote:

> 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)

Perhaps the name you're not finding is spelled differently than what you're typing, due to collation?

If there's actually something wrong with the database; it looks a bit like your tables and your indexes get out of sync somehow, which normally wouldn't be possible. I'm mostly guessing, but perhaps one of the below has something to do with it:
Maybe you turned fsync off?
What type of index is that? A standard btree or one of the newer types?
Are those tables and indexes perhaps on some kind of virtual storage or on a file-system that might be rolling back file-system transactions? It this database perhaps a replicated node?

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.