Re: Document atthasmissing default optimization avoids verification table scan

On Wed, Jan 19, 2022 at 6:14 PM James Coleman <jtc331(at)gmail(dot)com> wrote:

> I'm open to the idea of wordsmithing here, of course, but I strongly
> disagree that this is irrelevant data.

Ok, but wording aside, only changing a tip in the DDL - Add Table section
doesn't seem like a complete fix. The notes in alter table, where I'd look
for such an official directive first, need to be touched as well.

For the alter table docs maybe change/add to the existing sentence below
(I'm in favor of not pointing out that scanning the table doesn't mean we
are rewriting it, but maybe I'm making another unwarranted assumption
regarding obviousness...).

"Adding a CHECK or NOT NULL constraint requires scanning the table [but not
rewriting it] to verify that existing rows meet the constraint. It is
skipped when done as part of ADD COLUMN unless a table rewrite is required
anyway."

On that note, does the check constraint interplay with the default rewrite
avoidance in the same way?

For the Tip I'd almost rather redo it to say:

"Before PostgreSQL 11, adding a new column to a table required rewriting
that table, making it a very slow operation. More recent versions can
sometimes optimize away this rewrite and related validation scans. See the
notes in ALTER TABLE for details."

Though I suppose I'd accept something like (leave existing text,
alternative patch text):

"[...]large tables.\nIf the added column also has a not null constraint the
usual verification scan is also skipped."

"constant" is used in the Tip, "non-volatile" is used in alter table -
hence a desire to have just one source of truth, with alter table being the
correct place. We should sync them up otherwise.

David J.