Date: Mon, 4 Aug 2025 20:52:57 +0000 (UTC)
From: Shuyu Pan <psy2000usa@yahoo.com>
To: =?UTF-8?Q?=C3=81lvaro_Herrera?= <alvherre@kurilemu.de>
Cc: "David G. Johnston" <david.g.johnston@gmail.com>,
	PostgreSQL Documentation <pgsql-docs@lists.postgresql.org>
Message-ID: <1128760489.1007960.1754340777935@mail.yahoo.com>
In-Reply-To: <202508041132.d5q2a4nyt22v@alvherre.pgsql>
References: <1167230960.326897.1753987280731@mail.yahoo.com>
 <202508041132.d5q2a4nyt22v@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_1007959_2030446360.1754340777934"
Content-Length: 5693
Archived-At: 
 <https://www.postgresql.org/message-id/1128760489.1007960.1754340777935%40mail.yahoo.com>
Precedence: bulk

------=_Part_1007959_2030446360.1754340777934
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

Thanks a lot=C2=A0=C3=81lvaro for preparing=C2=A0the clarification so quick=
ly. Looking forward to the release.
If=C2=A0we mark a=C2=A0column to skip table=C2=A0scan during drop (phase 0)=
=C2=A0and later=C2=A0skip the=C2=A0table=C2=A0scan when=C2=A0setting=C2=A0a=
ttributes (phase 7), we will=C2=A0not risk data corruption if the Access Ex=
clusive lock is never released between phase 0 and 7.


Sent from Yahoo Mail for iPhone


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

On 2025-Jul-31, Shuyu Pan wrote:

> I like your versions that emphasize: don=E2=80=99t drop the constraint in=
 the
> same alter table set no null=C2=A0command.=C2=A0 Similar to=C2=A0David=E2=
=80=99s point, I
> spent some time trying to figure out a simple refactoring=C2=A0to carry t=
he
> 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 opt=
ion is only
> implement a special treatment for this specific=C2=A0use case but it is a
> code smell to me.

Oh yeah, delaying the drop is much more likely to break other things.=C2=A0=
 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.=C2=A0 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=C2=A0clarification for the doc entry=C2=A0is the most e=
fficient thing.

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

Thank you all!

--=20
=C3=81lvaro Herrera=C2=A0 =C2=A0 =C2=A0 =C2=A0 Breisgau, Deutschland=C2=A0 =
=E2=80=94=C2=A0 https://www.EnterpriseDB.com/


------=_Part_1007959_2030446360.1754340777934
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

<html xmlns=3D"http://www.w3.org/1999/xhtml" xmlns:v=3D"urn:schemas-microso=
ft-com:vml" xmlns:o=3D"urn:schemas-microsoft-com:office:office"><head><!--[=
if gte mso 9]><xml><o:OfficeDocumentSettings><o:AllowPNG/><o:PixelsPerInch>=
96</o:PixelsPerInch></o:OfficeDocumentSettings></xml><![endif]--></head><bo=
dy>
<!--[if gte mso 9]><xml><o:OfficeDocumentSettings><o:AllowPNG/><o:PixelsPer=
Inch>96</o:PixelsPerInch></o:OfficeDocumentSettings></xml><![endif]-->
Thanks a lot&nbsp;=C3=81lvaro for preparing&nbsp;the clarification so quick=
ly. Looking forward to the release.<div><br><div>If&nbsp;we mark a&nbsp;col=
umn to skip table&nbsp;scan during drop (phase 0)&nbsp;and later&nbsp;skip =
the&nbsp;table&nbsp;scan when&nbsp;setting&nbsp;attributes (phase 7), we wi=
ll&nbsp;not risk data corruption if the Access Exclusive lock is never rele=
ased between phase 0 and 7.<br><br><br><div class=3D"yahoo-signature" style=
=3D""><a style=3D"font-size:12px" href=3D"https://mail.onelink.me/107872968=
?pid=3Dnativeplacement&amp;c=3DGlobal_Acquisition_YMktg_315_Internal_EmailS=
ignature&amp;af_sub1=3DAcquisition&amp;af_sub2=3DGlobal_YMktg&amp;af_sub3=
=3D&amp;af_sub4=3D100000604&amp;af_sub5=3DEmailSignature__Static_&amp;af_io=
s_store_cpp=3D9d3a686e-218d-4849-8298-b480188dc8ac&amp;af_android_url=3Dhtt=
ps%3A%2F%2Fplay.google.com%2Fstore%2Fapps%2Fdetails%3Fid%3Dcom.yahoo.mobile=
.client.android.mail%26listing%3Demail_signature">Sent from Yahoo Mail for =
iPhone</a><br></div><br><p class=3D"yahoo-quoted-begin" style=3D"font-size:=
 15px; color: rgb(123, 106, 246); padding-top: 15px; margin-top: 0px;">On M=
onday, August 4, 2025, 04:32, =C3=81lvaro Herrera &lt;alvherre@kurilemu.de&=
gt; wrote:</p><blockquote class=3D"iosymail" style=3D"">On 2025-Jul-31, Shu=
yu Pan wrote:<br><br>&gt; I like your versions that emphasize: don=E2=80=99=
t drop the constraint in the<br>&gt; same alter table set no null&nbsp;comm=
and.&nbsp; Similar to&nbsp;David=E2=80=99s point, I<br>&gt; spent some time=
 trying to figure out a simple refactoring&nbsp;to carry the<br>&gt; optimi=
zation all the way to the end but it might require executing<br>&gt; =E2=80=
=9Cset not null=E2=80=9D sooner which has a big impact. Another option is o=
nly<br>&gt; implement a special treatment for this specific&nbsp;use case b=
ut it is a<br>&gt; code smell to me.<br><br>Oh yeah, delaying the drop is m=
uch more likely to break other things.&nbsp; I<br>was more thinking along t=
he lines of maintaining a list of columns that<br>are known non-null at the=
 start of the command (a bitmapset actually).<br>This could be computed in =
ALTER TABLE phase 1, and used later to<br>determine that no scans are neede=
d.&nbsp; But this is a lot of mechanism<br>which is useless 99% of the time=
, and moreso now that you can directly<br>add the NOT NULL constraints as N=
OT VALID to start with, which saves<br>having to mess with a separate CHECK=
 constraint.<br><br>&gt; I believe a small&nbsp;clarification for the doc e=
ntry&nbsp;is the most efficient thing.<br><br>Okay, I've pushed the change =
to all branches using David Johnston's<br>suggested wording.<br><br>Thank y=
ou all!<br><br>-- <br>=C3=81lvaro Herrera&nbsp; &nbsp; &nbsp; &nbsp; Breisg=
au, Deutschland&nbsp; =E2=80=94&nbsp; <a href=3D"https://www.EnterpriseDB.c=
om/" target=3D"_blank">https://www.EnterpriseDB.com/</a><br><blockquote></b=
lockquote></blockquote></div>
</div>
</body></html>
------=_Part_1007959_2030446360.1754340777934--