Of course, when i say no reason i mean "apparently" no reason because nothing happened on the server (forced shutdowns, data corruption, deadlocks, etc...).

I ask this because, there was an application which executed a simple select on PostgreSQL:

select * from product where barcode = '9990000088355'

Which returned nothing, but there was a record in the table with barcode = 9990000088355. There was an index specific for this field, and after reindexing it, the problem was solved and the query fetched the record.

So, is there a reason for this issue to happen, and can i do something to prevent this or at least see if there is a problem with other indices of another tables?

  • Which minor version of PostgreSQL? Do you have replication? – dezso Oct 2 '15 at 19:01
  • 9.3, no replication – Mateus Viccari Oct 3 '15 at 1:58
  • If that happens, it's a bug. Even if it's after a crash or forced shutdown or deadlock, it's a bug. What is the exact version you are running? SELECT version() please. Also, please show the output of the query SHOW fsync;. Have you ever had fsync or full_page_writes set to off? – Craig Ringer Oct 3 '15 at 5:03
  • Also, is there any chance you took a copy of the database directory before you reindexed? If so, it might be useful to have the damaged index and the corresponding table. – Craig Ringer Oct 3 '15 at 10:21
  • The version is PostgreSQL 9.3.9, compiled by Visual C++ build 1800, 64-bit. Both fsync and full_page_writes were always on. Actually, before doing the reindex i really did a pg_dump to get the database to my computer, and didn't test the select query to see if it returned the values... maybe pg_dump fixed the index? – Mateus Viccari Oct 3 '15 at 11:16

FINALLY, after so much time i found the cause of this problem. I actually saw the machine where postgres was running suffering a hard reset, after that, the exact error happened.
Turns out the index i was using was a HASH index instead of the default B-Tree. So, looking up on postgres documentation, the first thing i saw was a giant yellow warning explicitly stating about it's unsafety when a crash occurs. Taken from the docs:

Hash index operations are not presently WAL-logged, so hash indexes might need to be rebuilt with REINDEX after a database crash if there were unwritten changes. Also, changes to hash indexes are not replicated over streaming or file-based replication after the initial base backup, so they give wrong answers to queries that subsequently use them. For these reasons, hash index use is presently discouraged.

What i did was simply drop the hash index and create a new one with B-tree algorithm. So far it seems to be the working solution.

|improve this answer|||||
  • good on you for posting a good question, and coming back over a year later with a good answer. – Evan Carroll Jan 10 '17 at 20:15
  • necromancy at its finest! – Mateus Viccari Jan 11 '17 at 10:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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