Re: COPY TO BLACKHOLE / pg_dump -j -Fb

On Fri, Jul 31, 2026 at 4:47 AM Michael Paquier <michael(at)paquier(dot)xyz> wrote:

Thanks Michael for taking time to respond to this thread! I'm still in seeking
an answer what to do here next, see below for my doubts, yet I kind of torn
between marking this as rejected with feedback OR working on getting this
into pg_amcheck instead (please see that repro outcome)

> On Wed, May 20, 2026 at 09:16:28AM +0200, Jakub Wartak wrote:
> > From time to time we hit some corruption issue and usually we end up checking
> > for corruption with COPY to /dev/null and/or with verify_heapam(). Both seem
> > to detect different kind of corruption types, so I'm assuming they are somehow
> > complementary (e.g. seems that COPY is slower in cached case, but exercises
> > TOAST way harder than the amcheck routing even with check_toast=>true). Also
> > there's usecase that we often ask people to 'just run pg_dump -j', but that
> > requires -Fd <dir> which then requires plenty of disk space if you want to
> > verify whole DB in parallel, you need plenty of space (which is unrealistic on
> > big installations)
>
> Another limitation of COPY is that it does not check the state of
> index pages, neither does pg_dump. If we need better tooling for the
> detection of corruption, we should have new tools or improve the
> existing tools that exist for this purpose. pg_dump and COPY are a
> popular way to check some data state, but it's been historically wrong
> because it just points to people not knowing what to do, because
> perhaps what we have is thought as hard to use or just bad.

[..]

> pg_catcheck is another tool of this kind that I like a lot, in terms
> of corruption check. That has helped me a lot in the past.

Yes, but even with pg_catcheck --select-from-relations it just pulls SELECT
LIMIT 1 just to locate first and only first segment (it doesn't even care to
check more; see [1] and in the [2] we have an attempt to enhance somehow
the situation).

> Forcing consistency modes into queries that are not designed for this
> purpose is IMO a design mistake, making some code more complicated
> than it actually should. Providing more context regarding COPY and
> why this would be a bad idea: additions in its code path are *never*
> free, especially for row-level processing, and can show up very easily
> depending on the schema and/or the data pattern copied from/to.
>
> My 2c.

I accept the scepticism (it's sound!) and it made me rethink hard couple
of things here (for sure placing it under pg_dump is the wrong here). You
are pretty much spot on that we should do more in amcheck. However
verify_heapam() seems to be more about verifing page metadata (?) rather than
interpreting data for real (logical tuple reconstruction ) and that's why
I've though COPY/SELECT is often better/complementary to the amcheck. The sad
thing is that we need to to read all of the data twice. I have no such big
axe to plug all the holes within verify_heapam() and it could become something
completley diffent and end up being pretty much what COPY does anyway (?).

E.g. sample case/brutal case is TOAST corruption where verify_heapam does not
even attempt to decompress the data. The attached reproducer that gives:

chunk_seq=0 blk=0 lp_off=6160 t_hoff=24 lp_len=2032
corrupting 128 bytes of compressed data at file offset 6204 in base/5/16444
[..]
checking for corruption:
blkno | offnum | attnum | msg
-------+--------+--------+-----
(0 rows)

2026-08-07 11:05:09.882 CEST [56638] ERROR: compressed pglz data is corrupt
2026-08-07 11:05:09.882 CEST [56638] STATEMENT: COPY t TO '/dev/null';
ERROR: compressed pglz data is corrupt
2026-08-07 11:05:09.883 CEST [56638] ERROR: compressed pglz data is corrupt
2026-08-07 11:05:09.883 CEST [56638] STATEMENT: SELECT length(v) FROM t;
ERROR: compressed pglz data is corrupt

That 0 rows from verify_heapam() is bad news here, yes that's without
checksums, but it's about making the point the thing is that I have no idea
how many issues like those are on the table even if checksums would be on
(I'm not knowledgable enough about heap and TOAST). I suspect lot, but why
waste time on plugging all of the the amcheck holes to duplicate what COPY
seems to be already doing? (minus, we could just avoid outputting rows for
small efficency?)

However I fully accpet that pg_dump -j $VCPUs -F<blackhole> is the wrong fit,
so maybe then pg_amcheck -j $VCPUs --copy-to-blackhole/--copy is the way to
go? Or maybe just patch pg_amcheck to have -j $VCPUs --copy-to-null and avoid
even touching core COPY code...

> additions in its code path are *never* free, especially for row-level
processing,

The patch tries actually tries to avoid going into that (it's one branch which
is going to be completley predicitable for CPU): that is
+ else if (cstate->is_blackhole && whereToSendOutput == DestRemote)

-J.

[1a] - https://www.postgresql.org/message-id/flat/013D63E2-5D75-492E-85FF-1D5CC0148C82(at)gmail(dot)com
[1b] - https://www.postgresql.org/message-id/CAKZiRmwCT%3DpqesAjC4-2rRLWQ2uiBkrmJBhWeTgALn6yfqbuew%40mail.gmail.com
[2] - https://www.postgresql.org/message-id/flat/432626F9-65DF-4F0D-B345-26CFC3E2CFAC%40yandex-team.ru