Re: Addled index - Mailing list pgsql-general

On 16 March 2013 01:21, Steve Crawford <scrawford@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)