Re: More tests to stress directly checksum_impl.h

Michael Paquier <michael@paquier.xyz> writes:
> On Fri, Mar 06, 2020 at 03:04:27PM -0500, Tom Lane wrote:
>> Between that and the BLCKSZ dependency, it's not clear that we can
>> test this with just a plain old expected-file test case.  Might
>> need to fall back to a TAP test.

> Perhaps the dependency of page.sql on 8kB pages could be improved,
> still I am not sure either that testing checksums is worth the
> complexity of a new TAP test dependent on pageinspect (5a9323e has
> removed such a dependency recently for example).

Yeah, a TAP test is a mighty expensive solution.

>> Another way would be variant output files, which could be a sane
>> solution if we put this in its own test script.

I think this way could work; see attached.

I'm not sure if it's actually worth providing the variants for non-8K
block sizes.  While running the tests to construct those, I was reminded
that not only do several of the other pageinspect tests "fail" at
nondefault block sizes, but so do the core regression tests and some
other tests as well.  We are a long way from having check-world pass
with nondefault block sizes, so maybe this test doesn't need to either.
However, there's something to be said for memorializing the behavior
we expect.

> (It is kind of hard to find a qemu image with big endian lately?)

The boneyard over on my other desk has actual hardware ;-)

            regards, tom lane

diff --git a/contrib/pageinspect/Makefile b/contrib/pageinspect/Makefile
index 4b6df96..d9d8177 100644
--- a/contrib/pageinspect/Makefile
+++ b/contrib/pageinspect/Makefile
@@ -19,7 +19,7 @@ DATA =  pageinspect--1.7--1.8.sql pageinspect--1.6--1.7.sql \
     pageinspect--1.0--1.1.sql
 PGFILEDESC = "pageinspect - functions to inspect contents of database pages"

-REGRESS = page btree brin gin hash
+REGRESS = page btree brin gin hash checksum

 ifdef USE_PGXS
 PG_CONFIG = pg_config
