Thinking about EXPLAIN ALTER TABLE

I've been poking around with a feature I've wanted a number of times
in the past, "EXPLAIN ALTER TABLE". The idea is that there are a bunch
of optimizations in ALTER TABLE to minimize the amount of work and
lock levels but it's really hard for users to tell whether they've
written their ALTER TABLE commands carefully enough and properly to
trigger the optimizations. As a result it's really easy for to
accidentally take an exclusive lock and/or do a full table rewrite
when you were expecting to just do a quick catalog update.

The things I want to expose in ALTER TABLE are:

1. The lock level that's going to be taken
2. Whether a full table rewrite is going to happen
3. Whether a full table constraint validation is going to happen
4. Whether any indexes are going to be built or rebuilt
5. Whether the command is going to error out early due to syntax,
permissions, or other inconsistencies

Are there are other aspects of alter table that people would like to
see exposed that I haven't thought of?

For the most part ALTER TABLE is already structured such that this is
pretty easy. It does a lot of preparatory work without doing catalog
updates and I can just call that same preparatory work without calling
the subsequent work phases.

However there are a number of cases where decisions are made only
during the actual work phase, phase 2, and flags are set and work
enqueued for phase 3. In some cases the work that's enqueued would be
hard to predict in advance, for example if a partition is added a new
constraint is added for the partition but if that new constraint is
merged with an existing constraint (which is handled by
AddRelationNewConstraints()) then it doesn't need to be re-validated.

I'm thinking I should try to move all these decisions to phase 1 as
much as possible but I'm not sure how feasible it will be to get the
results exactly correct. Of course the cases where it's hardest to
predict are precisely where users would most like to know what's going
to happen...

If anyone has any ideas or tips on how to avoid these problems I'm all ears.

Currently the output is a bit rough, it looks like:

postgres=# explain alter table x2 add foreign key (i) references x1(i);
┌───────────────────────────────────┐
│ QUERY PLAN │
├───────────────────────────────────┤
│ Lock Level: ShareRowExclusiveLock │
│ ALTER TABLE: x2 │
│ Relation: x2 │
│ Rewrite: none │
└───────────────────────────────────┘

postgres***=# explain alter table t add column j integer generated
always as identity primary key;
┌─────────────────────────────────┐
│ QUERY PLAN │
├─────────────────────────────────┤
│ Lock Level: AccessExclusiveLock │
│ CREATE SEQUENCE: t_j_seq │
│ ALTER TABLE: t │
│ Relation: t │
│ Rewrite: none │
│ ALTER SEQUENCE: t_j_seq │
└─────────────────────────────────┘

postgres***=# explain alter table t set unlogged;
┌─────────────────────────────────────┐
│ QUERY PLAN │
├─────────────────────────────────────┤
│ Lock Level: AccessExclusiveLock │
│ ALTER TABLE: t │
│ Relation: t │
│ Rewrite: Due to ALTER PERSISTENCE │
└─────────────────────────────────────┘

postgres***=# explain alter table t alter column i set not null;
┌─────────────────────────────────┐
│ QUERY PLAN │
├─────────────────────────────────┤
│ Lock Level: AccessExclusiveLock │
│ ALTER TABLE: t │
│ Relation: t │
│ Rewrite: none │
│ Relation: t2 │
│ Rewrite: none │
└─────────────────────────────────┘

--
greg