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?
SELECT version()
please. Also, please show the output of the querySHOW fsync;
. Have you ever hadfsync
orfull_page_writes
set tooff
?reindex
ed? If so, it might be useful to have the damaged index and the corresponding table.PostgreSQL 9.3.9, compiled by Visual C++ build 1800, 64-bit
. Bothfsync
andfull_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?