Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uhYDi-001Crs-7n for pgsql-docs@arkaria.postgresql.org; Thu, 31 Jul 2025 18:41:39 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1uhYDh-002toY-6o for pgsql-docs@arkaria.postgresql.org; Thu, 31 Jul 2025 18:41:37 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1uhYDg-002toQ-GW for pgsql-docs@lists.postgresql.org; Thu, 31 Jul 2025 18:41:36 +0000 Received: from sonic321-26.consmr.mail.bf2.yahoo.com ([74.6.133.81] helo=sonic.asd.mail.yahoo.com) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1uhYDd-0004Ee-27 for pgsql-docs@lists.postgresql.org; Thu, 31 Jul 2025 18:41:36 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1753987290; bh=/ibGE1q/fQAFhX9OJFbvBCN9yTJMYh1GMAkdD4SQFHI=; h=Date:From:To:Cc:In-Reply-To:References:Subject:From:Subject:Reply-To; b=rxbrB9Mm3261Jk2f9DmsO6MkBudPQRFTbrZmNMxV8qCwokS2PN/dui8VdEuufnoufzkm9R2PBfjU1FjpsLanwOi/cLAgBS8HJ6o3DAy2HGPT+fiUEtpOO0tpRxPgp2dc4ybUcCRYr7C2W9ngsUs1i7pC0LfNRcaCA6pi+oXwn4AQelpYZkOuvMh7R7Gx2ICDtqvw2MR1s/UENbLHZ4m8olNSy8OqZaG3PIkKSrM+SJaTKIKM5GQh3X6dAI/QiWcPbmslGL+nJCyj5bPQcaYFmA20HKmD8d8JJzCY0agBrdAc6McVJKr9Yy9PL/fuwtW9yREWeLqSZ8ee6/MN+8FU3Q== X-SONIC-DKIM-SIGN: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1753987290; bh=D3ZGuLBbxG1wKQOU/d8KFGX2F5kuGhXa5eAXEQipE0r=; h=X-Sonic-MF:Date:From:To:Subject:From:Subject; b=jSfO/oVvWnfQlWcnugwsbQfWWfmMtvfoa3pqZ9tGInnzfDwSsqLeWk0u9l0hoAcI0ZgZjCG1KuZKKHJgpkRjhuvZocEyW/r/QimiMZcpTwXZhU1WtXpHibAy/NrcvvYLv6cxo8U9tY5q5ym6R1rPQzgayDXq4XbMl64YkaA7ccpuvR8apMBFO7l8gWKG+yMdjI5GuysS/AtoS/iPSGlCQBVmLfFk4XrVp5qhE16Qqiq83DvBR8qyhVsdJWSeIvk427VUoztvHYJG20iVidAiRvl8zJSTSJRMnBndXsEkMFVzfR8khI3I0/6fVdRrUxkbx1E9u7sPg7H9+rceVQTs7A== X-YMail-OSG: tkBVKOkVM1ls55gSlYMGDKQHDMlfq8MkrmOsT8lkea1bLM2y5b6j5VRmes4eOmW gIq.ZpPSX68r3VHLQDMrZ3SMyK6lvKt9BJOwX8Gg5Jg0UyooWIKRODIcVIthAPNj7vp1JwPnIBn6 XqDLYTL4k9_VDDDMAEFDq4gcOAQaRbldzet3TgH3F5ZC0ZadSRaO67Q85_nujHkR9YD6VAn3.4FU wKaMctL_NKPwJSWkXz3XFC9Gc7B.6f0jZKVEZoPrYSRxbKQG6x8MOujJ1d7nPWhlSCge.2iiJMfS m8KsP0ZqFLiVBhpHD56MKCMOALTQIhs3e5UZdOUYOJ5pd1v4znpV08R8njgR5OuD_1Nr9rMY3uq4 d59mAdhoJYTIsfCh8SdJpCBCuvdISI2xI3m3iaNyc.iX_23CGBVdPbpuGrMjvmmE8wrOyls7zs_9 rgPCUCxVvp3VVyzVkfP61i91QvCjv.sifeXJ8vYW3.7ZytOMjZdzwLqbPN4clwyNiekuDZtFwSch jx2VC_5GOJoI3aJ9w1t094a2_RTbyru92BmPqFRc0jYNoXeCM2QRsYv8G9Fi.jrpC69ZJL.p30VK vWjCqQV_wkMfF5rPwKADXTFkvx6gEUVjpPt.hFbRgu7pMu7oG6SJU7uXZ5FcoZ8RgnZ33Djulj.a zIVyy6G4DWPtzbN0cwN5cDpMXGXc6ZzEzTbSbAEL.jZDlB8CEyHIk5yZyI_XI3c0LVlhr_hEfmFS bTo5RZ6DNPr633Pp_ZBFZTQ8hmtUt.5yOa9nxjKAbdBF0XCpsa5UZKsOKHCUSmfxHViDcgH3.Hya 90diYEkh2PY2DILxkXralKibbLvsxI5HFyZkYUQBSqkYJDrV2rU.Xa_Xhfug944aRbjGZc19O30i aDuE2M.szzDiZyi88SfX4mBpAOB.Zr_mJPfNNwBjRPmkySSycY3aozkD_pB5pcu22ihCK0FHEHX6 zsW7B34iS0QYUjtWsNy_09Lqen8mH5AO2c3jRgYsML07u4atQlBEXad90QQMkO6_9mkj.reXtkVp 8dvtdGVgK3RxuZ8u3zWxvZs_sXnQaEBrzeYNb5M7Q5ddCNHZYN2sQohUQBQ6Qhoox2o9Us2QrBwk 4Dd9CAGOQ1l0AWy5W_oViCDGQQ5mZlB.WBGUVQdXvFeo_FPS4PMqBQ5qLhlA37g6njWnygcOSzpT MZLdMHKPgSOv_wfFQw836bbFEanMST500CkHQIIcFO8NSFDFrSGUDs9DGMBvegZlkBJM3ek4Ea.E dLJ_6_c8BOCMrJQKcAnJ_MOQcC3fQnSNFArXAeXDUPBkavaV20pgrlJf6uO54JZtR3qhY5VhBaHT kiQVaWf4kGbhzpirUQA..hUq84FSgo_eiB42H0mO7l1LB3566.uWktCLb4DsI4JM_4O4DPTHUjrJ rdXcJZyolpGZYHK7l7eFkaEUkGedgXu4NV8XfmQL9gor.A4bcoRV5a_.m39AfgQrlpg4Y.FiNI6R ODA5bp6ZLwjMptrw6PYtzo9MPztdRGEQkRiRTKvCv1E7cgoj2pVzig8.Yalerh1B8jKe5aiWxe30 uNaHy9VeSYzwGBtvkH.zpv9hvsibyHc740JLGCz3XO8aGXi6y8BX2ahJ_jfNU5uOm0srnPOakYJd ADbEUPK_OqJ.9gUEQxEoBeoR1D.HSERtBaaJNKqJFuiBpfa7VKDDlq21cAi8g6xwxADpHsotSJ8N 1m5Ct7Qpv0qTUlIJC7FySTKQds0aAztCmjw7GlT.3rTDj2IDn9a5bDRr6I.UCRN_rRHmufKiqDPE zX3u_E_oNZz4sdjzjizWJOIzk2dA2zBVcEt0FcsmLzRFxzG0yhfDpsf0O1ylr5roFLlUk4pdczM4 ZKhUmyQAg66LhAhtuHfJlzSWGLF9LrKZy5k3yuFpgA39Wkrz5FEDFFNMoVbU38oTfKSt2wboDzHR TCoT97TjCj4yJt7w1iCxh_O_GEIrCumZhAdrsjBlJmBbVPKTtjy5uP4GFLs4in99aP9p.ws5hAfk XSKCqtxx2fKrZh8SGePeeeysdf8.0vkvhbkes_HhutN4S1Tu7TABSW7k1CHycU9Y.e500pyqBTUr xdRBlGQhryavq9_FFHpoEoqHiYIcEaNhSl3.emn.PSp0Im31xEEBhAU8uYPZS.tKtpujLK.gQZ9r B9HmOUdosnGWTwOpU7Gl2sgr0IpO3.yfJl_EfAeAB_qLHZXhot1peOomez4r9xmYe6DbwNKaUi_d guX1sX4iwbNvAh1Pej4BYnpZFbPR.FTIcTeBZ5OT_jy_cgyJjbcVpHMWuZKeh3o7B_0wqpg-- X-Sonic-MF: X-Sonic-ID: c36e3d34-0fa3-4905-b3fb-37a7b381693d Received: from sonic.gate.mail.ne1.yahoo.com by sonic321.consmr.mail.bf2.yahoo.com with HTTP; Thu, 31 Jul 2025 18:41:30 +0000 Date: Thu, 31 Jul 2025 18:41:20 +0000 (UTC) From: Shuyu Pan To: "David G. Johnston" , =?UTF-8?Q?=C3=81lvaro_Herrera?= Cc: PostgreSQL Documentation Message-ID: <1167230960.326897.1753987280731@mail.yahoo.com> In-Reply-To: References: <202507311530.ls53ug7urrgx@alvherre.pgsql> Subject: Re: further clarification: alter table alter column set not null - table scan is skipped MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_326896_2126923717.1753987280729" X-Mailer: WebService/1.1.24260 YahooMailIosMobile Content-Length: 8857 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------=_Part_326896_2126923717.1753987280729 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable I like your versions that emphasize: don=E2=80=99t drop the constraint in t= he same alter table set no null=C2=A0command. Similar to=C2=A0David=E2=80=99s point, I spent some time trying to figure o= ut a simple refactoring=C2=A0to carry the optimization all the way to the e= nd but it might require executing =E2=80=9Cset not null=E2=80=9D sooner whi= ch has a big impact. Another option is only implement a special treatment f= or this specific=C2=A0use case but it is a code smell to me. I believe a sm= all=C2=A0clarification for the doc entry=C2=A0is the most efficient thing. Sent from Yahoo Mail for iPhone On Thursday, July 31, 2025, 09:01, David G. Johnston wrote: On Thursday, July 31, 2025, =C3=81lvaro Herrera wrot= e: On 2025-Jul-30, David G. Johnston wrote: > On Wed, Jul 30, 2025, 13:55 PG Doc comments form > wrote: > > The "table scan is skipped" optimization can use some clarification > > > > https://www.postgresql.org/doc s/current/sql-altertable.html# SQL-ALTER= TABLE-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=E2=80=99t want an error.=C2=A0 At the start of the command the con= straint existed and its presence then would be enough.=C2=A0 It is immateri= al that it went away during the command.=C2=A0 But it=E2=80=99s definitely = not something that seems worth spending a non-trivial amount of effort on.= =C2=A0 (This is correct for 18; for 17 and earlier, the mention of NOT VALID needs to be removed.)=C2=A0 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 ear= lier what the article points out regarding locks. Not sure if this reads better: =C2=A0 =C2=A0if a valid CHECK constraint is =C2=A0 =C2=A0found (and is not dropped in the same command) which =C2=A0 =C2=A0proves no NULL 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. ------=_Part_326896_2126923717.1753987280729 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
I like your versions that emphasize: don=E2=80=99t drop the constraint = in the same alter table set no null command.

