The following bug has been logged online:
Bug reference: 5727
Logged by: Jan Kantert
Email address: jan-postgres@kantert.net
PostgreSQL version: 9.0.1
Operating system: Ubuntu 10.04 x86_64 2.6.32-22-server #33-Ubuntu SMP
x86_64 GNU/Linux
Description: Indexes broken in streaming replication
Details:
Hi,
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=# SELECT user_id FROM users WHERE LOWER(login) = LOWER('my_login');
user_id
---------
1234
(1 row)
slave=# SELECT user_id FROM users WHERE LOWER(login) = LOWER('my_login');
user_id
---------
(0 rows)
This seemed to be strange. It turned out that it worked if we change the
LOWER(login) to login:
slave=# SELECT user_id FROM users WHERE login = LOWER('my_login');
user_id
---------
1234
(1 row)
We found out that there existed an index on LOWER(login). So we dropped the
index. Our query worked on master and slave as long as there existed no
indexes:
master=# DROP INDEX index_user_lower_login;
DROP INDEX
master=# SELECT user_id FROM users WHERE LOWER(login) = LOWER('my_login');
user_id
---------
1234
(1 row)
slave=# SELECT user_id FROM users WHERE LOWER(login) = LOWER('my_login');
user_id
---------
1234
(1 row)
After we created the index again, we saw strange problems on the slave:
master=# CREATE INDEX index_user_lower_login ON users USING hash
(lower(login::text));
CREATE INDEX
master=# SELECT user_id FROM users WHERE LOWER(login) = LOWER('my_login');
user_id
---------
1234
(1 row)
slave=# SELECT user_id FROM users WHERE LOWER(login) = LOWER('my_login');
ERROR: could not read block 0 in file "base/16408/98928848": read only 0 of
8192 bytes
If we remove the index, it will work again. Looks like some kind of bug in
the replication.
Regards,
Jan
Our Postgresbuild: PostgreSQL 9.0.1 on x86_64-pc-linux-gnu, compiled by GCC
gcc-4.4.real (Ubuntu 4.4.3-4ubuntu5) 4.4.3, 64-bit