pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: wwalker@solid-constructs.com
Subject: BUG #16166: Caution needs to be expanded
Date: Sun, 15 Dec 2019 04:48:15 +0000
Message-ID: <16166-c75565f474967090@postgresql.org> (raw)

The following bug has been logged on the website:

Bug reference:      16166
Logged by:          Wayne Walker
Email address:      wwalker@solid-constructs.com
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.



Message-ID: <16166-c75565f474967090@postgresql.org>
Permalink:  ../16166-c75565f474967090@postgresql.org/
Also on:    postgresql.org/message-id/16166-c75565f474967090@postgresql.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-bugs@postgresql.org
  Cc: noreply@postgresql.org, wwalker@solid-constructs.com
  Subject: Re: BUG #16166: Caution needs to be expanded
  In-Reply-To: <16166-c75565f474967090@postgresql.org>
  References: <16166-c75565f474967090@postgresql.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox