Re: pg_amcheck contrib application

> On Apr 8, 2021, at 3:11 PM, Robert Haas <robertmhaas@gmail.com> wrote:
> 
> On Thu, Apr 8, 2021 at 5:21 PM Mark Dilger <mark.dilger@enterprisedb.com> wrote:
>> All this leads me to believe that we should report the following:
>> 
>> 1) If the total number of chunks retrieved differs from the expected number, report how many we expected vs. how many we got
>> 2) If the chunk_seq numbers are discontiguous, report each discontiguity.
>> 3) If the index scan returned chunks out of chunk_seq order, report that
>> 4) If any chunk is not the expected size, report that
>> 
>> So, for your example of chunk 1 missing from chunks [0..17], we'd report that we got one fewer chunks than we expected, that the second chunk seen was discontiguous from the first chunk seen, that the final chunk seen was smaller than expected by M bytes, and that the total size was smaller than we expected by N bytes.  The third of those is somewhat misleading, since the final chunk was presumably the right size; we just weren't expecting to hit a partial chunk quite yet.  But I don't see how to make that better in the general case.
> 
> Hmm, that might be OK. It seems like it's going to be a bit verbose in
> simple cases like 1 missing chunk, but on the plus side, it avoids a
> mountain of output if the raw size has been overwritten with a
> gigantic bogus value. But, how is #2 different from #3? Those sound
> like the same thing to me.

I think #4, above, requires some clarification.  If there are missing chunks, the very definition of how large we expect subsequent chunks to be is ill-defined.  I took a fairly conservative approach to avoid lots of bogus complaints about chunks that are of unexpected size.   Not all such complaints are removed, but enough are removed that I needed to add a final complaint at the end about the total size seen not matching the total size expected.

Here are a set of corruptions with the corresponding corruption reports from before and from after the code changes.  The corruptions are *not* cumulative.

Honestly, I'm not totally convinced that these changes are improvements in all cases.  Let me know if you want further changes, or if you'd like to see other corruptions and their before and after results.


Corruption #1:

	UPDATE $toastname SET chunk_seq = chunk_seq + 1000

Before:

# heap table "postgres"."public"."test", block 0, offset 2, attribute 2:
#     toast value 16445 chunk 0 has sequence number 1000, but expected sequence number 0
# heap table "postgres"."public"."test", block 0, offset 2, attribute 2:
#     toast value 16445 chunk 1 has sequence number 1001, but expected sequence number 1
# heap table "postgres"."public"."test", block 0, offset 2, attribute 2:
#     toast value 16445 chunk 2 has sequence number 1002, but expected sequence number 2
# heap table "postgres"."public"."test", block 0, offset 2, attribute 2:
#     toast value 16445 chunk 3 has sequence number 1003, but expected sequence number 3
# heap table "postgres"."public"."test", block 0, offset 2, attribute 2:
#     toast value 16445 chunk 4 has sequence number 1004, but expected sequence number 4
# heap table "postgres"."public"."test", block 0, offset 2, attribute 2:
#     toast value 16445 chunk 5 has sequence number 1005, but expected sequence number 5

After:

# heap table "postgres"."public"."test", block 0, offset 2, attribute 2:
#     toast value 16445 missing chunks 0 through 999


Corruption #2:

	UPDATE $toastname SET chunk_seq = chunk_seq * 1000

Before:

# heap table "postgres"."public"."test", block 0, offset 2, attribute 2:
#     toast value 16445 chunk 1 has sequence number 1000, but expected sequence number 1
# heap table "postgres"."public"."test", block 0, offset 2, attribute 2:
#     toast value 16445 chunk 2 has sequence number 2000, but expected sequence number 2
# heap table "postgres"."public"."test", block 0, offset 2, attribute 2:
#     toast value 16445 chunk 3 has sequence number 3000, but expected sequence number 3
# heap table "postgres"."public"."test", block 0, offset 2, attribute 2:
#     toast value 16445 chunk 4 has sequence number 4000, but expected sequence number 4
# heap table "postgres"."public"."test", block 0, offset 2, attribute 2:
#     toast value 16445 chunk 5 has sequence number 5000, but expected sequence number 5

After:

# heap table "postgres"."public"."test", block 0, offset 2, attribute 2:
#     toast value 16445 missing chunks 1 through 999
# heap table "postgres"."public"."test", block 0, offset 2, attribute 2:
#     toast value 16445 missing chunks 1001 through 1999
# heap table "postgres"."public"."test", block 0, offset 2, attribute 2:
#     toast value 16445 missing chunks 2001 through 2999
# heap table "postgres"."public"."test", block 0, offset 2, attribute 2:
#     toast value 16445 missing chunks 3001 through 3999
# heap table "postgres"."public"."test", block 0, offset 2, attribute 2:
#     toast value 16445 missing chunks 4001 through 4999
# heap table "postgres"."public"."test", block 0, offset 3, attribute 2:


Corruption #3:

	UPDATE $toastname SET chunk_id = (chunk_id::integer + 10000000)::oid WHERE chunk_seq = 3

Before:

# heap table "postgres"."public"."test", block 0, offset 2, attribute 2:
#     toast value 16445 chunk 3 has sequence number 4, but expected sequence number 3
# heap table "postgres"."public"."test", block 0, offset 2, attribute 2:
#     toast value 16445 chunk 4 has sequence number 5, but expected sequence number 4
# heap table "postgres"."public"."test", block 0, offset 2, attribute 2:
#     toast value 16445 was expected to end at chunk 6, but ended at chunk 5

After:

# heap table "postgres"."public"."test", block 0, offset 2, attribute 2:
#     toast value 16445 missing chunk 3
# heap table "postgres"."public"."test", block 0, offset 2, attribute 2:
#     toast value 16445 chunk 4 has size 20, but expected size 1996
# heap table "postgres"."public"."test", block 0, offset 2, attribute 2:
#     toast value 16445 was expected to end at chunk 6, but ended at chunk 5
# heap table "postgres"."public"."test", block 0, offset 2, attribute 2:
#     toast value 16445 was expected to have size 10000, but had size 8004
# heap table "postgres"."public"."test", block 0, offset 3, attribute 2:



—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company