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