diff --git a/contrib/pageinspect/expected/checksum.out b/contrib/pageinspect/expected/checksum.out
new file mode 100644
index 0000000..ae67f6c
--- /dev/null
+++ b/contrib/pageinspect/expected/checksum.out
@@ -0,0 +1,39 @@
+--
+-- Verify correct calculation of checksums
+--
+-- Postgres' checksum algorithm produces different answers on little-endian
+-- and big-endian machines.  The results of this test also vary depending
+-- on the configured block size.  This test has several different expected
+-- results files to handle the following possibilities:
+--
+--    BLCKSZ    end    file
+--    8K    LE    checksum.out
+--    8K    BE    checksum_1.out
+--    16K    LE    checksum_2.out
+--    16K    BE    checksum_3.out
+--    32K    LE    checksum_4.out
+--    32K    BE    checksum_5.out
+-- This is to label the results files with blocksize:
+SHOW block_size;
+ block_size
+------------
+ 8192
+(1 row)
+
+SHOW block_size \gset
+-- Apply page_checksum() to some different data patterns and block numbers
+SELECT blkno,
+    page_checksum(decode(repeat('01', :block_size), 'hex'), blkno) AS checksum_01,
+    page_checksum(decode(repeat('04', :block_size), 'hex'), blkno) AS checksum_04,
+    page_checksum(decode(repeat('ff', :block_size), 'hex'), blkno) AS checksum_ff,
+    page_checksum(decode(repeat('abcd', :block_size / 2), 'hex'), blkno) AS checksum_abcd,
+    page_checksum(decode(repeat('e6d6', :block_size / 2), 'hex'), blkno) AS checksum_e6d6,
+    page_checksum(decode(repeat('4a5e', :block_size / 2), 'hex'), blkno) AS checksum_4a5e
+  FROM generate_series(0, 100, 50) AS a (blkno);
+ blkno | checksum_01 | checksum_04 | checksum_ff | checksum_abcd | checksum_e6d6 | checksum_4a5e
+-------+-------------+-------------+-------------+---------------+---------------+---------------
+     0 |        1175 |       28338 |        3612 |        -30781 |        -16269 |        -27377
+    50 |        1225 |       28352 |        3598 |        -30795 |        -16251 |        -27391
+   100 |        1139 |       28438 |        3648 |        -30881 |        -16305 |        -27349
+(3 rows)
+
diff --git a/contrib/pageinspect/expected/checksum_1.out b/contrib/pageinspect/expected/checksum_1.out
new file mode 100644
index 0000000..942a302
--- /dev/null
+++ b/contrib/pageinspect/expected/checksum_1.out
@@ -0,0 +1,39 @@
+--
+-- Verify correct calculation of checksums
+--
+-- Postgres' checksum algorithm produces different answers on little-endian
+-- and big-endian machines.  The results of this test also vary depending
+-- on the configured block size.  This test has several different expected
+-- results files to handle the following possibilities:
+--
+--    BLCKSZ    end    file
+--    8K    LE    checksum.out
+--    8K    BE    checksum_1.out
+--    16K    LE    checksum_2.out
+--    16K    BE    checksum_3.out
+--    32K    LE    checksum_4.out
+--    32K    BE    checksum_5.out
+-- This is to label the results files with blocksize:
+SHOW block_size;
+ block_size
+------------
+ 8192
+(1 row)
+
+SHOW block_size \gset
+-- Apply page_checksum() to some different data patterns and block numbers
+SELECT blkno,
+    page_checksum(decode(repeat('01', :block_size), 'hex'), blkno) AS checksum_01,
+    page_checksum(decode(repeat('04', :block_size), 'hex'), blkno) AS checksum_04,
+    page_checksum(decode(repeat('ff', :block_size), 'hex'), blkno) AS checksum_ff,
+    page_checksum(decode(repeat('abcd', :block_size / 2), 'hex'), blkno) AS checksum_abcd,
+    page_checksum(decode(repeat('e6d6', :block_size / 2), 'hex'), blkno) AS checksum_e6d6,
+    page_checksum(decode(repeat('4a5e', :block_size / 2), 'hex'), blkno) AS checksum_4a5e
+  FROM generate_series(0, 100, 50) AS a (blkno);
+ blkno | checksum_01 | checksum_04 | checksum_ff | checksum_abcd | checksum_e6d6 | checksum_4a5e
+-------+-------------+-------------+-------------+---------------+---------------+---------------
+     0 |      -16327 |        8766 |       -2722 |         13757 |        -11485 |        -31426
+    50 |      -16281 |        8780 |       -2708 |         13771 |        -11503 |        -31440
+   100 |      -16235 |        8866 |       -2758 |         13721 |        -11577 |        -31518
+(3 rows)
+
diff --git a/contrib/pageinspect/expected/checksum_2.out b/contrib/pageinspect/expected/checksum_2.out
new file mode 100644
index 0000000..1c98f8f
--- /dev/null
+++ b/contrib/pageinspect/expected/checksum_2.out
@@ -0,0 +1,39 @@
+--
+-- Verify correct calculation of checksums
+--
+-- Postgres' checksum algorithm produces different answers on little-endian
+-- and big-endian machines.  The results of this test also vary depending
+-- on the configured block size.  This test has several different expected
+-- results files to handle the following possibilities:
+--
+--    BLCKSZ    end    file
+--    8K    LE    checksum.out
+--    8K    BE    checksum_1.out
+--    16K    LE    checksum_2.out
+--    16K    BE    checksum_3.out
+--    32K    LE    checksum_4.out
+--    32K    BE    checksum_5.out
+-- This is to label the results files with blocksize:
+SHOW block_size;
+ block_size
+------------
+ 16384
+(1 row)
+
+SHOW block_size \gset
+-- Apply page_checksum() to some different data patterns and block numbers
+SELECT blkno,
+    page_checksum(decode(repeat('01', :block_size), 'hex'), blkno) AS checksum_01,
+    page_checksum(decode(repeat('04', :block_size), 'hex'), blkno) AS checksum_04,
+    page_checksum(decode(repeat('ff', :block_size), 'hex'), blkno) AS checksum_ff,
+    page_checksum(decode(repeat('abcd', :block_size / 2), 'hex'), blkno) AS checksum_abcd,
+    page_checksum(decode(repeat('e6d6', :block_size / 2), 'hex'), blkno) AS checksum_e6d6,
+    page_checksum(decode(repeat('4a5e', :block_size / 2), 'hex'), blkno) AS checksum_4a5e
+  FROM generate_series(0, 100, 50) AS a (blkno);
+ blkno | checksum_01 | checksum_04 | checksum_ff | checksum_abcd | checksum_e6d6 | checksum_4a5e
+-------+-------------+-------------+-------------+---------------+---------------+---------------
+     0 |       11110 |       11537 |        5518 |         29279 |         -5920 |         24342
+    50 |       11064 |       11587 |        5472 |         29293 |         -5966 |         24388
+   100 |       11138 |       11509 |        5418 |         29371 |         -6012 |         24434
+(3 rows)
+
diff --git a/contrib/pageinspect/expected/checksum_3.out b/contrib/pageinspect/expected/checksum_3.out
new file mode 100644
index 0000000..7f62d22
--- /dev/null
+++ b/contrib/pageinspect/expected/checksum_3.out
@@ -0,0 +1,39 @@
+--
+-- Verify correct calculation of checksums
+--
+-- Postgres' checksum algorithm produces different answers on little-endian
+-- and big-endian machines.  The results of this test also vary depending
+-- on the configured block size.  This test has several different expected
+-- results files to handle the following possibilities:
+--
+--    BLCKSZ    end    file
+--    8K    LE    checksum.out
+--    8K    BE    checksum_1.out
+--    16K    LE    checksum_2.out
+--    16K    BE    checksum_3.out
+--    32K    LE    checksum_4.out
+--    32K    BE    checksum_5.out
+-- This is to label the results files with blocksize:
+SHOW block_size;
+ block_size
+------------
+ 16384
+(1 row)
+
+SHOW block_size \gset
+-- Apply page_checksum() to some different data patterns and block numbers
+SELECT blkno,
+    page_checksum(decode(repeat('01', :block_size), 'hex'), blkno) AS checksum_01,
+    page_checksum(decode(repeat('04', :block_size), 'hex'), blkno) AS checksum_04,
+    page_checksum(decode(repeat('ff', :block_size), 'hex'), blkno) AS checksum_ff,
+    page_checksum(decode(repeat('abcd', :block_size / 2), 'hex'), blkno) AS checksum_abcd,
+    page_checksum(decode(repeat('e6d6', :block_size / 2), 'hex'), blkno) AS checksum_e6d6,
+    page_checksum(decode(repeat('4a5e', :block_size / 2), 'hex'), blkno) AS checksum_4a5e
+  FROM generate_series(0, 100, 50) AS a (blkno);
+ blkno | checksum_01 | checksum_04 | checksum_ff | checksum_abcd | checksum_e6d6 | checksum_4a5e
+-------+-------------+-------------+-------------+---------------+---------------+---------------
+     0 |      -16158 |        9656 |      -23891 |          8765 |          8089 |          -626
+    50 |      -16172 |        9706 |      -23909 |          8815 |          8075 |          -672
+   100 |      -16250 |        9620 |      -23863 |          8737 |          8125 |          -718
+(3 rows)
+
diff --git a/contrib/pageinspect/expected/checksum_4.out b/contrib/pageinspect/expected/checksum_4.out
new file mode 100644
index 0000000..75d5ab8
--- /dev/null
+++ b/contrib/pageinspect/expected/checksum_4.out
@@ -0,0 +1,39 @@
+--
+-- Verify correct calculation of checksums
+--
+-- Postgres' checksum algorithm produces different answers on little-endian
+-- and big-endian machines.  The results of this test also vary depending
+-- on the configured block size.  This test has several different expected
+-- results files to handle the following possibilities:
+--
+--    BLCKSZ    end    file
+--    8K    LE    checksum.out
+--    8K    BE    checksum_1.out
+--    16K    LE    checksum_2.out
+--    16K    BE    checksum_3.out
+--    32K    LE    checksum_4.out
+--    32K    BE    checksum_5.out
+-- This is to label the results files with blocksize:
+SHOW block_size;
+ block_size
+------------
+ 32768
+(1 row)
+
+SHOW block_size \gset
+-- Apply page_checksum() to some different data patterns and block numbers
+SELECT blkno,
+    page_checksum(decode(repeat('01', :block_size), 'hex'), blkno) AS checksum_01,
+    page_checksum(decode(repeat('04', :block_size), 'hex'), blkno) AS checksum_04,
+    page_checksum(decode(repeat('ff', :block_size), 'hex'), blkno) AS checksum_ff,
+    page_checksum(decode(repeat('abcd', :block_size / 2), 'hex'), blkno) AS checksum_abcd,
+    page_checksum(decode(repeat('e6d6', :block_size / 2), 'hex'), blkno) AS checksum_e6d6,
+    page_checksum(decode(repeat('4a5e', :block_size / 2), 'hex'), blkno) AS checksum_4a5e
+  FROM generate_series(0, 100, 50) AS a (blkno);
+ blkno | checksum_01 | checksum_04 | checksum_ff | checksum_abcd | checksum_e6d6 | checksum_4a5e
+-------+-------------+-------------+-------------+---------------+---------------+---------------
+     0 |       26028 |       17735 |       21350 |         -8851 |        -17998 |         -7046
+    50 |       26042 |       17721 |       21400 |         -8837 |        -18012 |         -6996
+   100 |       26120 |       17771 |       21442 |         -8759 |        -18090 |         -6946
+(3 rows)
+
diff --git a/contrib/pageinspect/expected/checksum_5.out b/contrib/pageinspect/expected/checksum_5.out
new file mode 100644
index 0000000..3a68ccc
--- /dev/null
+++ b/contrib/pageinspect/expected/checksum_5.out
@@ -0,0 +1,39 @@
+--
+-- Verify correct calculation of checksums
+--
+-- Postgres' checksum algorithm produces different answers on little-endian
+-- and big-endian machines.  The results of this test also vary depending
+-- on the configured block size.  This test has several different expected
+-- results files to handle the following possibilities:
+--
+--    BLCKSZ    end    file
+--    8K    LE    checksum.out
+--    8K    BE    checksum_1.out
+--    16K    LE    checksum_2.out
+--    16K    BE    checksum_3.out
+--    32K    LE    checksum_4.out
+--    32K    BE    checksum_5.out
+-- This is to label the results files with blocksize:
+SHOW block_size;
+ block_size
+------------
+ 32768
+(1 row)
+
+SHOW block_size \gset
+-- Apply page_checksum() to some different data patterns and block numbers
+SELECT blkno,
+    page_checksum(decode(repeat('01', :block_size), 'hex'), blkno) AS checksum_01,
+    page_checksum(decode(repeat('04', :block_size), 'hex'), blkno) AS checksum_04,
+    page_checksum(decode(repeat('ff', :block_size), 'hex'), blkno) AS checksum_ff,
+    page_checksum(decode(repeat('abcd', :block_size / 2), 'hex'), blkno) AS checksum_abcd,
+    page_checksum(decode(repeat('e6d6', :block_size / 2), 'hex'), blkno) AS checksum_e6d6,
+    page_checksum(decode(repeat('4a5e', :block_size / 2), 'hex'), blkno) AS checksum_4a5e
+  FROM generate_series(0, 100, 50) AS a (blkno);
+ blkno | checksum_01 | checksum_04 | checksum_ff | checksum_abcd | checksum_e6d6 | checksum_4a5e
+-------+-------------+-------------+-------------+---------------+---------------+---------------
+     0 |      -14560 |      -27746 |       32614 |          2147 |         -8732 |        -27565
+    50 |      -14546 |      -27700 |       32632 |          2193 |         -8746 |        -27551
+   100 |      -14596 |      -27782 |       32578 |          2119 |         -8704 |        -27473
+(3 rows)
+
diff --git a/contrib/pageinspect/sql/checksum.sql b/contrib/pageinspect/sql/checksum.sql
new file mode 100644
index 0000000..fdb3250
--- /dev/null
+++ b/contrib/pageinspect/sql/checksum.sql
@@ -0,0 +1,30 @@
+--
+-- Verify correct calculation of checksums
+--
+-- Postgres' checksum algorithm produces different answers on little-endian
+-- and big-endian machines.  The results of this test also vary depending
+-- on the configured block size.  This test has several different expected
+-- results files to handle the following possibilities:
+--
+--    BLCKSZ    end    file
+--    8K    LE    checksum.out
+--    8K    BE    checksum_1.out
+--    16K    LE    checksum_2.out
+--    16K    BE    checksum_3.out
+--    32K    LE    checksum_4.out
+--    32K    BE    checksum_5.out
+
+-- This is to label the results files with blocksize:
+SHOW block_size;
+
+SHOW block_size \gset
+
+-- Apply page_checksum() to some different data patterns and block numbers
+SELECT blkno,
+    page_checksum(decode(repeat('01', :block_size), 'hex'), blkno) AS checksum_01,
+    page_checksum(decode(repeat('04', :block_size), 'hex'), blkno) AS checksum_04,
+    page_checksum(decode(repeat('ff', :block_size), 'hex'), blkno) AS checksum_ff,
+    page_checksum(decode(repeat('abcd', :block_size / 2), 'hex'), blkno) AS checksum_abcd,
+    page_checksum(decode(repeat('e6d6', :block_size / 2), 'hex'), blkno) AS checksum_e6d6,
+    page_checksum(decode(repeat('4a5e', :block_size / 2), 'hex'), blkno) AS checksum_4a5e
+  FROM generate_series(0, 100, 50) AS a (blkno);