On Wed, Jan 19, 2022 at 9:34 PM David G. Johnston
<[email protected]> wrote:
>
> On Wed, Jan 19, 2022 at 6:14 PM James Coleman <[email protected]> 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."
I'm looking over the docs again to see how it might be better
structured; point is well taken that we should have it clearly in the
primary place.
> On that note, does the check constraint interplay with the default rewrite
> avoidance in the same way?
I hadn't checked until you asked, but interestingly, no it doesn't (I
assume you mean scan not rewrite in this context):
test=# select seq_scan from pg_stat_all_tables where relname = 't2';
seq_scan
----------
2
test=# alter table t2 add column i3 int not null default 5;
ALTER TABLE
test=# select seq_scan from pg_stat_all_tables where relname = 't2';
seq_scan
----------
2
test=# alter table t2 add column i4 int default 5 check (i4 < 50);
ALTER TABLE
test=# select seq_scan from pg_stat_all_tables where relname = 't2';
seq_scan
----------
3
That seems like an opportunity for improvement here, though it's
obviously a separate patch. I might poke around at that though
later...
> 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.
As noted I'll look over how restructuring might improve and reply with
an updated proposed patch.
Thanks,
James Coleman
