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

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. I believe a small clarification for the doc entry is the most efficient thing.

Sent from Yahoo Mail for iPhone

On Thursday, July 31, 2025, 09:01, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

On Thursday, July 31, 2025, Álvaro Herrera <alvherre(at)kurilemu(dot)de> wrote:

On 2025-Jul-30, David G. Johnston wrote:

> On Wed, Jul 30, 2025, 13:55 PG Doc comments form <noreply(at)postgresql(dot)org>
> wrote:

> > The "table scan is skipped" optimization can use some clarification
> >
> > https://www.postgresql.org/doc s/current/sql-altertable.html# SQL-ALTERTABLE-DESC-SET-DROP- NOT-NULL
> > My proposal is "then the table scan is skipped if the alter statement
> > doesn't drop the constraint."

> I'm kinda hoping this is actually just a fixable bug...

I don't think so -- it's just the way ALTER TABLE is designed to work.
We don't promise that the subcommands are going to be executed in the
order that they are given, and thus this sort of thing can happen.
I suspect a mechanism that would throw an error at trying to drop the
constraint would be too complicated / brittle / laborious to write.

I wouldn’t want an error.  At the start of the command the constraint existed and its presence then would be enough.  It is immaterial that it went away during the command.  But it’s definitely not something that seems worth spending a non-trivial amount of effort on. 

(This is correct for 18; for 17 and earlier, the mention of NOT VALID
needs to be removed.)  Of course, in 18 you'd rely on ADD NOT NULL NOT
VALID instead of using a separate CHECK constraint.

Yeah, the main question here is whether we want to document for v17 and earlier what the article points out regarding locks.

Not sure if this reads better:

   if a valid <literal>CHECK</literal> constraint is
   found (and is not dropped in the same command) which
   proves no <literal>NULL</literal> can exist, then

If a valid check constraint exists (and is not dropped in the same command) which proves the absence of NULLs, then
I do agree the parenthetical should appear closer to the word constraint.
David J.