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