[PATCH] Support for basic ALTER TABLE progress reporting.

From: Jiří Kavalík <jkavalik(at)gmail(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: [PATCH] Support for basic ALTER TABLE progress reporting.
Date: 2025-06-01 16:58:05
Message-ID: CAKMhz2mMerMby3uvqO6Gv8ptjkbR3WqZLjZ5Uhz4dvX_U=Zyjg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi,
while testing int->bigint migrations for our db at work I really missed
ALTER TABLE progress reporting and during the waits I checked the code.
It seems to me that ALTERs can be mostly categorized as
1) trivial ones - metadata rewrites, fast adding/removing columns, trying
to change column type to one already present etc. not much to report here
2) scanning ones - adding constraints - it imho gives enough info to report
blocks total and scanned and tuples scanned
3) rewrites - actually changing data or types - add number of written
blocks/tuples
3b) index rewrites - report number of indexes processed

From that it seems to me that the basic info is very similar to already
present CLUSTER/VACUUM-FULL reporting so I tried to tap into that and just
add a support for a new command.

I identified a handful of places where to add the reporting for ALTERs and
it seems to work,

What I changed:
`commands/progress.h`
- new cluster reporting command + new phase for FK checks

`commands/tablecmds.c`
- start and end reporting inside `ATRewriteTables()`
- report blocks total, blocks and tuples scanned and possibly tuples
written in `ATRewriteTable`
- add at least phase info in `validateForeignKeyConstraint`, possibly more
if the check cannot be done by left join

`catalog/system_views.sql`
- output for the new command and phase

`catalog/storage.c`
- number of blocks processed in `RelationCopyStorage()` for the case table
is moved between tablespaces by direct copying

+ some basic documentation updates

What I did not have to change - index rebuilds used by CLUSTER reported
their progress already, it just was not shown without a valid command
configured.

I ran some manual tests locally + ran regression tests and it seems to work
fine.

The reporting may be a bit crude and may be missing some phases but it
covers the IO-heavy operations with some reasonable numbers. (well, not the
FK check by left anti-join, but I don't want to mess with that + maybe
number of FKs checked might be shown?)

Thanks
Best regards
jkavalik

Attachment Content-Type Size
0001-ALTER-TABLE-progress-support.patch text/plain 14.3 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Melanie Plageman 2025-06-01 17:51:06 Re: RelationGetNumberOfBlocks called before vacuum_get_cutoffs
Previous Message Álvaro Herrera 2025-06-01 16:13:20 Re: CREATE DOMAIN create two not null constraints