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

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

1 Answer 1

Reset to default
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

Improve this answer
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.
    – Erwin Brandstetter
    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?
    – Mateus Viccari
    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.
    – Erwin Brandstetter
    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 that you have read and understand our privacy policy and code of conduct.

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