COPY TO BLACKHOLE / pg_dump -j -Fb

Hi Jakub, The use case that this feature addresses is interesting, but the
proposed implementation feels a bit ad-hoc to us: 1. `COPY ... TO ...`
implies moving data from one place to another. Having a `BLACKHOLE`
destination whose purpose is to check for data corruption seems
counter-intuitive. 2. The argument to `pg_dump --format=...` specifies an
archive format. A `blackhole` archive format and the `archBlackhole` entry
in the `ArchiveFormat` enum in the `pg_dump` code also seem ad-hoc and
counter-intuitive, because nothing is actually being archived. For those
reasons, I don't think `COPY` or `pg_dump` is the right place to support
this use case. Perhaps this needs a new command, but that might
overcomplicate things. In that case, I would prefer to leave things as they
are. Regards, Ahmed & Miłosz

On Wed, Jul 29, 2026 at 4:09 PM ahmed <gouda0x@gmail.com> wrote:

Hi Jakub, The use case that this feature addresses is interesting, but the proposed implementation feels a bit ad-hoc to us:

1. `COPY ... TO ...` implies moving data from one place to another. Having a `BLACKHOLE` destination whose purpose is to check for data corruption seems counter-intuitive.

2. The argument to `pg_dump --format=...` specifies an archive format. A `blackhole` archive format and the `archBlackhole` entry in the `ArchiveFormat` enum in the `pg_dump` code also seem ad-hoc and counter- intuitive, because nothing is actually being archived.

For those reasons, I don't think `COPY` or `pg_dump` is the right place to support this use case. Perhaps this needs a new command, but that might overcomplicate things. In that case, I would prefer to leave things as they are.

Hi Ahmed & Milosz,

thanks for reminding me of this old thread :)

I partially agree. Let me explain, this was based on minimal changes approach
to have it in and re-use parallel scheduling for this SQL syntax in pg_dump
(the primary idea is to have one simple one-liner command that people could
use to logically check their databases somewhat similiar to pg_amcheck, but
using more-rigorious "COPY" than amcheck functions do itself; to this day
people are using pg_dump > /dev/null for this but it really writes and sadly
cannot use --jobs).

Perhaps we should have some other command or even amcheck function to do the
same, but that would re-implementation of everything that actually COPY does,
so maybe instead it would be good idea to have COPY .. TO BLACKHOLE (or COPY
VERIFY %tablename%?), but support launching many jobs using pg_amcheck --jobs
rather than having pg_dump --jobs (and as You mention this is not archiving),
as it would be more suited for logical database verification. I'm pretty open
for speciifc naming proposals if anyone has those, before writing new version
of the patch.

-J.

To force full construction of tuples I usually use

SELECT sum(hashtext(t::text) FROM <tablename> AS t;

For "BLACKHOLE" you can use '/dev/null' most of the time, so

COPY tablename TO '/dev/null';

On Thu, Jul 30, 2026 at 9:00 AM Jakub Wartak <jakub.wartak@enterprisedb.com>
wrote:

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.

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.
--
Michael

On Fri, Jul 31, 2026 at 4:47 AM Michael Paquier <michael@paquier.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]/messages/by-id/432626F9-65DF-4F0D-B345-26CFC3E2CFAC@yandex-team.ru 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] - /messages/by-id/013D63E2-5D75-492E-85FF-1D5CC0148C82@gmail.com
[1b] - /messages/by-id/CAKZiRmwCT=pqesAjC4-2rRLWQ2uiBkrmJBhWeTgALn6yfqbuew@mail.gmail.com
[2]: /messages/by-id/432626F9-65DF-4F0D-B345-26CFC3E2CFAC@yandex-team.ru