Thread: Index contains unexpected zero page at block

Index contains unexpected zero page at block

From

Victor Blomqvist

Date:

17 December 2015, 06:49:15

From time to time I get this and similar errors in my Postgres log file:

< 2015-12-17 07:45:05.976 CST >ERROR: index "user_pictures_picture_dhash_idx" contains unexpected zero page at block 123780
< 2015-12-17 07:45:05.976 CST >HINT: Please REINDEX it.
< 2015-12-17 07:45:05.976 CST >CONTEXT: PL/pgSQL function select_pictures_by_dhash(bigint,integer,integer) line 3 at RETURN QUERY
< 2015-12-17 07:45:05.976 CST >STATEMENT: SELECT * FROM select_pictures_by_dhash(8559245352688506666,100,0)

I have tried to recreate the index (create new index, drop the old one), but it doesnt help, the error comes back anyway. Also, its not there all the time, when I tried to rerun the statement from the logfile above a couple of hours later it worked fine without any error.

The server is a read slave, set up with streaming replication. We run PostgreSQL 9.3.5.

Is this anything I should be worried about, and if so, what can I do to fix it? Will it be fixed with a newer version of Postgres?

Thanks!

Victor

Re: Index contains unexpected zero page at block

From

Tom Lane

Date:

17 December 2015, 07:22:51

Victor Blomqvist <vb@viblo.se> writes:
>> From time to time I get this and similar errors in my Postgres log file:
> < 2015-12-17 07:45:05.976 CST >ERROR:  index
> "user_pictures_picture_dhash_idx" contains unexpected zero page at block
> 123780

Hm, can't tell for sure    from the error message text, but the index name
suggests that this is a hash index?

> The server is a read slave, set up with streaming replication. We run
> PostgreSQL 9.3.5.

Hash indexes are not WAL-logged, which means their contents do not
propagate to slave servers, which basically means you cannot use them
in replication setups.

> Will it be fixed with a newer version of Postgres?

Adding WAL-logging to hash indexes has been on the to-do list for a long
time; but it's never gotten done, in part because there has never been
any clear evidence that hash indexes are better than btree indexes for
any real-world purpose.  I'm curious why you chose this index type in
the first place.

            regards, tom lane

Re: Index contains unexpected zero page at block

From

Victor Blomqvist

Date:

17 December 2015, 07:30:24

Sorry, I should have included the index definition, its a normal btree index on a bigint column:

CREATE INDEX user_pictures_picture_dhash_idx
ON user_pictures
USING btree
(picture_dhash);

And the table itself:
CREATE TABLE user_pictures (picture_dhash bigint)

(and ~10 other columns not relevant for this I think)

/Victor

On Thu, Dec 17, 2015 at 12:22 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Victor Blomqvist <vb@viblo.se> writes:
>> From time to time I get this and similar errors in my Postgres log file:
> < 2015-12-17 07:45:05.976 CST >ERROR: index
> "user_pictures_picture_dhash_idx" contains unexpected zero page at block
> 123780

Hm, can't tell for sure from the error message text, but the index name
suggests that this is a hash index?

> The server is a read slave, set up with streaming replication. We run
> PostgreSQL 9.3.5.

Hash indexes are not WAL-logged, which means their contents do not
propagate to slave servers, which basically means you cannot use them
in replication setups.

> Will it be fixed with a newer version of Postgres?

Adding WAL-logging to hash indexes has been on the to-do list for a long
time; but it's never gotten done, in part because there has never been
any clear evidence that hash indexes are better than btree indexes for
any real-world purpose. I'm curious why you chose this index type in
the first place.

regards, tom lane

Re: Index contains unexpected zero page at block

From

Merlin Moncure

Date:

17 December 2015, 23:48:55

On Wed, Dec 16, 2015 at 9:48 PM, Victor Blomqvist <vb@viblo.se> wrote:
> From time to time I get this and similar errors in my Postgres log file:
>
> < 2015-12-17 07:45:05.976 CST >ERROR:  index
> "user_pictures_picture_dhash_idx" contains unexpected zero page at block
> 123780
> < 2015-12-17 07:45:05.976 CST >HINT:  Please REINDEX it.
> < 2015-12-17 07:45:05.976 CST >CONTEXT:  PL/pgSQL function
> select_pictures_by_dhash(bigint,integer,integer) line 3 at RETURN QUERY
> < 2015-12-17 07:45:05.976 CST >STATEMENT:  SELECT * FROM
> select_pictures_by_dhash(8559245352688506666,100,0)
>
> I have tried to recreate the index (create new index, drop the old one), but
> it doesnt help, the error comes back anyway. Also, its not there all the
> time, when I tried to rerun the statement from the logfile above a couple of
> hours later it worked fine without any error.
>
> The server is a read slave, set up with streaming replication. We run
> PostgreSQL 9.3.5.

Step #1: Install 9.3.10 on both servers.

If the issue is still then reproducing, then this problem gets a lot
more interesting.  Are you running with checksums on?

merlin