Re: further clarification: alter table alter column set not null - table scan is skipped

Thanks a lot Álvaro for preparing the clarification so quickly. Looking forward to the release.
If we mark a column to skip table scan during drop (phase 0) and later skip the table scan when setting attributes (phase 7), we will not risk data corruption if the Access Exclusive lock is never released between phase 0 and 7.


Sent from Yahoo Mail for iPhone


On Monday, August 4, 2025, 04:32, Álvaro Herrera <alvherre@kurilemu.de> wrote:

On 2025-Jul-31, Shuyu Pan wrote:

> I like your versions that emphasize: don’t drop the constraint in the
> same alter table set no null command.  Similar to David’s point, I
> spent some time trying to figure out a simple refactoring to carry the
> optimization all the way to the end but it might require executing
> “set not null” sooner which has a big impact. Another option is only
> implement a special treatment for this specific use case but it is a
> code smell to me.

Oh yeah, delaying the drop is much more likely to break other things.  I
was more thinking along the lines of maintaining a list of columns that
are known non-null at the start of the command (a bitmapset actually).
This could be computed in ALTER TABLE phase 1, and used later to
determine that no scans are needed.  But this is a lot of mechanism
which is useless 99% of the time, and moreso now that you can directly
add the NOT NULL constraints as NOT VALID to start with, which saves
having to mess with a separate CHECK constraint.

> I believe a small clarification for the doc entry is the most efficient thing.

Okay, I've pushed the change to all branches using David Johnston's
suggested wording.

Thank you all!

-- 
Álvaro Herrera        Breisgau, Deutschland  —  https://www.EnterpriseDB.com/