Re: Document atthasmissing default optimization avoids verification table scan - Mailing list pgsql-hackers

From Bossart, Nathan
Subject Re: Document atthasmissing default optimization avoids verification table scan
Date
Msg-id 90098862-68F6-4C57-B924-EF61CB12AAB3@amazon.com
Whole thread Raw
In response to Document atthasmissing default optimization avoids verification table scan  (James Coleman <jtc331@gmail.com>)
Responses Re: Document atthasmissing default optimization avoids verification table scan
List pgsql-hackers
On 9/24/21, 7:30 AM, "James Coleman" <jtc331@gmail.com> wrote:
> When PG11 added the ability for ALTER TABLE ADD COLUMN to set a constant
> default value without rewriting the table the doc changes did not note
> how the new feature interplayed with ADD COLUMN DEFAULT NOT NULL.
> Previously such a new column required a verification table scan to
> ensure no values were null. That scan happens under an exclusive lock on
> the table, so it can have a meaningful impact on database "accessible
> uptime".

I'm likely misunderstanding, but are you saying that adding a new
column with a default value and a NOT NULL constraint used to require
a verification scan?

+     Additionally adding a column with a constant default value avoids a
+     a table scan to verify no <literal>NULL</literal> values are present.

Should this clarify that it's referring to NOT NULL constraints?

Nathan


pgsql-hackers by date:

Previous
From: Tom Lane
Date:
Subject: Re: Avoid erroring out when unable to remove or parse logical rewrite files to save checkpoint work
Next
From: "Bossart, Nathan"
Date:
Subject: Re: Add checkpoint and redo LSN to LogCheckpointEnd log message