Re: Thinking about EXPLAIN ALTER TABLE - Mailing list pgsql-hackers

Hi Greg

On 2018-Dec-07, Greg Stark wrote:

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

Maybe you can move some of these decisions to phase 1, but I'm not sure
it can be done for all of them.  Another possible plan is to add a flag
"dry run" so that phases 2/3 do whatever analysis they need to report
for your EXPLAIN, but not actually carry out their tasks.  (I see two
options to implement this, one is a global flag and the other is a new
argument to all those routines.)

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

Note there's a relation scan that doesn't rewrite (to verify constraints
IIRC).  That's certainly worth reporting in some form.  Maybe instead of
"Rewrite:" use something like "Scan: read-only / rewrite due to ALTER
PERSISTENCE".

But ... not sure what you propose to print when a table rewrite is
caused by two subcommands, say change persistence at the same time as a
column datatype.  And what if you add a new constraint together with
those two?

> 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                 │
> └─────────────────────────────────┘

I think putting the "Rewrite:" at the same indentation level as the
relation that it qualifies is confusing.  I'd do it this way:

 ┌─────────────────────────────────┐
 │           QUERY PLAN            │
 ├─────────────────────────────────┤
 │ Lock Level: AccessExclusiveLock │
 │ ALTER TABLE: t                  │
 │   Relation: t                   │
 │     Rewrite: none               │
 │   Relation: t2                  │
 │     Rewrite: none               │
 └─────────────────────────────────┘

Maybe make the output some legible form of YAML or JSON?

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services