Search code examples
sqlpostgresqlindexingb-tree

Wrong working b tree index in postgres

Recently, I have problem with b tree index on one column in table. I create index:

CREATE INDEX t_client_contact_email on t_client_contakt (email);

Everything is OK, but after a few days, searching using this index dosen't work correctly. Rebuild this index (DROP - CREATE) everytning is OK.

What can I do with this? I'm using Postgres 8.4.

Solution

  • You might be getting bitten by an old bug, that was fixed very recently.

    See this:

    http://www.postgresql.org/docs/8.4/static/release-8-4-11.html

    Specifically:

    Fix btree index corruption from insertions concurrent with vacuuming (Tom Lane)

    An index page split caused by an insertion could sometimes cause a concurrently-running VACUUM to miss removing index entries that it should remove. After the corresponding table rows are removed, the dangling index entries would cause errors (such as "could not read block N in file ...") or worse, silently wrong query results after unrelated rows are re-inserted at the now-free table locations. This bug has been present since release 8.2, but occurs so infrequently that it was not diagnosed until now. If you have reason to suspect that it has happened in your database, reindexing the affected index will fix things.