Re: pg_verify_checksums failure with hash indexes

Hi,

On Tue, Aug 28, 2018 at 11:21:34AM +0200, Peter Eisentraut wrote:
> This is reproducible with PG11 and PG12:
>
> initdb -k data
> postgres -D data
>
> make installcheck
> # shut down postgres with Ctrl-C
>
> pg_verify_checksums data
>
> pg_verify_checksums: checksum verification failed in file
> "data/base/16384/28647", block 65: calculated checksum DC70 but expected 0
> pg_verify_checksums: checksum verification failed in file
> "data/base/16384/28649", block 65: calculated checksum 89D8 but expected 0
> pg_verify_checksums: checksum verification failed in file
> "data/base/16384/28648", block 65: calculated checksum 9636 but expected 0
> Checksum scan completed
> Data checksum version: 1
> Files scanned: 2493
> Blocks scanned: 13172
> Bad checksums: 3
>
> The files in question correspond to
>
> hash_i4_index
> hash_name_index
> hash_txt_index
>
> Discuss. ;-)

I took a look at hash_name_index, assuming the others are similar.

Page 65 is the last page, pageinspect barfs on it as well:

regression=# SELECT get_raw_page('hash_name_index', 'main', 65);
WARNING: page verification failed, calculated checksum 18066 but expected 0
ERROR: invalid page in block 65 of relation base/16384/28638

The pages before that one from page 35 on are empty:

regression=# SELECT * FROM page_header(get_raw_page('hash_name_index', 'main', 1));
lsn | checksum | flags | lower | upper | special | pagesize | version | prune_xid
-----------+----------+-------+-------+-------+---------+----------+---------+-----------
0/422D890 | 8807 | 0 | 664 | 5616 | 8176 | 8192 | 4 | 0
(1 Zeile)
[...]
regression=# SELECT * FROM page_header(get_raw_page('hash_name_index', 'main', 34));
lsn | checksum | flags | lower | upper | special | pagesize | version | prune_xid
-----------+----------+-------+-------+-------+---------+----------+---------+-----------
0/422C690 | 18153 | 0 | 580 | 5952 | 8176 | 8192 | 4 | 0
(1 Zeile)
regression=# SELECT * FROM page_header(get_raw_page('hash_name_index', 'main', 35));
lsn | checksum | flags | lower | upper | special | pagesize | version | prune_xid
-----+----------+-------+-------+-------+---------+----------+---------+-----------
0/0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
[...]
regression=# SELECT * FROM page_header(get_raw_page('hash_name_index', 'main', 64));
lsn | checksum | flags | lower | upper | special | pagesize | version | prune_xid
-----+----------+-------+-------+-------+---------+----------+---------+-----------
0/0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
regression=# SELECT * FROM page_header(get_raw_page('hash_name_index', 'main', 65));
WARNING: page verification failed, calculated checksum 18066 but expected 0
ERROR: invalid page in block 65 of relation base/16384/28638

Running pg_filedump on the last two pages results in (not sure the
"Invalid header information." are legit; neither about the checksum
failure on block 64):

mba(at)fock:~/[...]postgresql/build/src/test/regress$ ~/tmp/bin/pg_filedump -R 64 65 -k -f tmp_check/data/base/16384/28638

--8<--
*******************************************************************
* PostgreSQL File/Block Formatted Dump Utility - Version 10.1
*
* File: tmp_check/data/base/16384/28638
* Options used: -R 64 65 -k -f
*
* Dump created on: Tue Aug 28 14:53:37 2018
*******************************************************************

Block 64 ********************************************************
<Header> -----
Block Offset: 0x00080000 Offsets: Lower 0 (0x0000)
Block: Size 0 Version 0 Upper 0 (0x0000)
LSN: logid 0 recoff 0x00000000 Special 0 (0x0000)
Items: 0 Free Space: 0
Checksum: 0x0000 Prune XID: 0x00000000 Flags: 0x0000 ()
Length (including item array): 24

Error: Invalid header information.

Error: checksum failure: calculated 0xc66a.

0000: 00000000 00000000 00000000 00000000 ................
0010: 00000000 00000000 ........

<Data> ------
Empty block - no items listed

<Special Section> -----
Error: Invalid special section encountered.
Error: Special section points off page. Unable to dump contents.

Block 65 ********************************************************
<Header> -----
Block Offset: 0x00082000 Offsets: Lower 24 (0x0018)
Block: Size 8192 Version 4 Upper 8176 (0x1ff0)
LSN: logid 0 recoff 0x04229c20 Special 8176 (0x1ff0)
Items: 0 Free Space: 8152
Checksum: 0x0000 Prune XID: 0x00000000 Flags: 0x0000 ()
Length (including item array): 24

Error: checksum failure: calculated 0x4692.

0000: 00000000 209c2204 00000000 1800f01f .... .".........
0010: f01f0420 00000000 ... ....

<Data> ------
Empty block - no items listed

<Special Section> -----
Hash Index Section:
Flags: 0x0000 ()
Bucket Number: 0xffffffff
Blocks: Previous (-1) Next (-1)

1ff0: ffffffff ffffffff ffffffff 000080ff ................

*** End of Requested Range Encountered. Last Block Read: 65 ***
--8<--

So it seems there is some data on the last page, which makes the zero
checksum bogus, but I don't know anything about hash indexes. Also maybe
those empty pages are not initialized correctly? Or maybe the "Invalid
special section encountered" error meand pg_filedump cannot handle hash
indexes completely.

In any case, I am not sure pg_verify_checksums is at fault here.

Michael

--
Michael Banck
Projektleiter / Senior Berater
Tel.: +49 2166 9901-171
Fax: +49 2166 9901-100
Email: michael(dot)banck(at)credativ(dot)de

credativ GmbH, HRB Mönchengladbach 12080
USt-ID-Nummer: DE204566209
Trompeterallee 108, 41189 Mönchengladbach
Geschäftsführung: Dr. Michael Meskes, Jörg Folz, Sascha Heuer

Unser Umgang mit personenbezogenen Daten unterliegt
folgenden Bestimmungen: https://www.credativ.de/datenschutz