Feedback-ID: ie3de48e3:Fastmail
Date: Thu, 31 Jul 2025 17:30:24 +0200
From: =?utf-8?Q?=C3=81lvaro?= Herrera <alvherre@kurilemu.de>
To: "David G. Johnston" <david.g.johnston@gmail.com>
Cc: psy2000usa@yahoo.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: <202507311530.ls53ug7urrgx@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: 
 <CAKFQuwbkV4yQU_5rHWwCfwSY3mjhh_jk50THwYEdRvwAZ36BRQ@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/202507311530.ls53ug7urrgx%40alvherre.pgsql>
Precedence: bulk

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

> On Wed, Jul 30, 2025, 13:55 PG Doc comments form <noreply@postgresql.org>
> wrote:

> > The "table scan is skipped" optimization can use some clarification
> >
> > https://www.postgresql.org/docs/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.

It's possible that there are other combinations that are similarly
affected, but I suspect the majority of them would just give an error
rather than silently wasting a lot of time; so I agree that this
subcommand specifically could use a small note.  While writing it I
realized we failed to note that the addition of NOT VALID changes
behavior.  So, how about like this:

      <para>
       <literal>SET NOT NULL</literal> may only be applied to a column
       provided none of the records in the table contain a
       <literal>NULL</literal> value for the column.  Ordinarily this is
       checked during the <literal>ALTER TABLE</literal> by scanning the
-      entire table; however, if a valid <literal>CHECK</literal> constraint is
-      found which proves no <literal>NULL</literal> can exist, then the
-      table scan is skipped.
+      entire table, unless <literal>NOT VALID</literal> is specified;
+      however, if a valid <literal>CHECK</literal> constraint is
+      found which proves no <literal>NULL</literal> can exist (and is not
+      dropped in the same command), then the table scan is skipped.
       If a column has an invalid not-null constraint,
       <literal>SET NOT NULL</literal> validates it.
      </para>
 
(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.


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

-- 
Álvaro Herrera               48°01'N 7°57'E  —  https://www.EnterpriseDB.com/
"Most hackers will be perfectly comfortable conceptualizing users as entropy
 sources, so let's move on."                               (Nathaniel Smith)
      https://mail.gnu.org/archive/html/monotone-devel/2007-01/msg00080.html