Re: Thinking about EXPLAIN ALTER TABLE

On Mon, 10 Dec 2018 at 11:14, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> wrote:
>
> 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.

Indeed I've done some work on this and it will be impossible to get it
100% of the way there. At least not with a reasonable amount of work.
Personally I think it can be close enough to be useful but others may
have a different opinion...

A typically example, and I think the main realistic source of
problems, is the partitioning bound constraint checks. They're elided
if the new bound can be proven from the existing constraints. But that
requires recursing through all the subpartitions which currently only
happens in the final phase. Worse, I think -- though I'm not 100%
clear on this -- that these constraint proofs can't even be done
earlier in the process because the constraints can refer to column
names that don't necessarily exist until earlier catalog changes are
done.

Personally I'm fine saying here's the plan -- some steps may be
optimized away at run-time but the locking and table rewrites
described should be correct and most of the time the constraint
validations should be correct too.

> 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 don't think this helps any. Anything that could be done in a dry run
could just be duplicated work earlier. The bits that cannot are the
bits that depend on catalog changes that are done in the same command.
The ony way they could be done in a "dry run" would be doing the whole
operation in a not-dry-run in a transaction and then rolling it back.
But that would produce a lot of production impact that would defeat
the whole purpose of having the command at all.

--
greg