Feedback-ID: ie3de48e3:Fastmail
Date: Mon, 4 Aug 2025 13:32:06 +0200
From: =?utf-8?Q?=C3=81lvaro?= Herrera <alvherre@kurilemu.de>
To: Shuyu Pan <psy2000usa@yahoo.com>
Cc: "David G. Johnston" <david.g.johnston@gmail.com>,
	PostgreSQL Documentation <pgsql-docs@lists.postgresql.org>
Subject: Re: further clarification: alter table alter column set not null -
 table scan is skipped
Message-ID: <202508041132.d5q2a4nyt22v@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <1167230960.326897.1753987280731@mail.yahoo.com>
Archived-At: 
 <https://www.postgresql.org/message-id/202508041132.d5q2a4nyt22v%40alvherre.pgsql>
Precedence: bulk

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/