On 26.10.2010 20:04, Jan Kantert wrote:
> we have set up streaming replication. It works fine in normal cases. We
> found out that one query did not work anymore on our slaves. We have
> verified that the slaves were up to date and contained all data.
>...
> master=# CREATE INDEX index_user_lower_login ON users USING hash
> (lower(login::text));
Hash indexes are not WAL-logged, and therefore are not replicated
either. For the same reason, they are not crash-safe, and won't work
after recovery from a continuous WAL archive. Don't use them.
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
aftera 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:
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.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com