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

[Álvaro Herrera]

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/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:

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

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

[David G. Johnston]

On Thursday, July 31, 2025, Álvaro Herrera  wrote:

> 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/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.


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 CHECK constraint is
>found (and is not dropped in the same command) which
>proves 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.

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

[Shuyu Pan]

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 
 wrote:

On Thursday, July 31, 2025, Álvaro Herrera  wrote:

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-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 CHECK constraint is
   found (and is not dropped in the same command) which
   proves 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.

Re: Lets prohibit predicting the future in the documentation.

[Peter Smith]

On Thu, Jul 31, 2025 at 8:05 PM Magnus Hagander  wrote:
>
>
>
> On Thu, Jul 31, 2025 at 5:03 AM David Rowley  wrote:
>>
>> On Thu, 31 Jul 2025 at 14:17, David G. Johnston
>>  wrote:
>> >
>> > Came across this again today...we added, way back in v11:
>> >
>> > "This limitation will likely be removed in a future version of 
>> > PostgreSQL."
>> >
>> > https://www.postgresql.org/docs/18/sql-createstatistics.html
>>
>> This sort of thing doesn't particularly upset me. I don't believe we
>> should hide the fact that certain features might need more work. If it
>> inspires someone to work on making improvements, wouldn't it be
>> worthwhile keeping these? A huge amount of stuff gets done around here
>> because people find some inspiration to make things better. I don't
>> believe all those people need to experience the problems first-hand to
>> be able to fix them. Plenty of people arrive here just looking to get
>> involved and make a difference. I presume that something like this
>> being mentioned in the docs likely has a much better "we actually want
>> this feature" ratio than the TODO list does. I also imagine it's more
>> likely to inspire users of PostgreSQL to get involved in developing
>> than the TODO list is.
>>
>> -1 from me.
>
>
> I can agree that the "will likely be removed" is a bad wording, and clearly 
> it was wrong :) But  something like "could be removed" would convey the 
> important message that it is not a limitation of the concept itself, it's 
> just something that hasn't been done yet -- and would perhaps encourage 
> exactly the sort of thing yuo'r suggesting. Where as "will likely be removed" 
> almost sounds like someone is already working on it.
>

FYI, there are quite a lot like this. Mostly the docs are worded using
"may/might/can" rather than "will" be changed.

Some examples (e.g. search .sgml for "future")

... but this may change in future releases.

... These will probably be fixed in future releases:

... An area for future development is to ...

... restriction that may be lifted in a future version ...

... this might be replaced by a different mechanism in the future.

... This may be changed in a future release ...

... might change in a future release.

... This information describes possible future behavior.

... some of these restrictions might be loosened in a future release.

... (this behavior might change in the future).

... These can and probably will be fixed in future releases:

... These deficiencies may be remedied in future versions ...

... It is hoped that a future version of this module will ...

... This restriction on ... may be lifted in a future version

... These might be addressed in future releases.

... This may be expanded in the future.

... might be changed in a future release.

... This is an implementation restriction that might be fixed in
future releases.

==
Kind Regards,
Peter Smith.
Fujitsu Australia

Re: Lets prohibit predicting the future in the documentation.

[David G. Johnston]

On Thu, Jul 31, 2025 at 4:24 PM Peter Smith  wrote:

> On Thu, Jul 31, 2025 at 8:05 PM Magnus Hagander 
> wrote:
> >
> >
> >
> > On Thu, Jul 31, 2025 at 5:03 AM David Rowley 
> wrote:
> >>
> >> On Thu, 31 Jul 2025 at 14:17, David G. Johnston
> >>  wrote:
> >> >
> >> > Came across this again today...we added, way back in v11:
> >> >
> >> > "This limitation will likely be removed in a future version of
> PostgreSQL."
> >> >
> >> > https://www.postgresql.org/docs/18/sql-createstatistics.html
> >>
> >> This sort of thing doesn't particularly upset me. I don't believe we
> >> should hide the fact that certain features might need more work. If it
> >> inspires someone to work on making improvements, wouldn't it be
> >> worthwhile keeping these? A huge amount of stuff gets done around here
> >> because people find some inspiration to make things better. I don't
> >> believe all those people need to experience the problems first-hand to
> >> be able to fix them. Plenty of people arrive here just looking to get
> >> involved and make a difference. I presume that something like this
> >> being mentioned in the docs likely has a much better "we actually want
> >> this feature" ratio than the TODO list does. I also imagine it's more
> >> likely to inspire users of PostgreSQL to get involved in developing
> >> than the TODO list is.
> >>
> >> -1 from me.
> >
> >
> > I can agree that the "will likely be removed" is a bad wording, and
> clearly it was wrong :) But  something like "could be removed" would convey
> the important message that it is not a limitation of the concept itself,
> it's just something that hasn't been done yet -- and would perhaps
> encourage exactly the sort of thing yuo'r suggesting. Where as "will likely
> be removed" almost sounds like someone is already working on it.
> >
>
> FYI, there are quite a lot like this. Mostly the docs are worded using
> "may/might/can" rather than "will" be changed.
>
>
Yeah, I haven't been able to dig into the source yet on this topic but
basically that says to me that lots of people, with good intentions, want
to couch bad news (limitations) with something positive (hope).  But in the
documentation it ends up almost inevitably turning into false hope.

There is no good way to extract all these "TODO" items from the HTML docs
and seems like a non-optimal method for transferring knowledge to potential
developers who may choose to try and remove such limitations.

David J.

I want a postgres function to generate ctid where condition for table migration based ctid

[yanliang lei]

for instance,
select * from  tabxxx where  ctid >= '(0,0)' AND ctid <= '(27,0)'
select * from  tabxxx where  ctid > '(27,0)' AND ctid <= '(57,0)'
select * from  tabxxx where  ctid > '(57,0)' AND ctid <= '(69,0)'

and so on


I want to generate a table ‘s  ctid condition automatically

Re: Lets prohibit predicting the future in the documentation.

[Magnus Hagander]

On Thu, Jul 31, 2025 at 5:03 AM David Rowley  wrote:

> On Thu, 31 Jul 2025 at 14:17, David G. Johnston
>  wrote:
> >
> > Came across this again today...we added, way back in v11:
> >
> > "This limitation will likely be removed in a future version of
> PostgreSQL."
> >
> > https://www.postgresql.org/docs/18/sql-createstatistics.html
>
> This sort of thing doesn't particularly upset me. I don't believe we
> should hide the fact that certain features might need more work. If it
> inspires someone to work on making improvements, wouldn't it be
> worthwhile keeping these? A huge amount of stuff gets done around here
> because people find some inspiration to make things better. I don't
> believe all those people need to experience the problems first-hand to
> be able to fix them. Plenty of people arrive here just looking to get
> involved and make a difference. I presume that something like this
> being mentioned in the docs likely has a much better "we actually want
> this feature" ratio than the TODO list does. I also imagine it's more
> likely to inspire users of PostgreSQL to get involved in developing
> than the TODO list is.
>
> -1 from me.
>

I can agree that the "will likely be removed" is a bad wording, and clearly
it was wrong :) But  something like "could be removed" would convey the
important message that it is not a limitation of the concept itself, it's
just something that hasn't been done yet -- and would perhaps encourage
exactly the sort of thing yuo'r suggesting. Where as "will likely be
removed" almost sounds like someone is already working on it.

-- 
 Magnus Hagander
 Me: https://www.hagander.net/ 
 Work: https://www.redpill-linpro.com/