Re: BUG #5727: Indexes broken in streaming replication

On 26.10.2010 20:47, Tom Lane wrote:
> Heikki Linnakangas<heikki(dot)linnakangas(at)enterprisedb(dot)com> writes:
>> There's a note in the docs about this:
>
>>> Note: Hash index operations are not presently WAL-logged, so hash indexes might need to be rebuilt with REINDEX after a database crash. For this reason, hash index use is presently discouraged.
>
>> though it doesn't explicitly mention replication. Perhaps it should be
>> updated to something like:
>
> Yeah, I too just noticed that that caveat hadn't been updated.
>
>> Note: Hash index operations are not presently WAL-logged, so hash
>> indexes might need to be rebuilt with REINDEX after a database crash.
>> They are also not replicated over streaming or file-based replication.
>> For this reason, hash index use is presently discouraged.
>
> s/this reason/these reasons/. Also maybe we should change this to a
> <caution> or<warning> instead of just a<note>? Otherwise +1.

Will do.

I also noticed that the hot standby docs are not up-to-date on this:

> 25.5.5. Caveats
>
> There are several limitations of Hot Standby. These can and probably will be fixed in future releases:
>
> * Operations on hash indexes are not presently WAL-logged, so replay will not update these indexes. Hash indexes will not be used for query plans during recovery.

The initial patch indeed had a special-case in the planner to ignore
hash indexes during hot standby, but it was left out because the lack of
WAL-logging is a general problem with hash indexes, not a hot standby
issue. I'd be tempted to remove that caveat altogether for the same
reason, but it's probably good to have that note there and just remove
the sentence about query plans:

* Operations on hash indexes are not presently WAL-logged, so replay
will not update these indexes.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com