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 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:
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 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
