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@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.banck@credativ.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