Simila= r to David=E2=80=99s point, I spent some time trying to figure out a s= imple refactoring to carry the optimization all the way to the end but= it might require executing =E2=80=9Cset not null=E2=80=9D sooner which has= a big impact. Another option is only implement a special treatment for thi= s specific use case but it is a code smell to me. I believe a small&nb= sp;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.g.johnston@gmail.com> wrote:

On Thursday, July 31, = 2025, =C3=81lvaro Herrera <alvherre@kurilemu.de> wrote:
On 2025-Jul-30, Davi= d G. Johnston wrote:

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

> > The "table scan is skipped" optimization can use some clarificati= on
> >
> > https://www.postgresql.org/doc s/cu= rrent/sql-altertable.html# SQL-ALTERTABLE-DESC-SET-DROP- NOT-NULL
> > My proposal is "then the table scan is skipped if the alter state= ment
> > 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=E2=80=99t want an error.&n= bsp; At the start of the command the constraint existed and its presence th= en would be enough.  It is immaterial that it went away during the com= mand.  But it=E2=80=99s definitely not something that seems worth spen= ding 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<= br clear=3D"none">    found (and is not dropped in the same command) which
   proves no <literal>NULL</literal> can exist, then<= /div>


If a valid check constraint= exists (and is not dropped in the same command) which proves the absence o= f NULLs, then

I do agree the parent= hetical should appear closer to the word constraint.

David J.

------=_Part_326896_2126923717.1753987280729--