On 16 March 2013 14:32, Alban Hertroys <haram...@gmail.com> wrote: > On Mar 16, 2013, at 9:33, Oleg Alexeev <oalex...@gmail.com> wrote: > > On 16 March 2013 01:21, Steve Crawford <scrawf...@pinpointresearch.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? > > > Oh, it is not about short experiments. :)
Both failed queries are part of 24/7 application. And one of the query results became empty for existing key at some moment. We've recreated index and same query returned to the normal work. fsync is in off state, yes Both failed indexes are btree type. Database located on software md raid 1 based on two SSD disks array. Ext4 filesystem. Database is master node. -- Oleg V Alexeev E:oalex...@gmail.com
