Corrupted index on 9.0.3 streaming hot standby

From: Jakub Ouhrabka <kuba(at)comgate(dot)cz>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Corrupted index on 9.0.3 streaming hot standby
Date: 2011-02-25 11:20:33
Message-ID: 4D679081.4020801@comgate.cz
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Hi,

we've found that we have corrupted index on 9.0.3 streaming hot standby.
Master works ok. There is one more non-streaming standby which is ok as
well. Platform is 64bit Linux.

Database cluster and this database were created on 9.0.2 and than
upgraded to 9.0.3. We are not aware of any crash on either master or
streaming standby but we didn't investigate it deeply yet.

For details about corrupted index see below. The table and index in
question are mostly read-only (several queries per second) writes happen
only few times a day.

We've backed up whole cluster and recreated it.

Shall we investigate it further? How? Is it possible that we make some
mistake when doing initial backup which caused corruption? Is there a
way to check other indexes?

Thanks,

Kuba

set enable_bitmapscan to on ;

explain analyze select * from tXX where colXX = '18';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on tXX (cost=4.49..96.56 rows=30 width=102) (actual
time=0.018..0.018 rows=0 loops=1)
Recheck Cond: (colXX = '18'::text)
-> Bitmap Index Scan on tXX_colXX_idx (cost=0.00..4.48 rows=30
width=0) (actual time=0.015..0.015 rows=0 loops=1)
Index Cond: (colXX = '18'::text)
Total runtime: 0.053 ms

set enable_bitmapscan to off ;

explain analyze select * from tXX where colXX = '18';
QUERY PLAN
-------------------------------------------------------------------------------------------------------
Seq Scan on tXX (cost=0.00..736.65 rows=30 width=102) (actual
time=1.579..8.727 rows=30 loops=1)
Filter: (colXX = '18'::text)
Total runtime: 8.766 ms

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Sergey Aleynikov 2011-02-25 12:11:08 BUG #5900: Coredump on executing query
Previous Message Greg Stark 2011-02-25 02:03:40 Re: Function trunc() behaves in unexpected manner with different data types