Re: Document atthasmissing default optimization avoids verification table scan

On Sat, Mar 26, 2022 at 4:36 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> "David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> > Or, we can leave it where things are and make sure the reader understands
> > there are two paths to having a NOT NULL constraint on the newly added
> > column. Something like:
>
> > "If you plan on having a NOT NULL constraint on the newly added column
> you
> > should add it as a column constraint during the ADD COLUMN command. If
> you
> > add it later via ALTER COLUMN SET NOT NULL the table will have to be
> > completely scanned in order to ensure that no null values were inserted."
>
> The first way also requires having a non-null DEFAULT, of course, and
> then also that default value must be a constant (else you end up with
> a table rewrite which is even worse). This sort of interaction
> between features is why I feel that a separate unified discussion
> is the only reasonable solution.
>
>
The paragraph it is being added to discusses the table rewrite already.
This does nothing to contradict the fact that a table rewrite might still
have to happen.

The goal of this sentence is to tell the user to make sure they don't
forget to add the NOT NULL during the column add so that they don't have to
incur a future table scan by executing ALTER COLUMN SET NOT NULL.

I am assuming that the user understands when a table rewrite has to happen
and that the presence of NOT NULL in the ADD COLUMN doesn't impact that.
And if a table rewrite happens that a table scan happens implicitly.
Admittedly, this doesn't directly address the original complaint, but by
showing how the two commands differ I believe the confusion will go away.
SET NOT NULL performs a scan, ADD COLUMN NOT NULL does not; it just might
require something worse if the supplied default is volatile.

David J.