Collected from the PG bugs email list.
|Reported by||Wayne Walker|
Body of first available message related to this bug follows.
The following bug has been logged on the website: Bug reference: 16166 Logged by: Wayne Walker Email address: (redacted) PostgreSQL version: 9.6.0 Operating system: Documentation Description: https://www.postgresql.org/docs/9.6/sql-createindex.html Has this Caution: " 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. Hash indexes are also not properly restored during point-in-time recovery. For these reasons, hash index use is presently discouraged." This leaves out the fact that if one creates a HASH index in a cluster that is replicated, the replica will Not be Usable. The fact that the index exists will be replicated, but no data will be replicated. Therefore, queries will attempt to use the index and will fail with a message like this: 'ERROR: could not read block 0 in file "pg_tblspc/3291733/PG_9.6_201608131/17993/32595182": read only 0 of 8192 bytes' This happened when we were in the middle of a failover to our primary replica because we had just had a hardware failure on our master server. This is not the time to find out. We had read the caution and discussed it. For our use case it was acceptable (we thought), because the hash index wouldn't exist on the replica, and we would just get slow queries while we were creating the new hash index on the replica become primary. Instead we were trying to find out why queries ( INSERTs :-( ) were failing. If the caution had mentioned the "partially, but unusably, replication of the index,we would have recovered much faster and without the extra dowtime.
|2019-12-15 04:48:15+00||PG Bug reporting form||BUG #16166: Caution needs to be expanded|