1

Sometimes, when doing an update on a specific table record, i get this error:

index "myIndexName" contains corrupted page at block 468
Please REINDEX it.

But on the next update it works, without having to do a reindex. What can cause this issue? Is there a way to prevent this error?

This index is only there to speed up performance on queries, but i'm seriously wanting to drop it to prevent information loss if i can't find a solution.

My postgres version is 9.3.9

1
  • That really shouldn't happen. Can you supply more information about the queries that work / don't work, when this started happening, etc? Nov 6, 2015 at 1:24

1 Answer 1

2

It looks like an on disk index corruption. If you can afford to have the table locked for a while you can solve it by running:

REINDEX INDEX "myIndexName";

The command will rebuild the index getting rid of the error.

If you cannot have the table locked for the time needed by the REINDEX command, you can concurrently create an additional index with the same definition. If the index is still usable, you can drop it after the CREATE INDEX CONCURRENTLY command finished and the index become valid, otherwise you can drop it before to start.

You can find more information here:

http://www.postgresql.org/docs/9.3/static/sql-reindex.html

http://www.postgresql.org/docs/9.3/static/sql-createindex.html#SQL-CREATEINDEX-CONCURRENTLY

5
  • 2
    If the index is corrupted it shouldn't be used at all until fixed. So, if the table can't be locked, I would drop the index immediately and CREATE INDEX CONCURRENTLY - risking some slow queries rather than incorrect results. Nov 5, 2015 at 17:51
  • From the description of the OP it could be a not widespread corruption, maybe only a leaf block, however I agree that is safer to drop the index before concurrently recreate it.
    – mnencia
    Nov 5, 2015 at 17:59
  • Will do that, but isn't there a way to prevent things like this from happening? Nov 5, 2015 at 18:14
  • 2
    @MateusViccari: Shouldn't happen at all. (Never happened to me, yet.) But there are many factors in play. Check your disk for hardware failures. Killing the Postgres server process or power outage without battery-backed storage system would be possible roots for the problem. Nov 5, 2015 at 18:15
  • 2
    Computers are not perfect. Even in well written and well tested software there is so many things that can go wrong. Hardware defects, operating system bugs, memory bit-flips caused by cosmic rays and obviously subtle software bugs that have gone unnoticed during the estensive testing. My advice is to use good quality hardware with ECC memory, but more important, to implement a solid Disaster Recovery plan
    – mnencia
    Nov 5, 2015 at 21:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.