Thread: BUG #5727: Indexes broken in streaming replication

BUG #5727: Indexes broken in streaming replication

From
"Jan Kantert"
Date:
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

Re: BUG #5727: Indexes broken in streaming replication

From
Tom Lane
Date:
"Jan Kantert" <jan-postgres@kantert.net> writes:
> 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));

Hash indexes are not replicated.  There's seldom any very good reason to
use them in practice, because they also have no WAL protection and don't
perform very well anyway.  Why did you pick a hash index for a
production application?

            regards, tom lane

Re: BUG #5727: Indexes broken in streaming replication

From
Heikki Linnakangas
Date:
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

Re: BUG #5727: Indexes broken in streaming replication

From
Tom Lane
Date:
Heikki Linnakangas <heikki.linnakangas@enterprisedb.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
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:

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.

            regards, tom lane

Re: BUG #5727: Indexes broken in streaming replication

From
Heikki Linnakangas
Date:
On 26.10.2010 20:47, Tom Lane wrote:
> Heikki Linnakangas<heikki.linnakangas@enterprisedb.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
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:
>
> 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
notbe 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

Re: BUG #5727: Indexes broken in streaming replication

From
Tom Lane
Date:
Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> writes:
>> * Operations on hash indexes are not presently WAL-logged, so replay will not update these indexes. Hash indexes
willnot 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.

Yeah, and also the index would still be broken after the slave exits hot
standby and becomes live; so that hack didn't cure the problem anyway.

> 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:

Agreed.

            regards, tom lane

Re: BUG #5727: Indexes broken in streaming replication

From
Simon Riggs
Date:
On Tue, 2010-10-26 at 14:08 -0400, Tom Lane wrote:
> Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> writes:
> >> * Operations on hash indexes are not presently WAL-logged, so replay will not update these indexes. Hash indexes
willnot 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.
>
> Yeah, and also the index would still be broken after the slave exits hot
> standby and becomes live; so that hack didn't cure the problem anyway.

OK, that's a good argument.

--
 Simon Riggs           http://www.2ndQuadrant.com/books/
 PostgreSQL Development, 24x7 Support, Training and Services