agora inbox for pgsql-docs@postgresql.org
help / color / mirror / Atom feedFrom: Shuyu Pan <psy2000usa@yahoo.com>
To: Álvaro Herrera <alvherre@kurilemu.de>
Cc: David G. Johnston <david.g.johnston@gmail.com>
Cc: PostgreSQL Documentation <pgsql-docs@lists.postgresql.org>
Subject: Re: further clarification: alter table alter column set not null - table scan is skipped
Date: Mon, 4 Aug 2025 20:52:57 +0000 (UTC)
Message-ID: <1128760489.1007960.1754340777935@mail.yahoo.com> (raw)
In-Reply-To: <202508041132.d5q2a4nyt22v@alvherre.pgsql>
References: <1167230960.326897.1753987280731@mail.yahoo.com>
<202508041132.d5q2a4nyt22v@alvherre.pgsql>
Thanks a lot Álvaro for preparing the clarification so quickly. Looking forward to the release.
If we mark a column to skip table scan during drop (phase 0) and later skip the table scan when setting attributes (phase 7), we will not risk data corruption if the Access Exclusive lock is never released between phase 0 and 7.
Sent from Yahoo Mail for iPhone
On Monday, August 4, 2025, 04:32, Álvaro Herrera <alvherre@kurilemu.de> wrote:
On 2025-Jul-31, Shuyu Pan wrote:
> 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.
Oh yeah, delaying the drop is much more likely to break other things. 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. 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 clarification for the doc entry is the most efficient thing.
Okay, I've pushed the change to all branches using David Johnston's
suggested wording.
Thank you all!
--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/
view thread (7+ messages)
Message-ID: <1128760489.1007960.1754340777935@mail.yahoo.com>
Permalink: ../1128760489.1007960.1754340777935@mail.yahoo.com/
Also on: postgresql.org/message-id/1128760489.1007960.1754340777935@mail.yahoo.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-docs@postgresql.org
Cc: psy2000usa@yahoo.com, alvherre@kurilemu.de, david.g.johnston@gmail.com, pgsql-docs@lists.postgresql.org
Subject: Re: further clarification: alter table alter column set not null - table scan is skipped
In-Reply-To: <1128760489.1007960.1754340777935@mail.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox