3

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?

Improve this question
6
  • Which minor version of PostgreSQL? Do you have replication?
    – dezso
    Oct 2, 2015 at 19:01
  • 9.3, no replication
    – Mateus Viccari
    Oct 3, 2015 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, 2015 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, 2015 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, 2015 at 11:16

1 Answer 1

Reset to default
3

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:

Caution
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
2
  • good on you for posting a good question, and coming back over a year later with a good answer.
    – Evan Carroll
    Jan 10, 2017 at 20:15
  • necromancy at its finest!
    – Mateus Viccari
    Jan 11, 2017 at 10:43

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.