pg.ddx.io pgsql-committers@postgresql.org mailing list archivehelp / color / mirror / Atom feed
pgsql: doc: mention unusability of dropped CHECK to verify NOT NULL 7+ messages / 1 participants [nested] [flat]
* pgsql: doc: mention unusability of dropped CHECK to verify NOT NULL @ 2025-08-04 11:29 Álvaro Herrera <alvherre@kurilemu.de> 0 siblings, 0 replies; 7+ messages in thread From: Álvaro Herrera @ 2025-08-04 11:29 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org doc: mention unusability of dropped CHECK to verify NOT NULL It's possible to use a CHECK (col IS NOT NULL) constraint to skip scanning a table for nulls when adding a NOT NULL constraint on the same column. However, if the CHECK constraint is dropped on the same command that the NOT NULL is added, this fails, i.e., makes the NOT NULL addition slow. The best we can do about it at this stage is to document this so that users aren't taken by surprise. (In Postgres 18 you can directly add the NOT NULL constraint as NOT VALID instead, so there's no longer much use for the CHECK constraint, therefore no point in building mechanism to support the case better.) Reported-by: Andrew <psy2000usa@yahoo.com> Reviewed-by: David G. Johnston <david.g.johnston@gmail.com> Discussion: https://postgr.es/m/175385113607.786.16774570234342968908@wrigleys.postgresql.org Branch ------ master Details ------- https://git.postgresql.org/pg/commitdiff/126665289fa8e0f7b30165674983f079a5896d91 Modified Files -------------- doc/src/sgml/ref/alter_table.sgml | 7 ++++--- 1 file changed, 4 insertions(+), 3 deletions(-) ^ permalink raw reply [nested|flat] 7+ messages in thread
* pgsql: doc: mention unusability of dropped CHECK to verify NOT NULL @ 2025-08-04 11:29 Álvaro Herrera <alvherre@kurilemu.de> 0 siblings, 0 replies; 7+ messages in thread From: Álvaro Herrera @ 2025-08-04 11:29 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org doc: mention unusability of dropped CHECK to verify NOT NULL It's possible to use a CHECK (col IS NOT NULL) constraint to skip scanning a table for nulls when adding a NOT NULL constraint on the same column. However, if the CHECK constraint is dropped on the same command that the NOT NULL is added, this fails, i.e., makes the NOT NULL addition slow. The best we can do about it at this stage is to document this so that users aren't taken by surprise. (In Postgres 18 you can directly add the NOT NULL constraint as NOT VALID instead, so there's no longer much use for the CHECK constraint, therefore no point in building mechanism to support the case better.) Reported-by: Andrew <psy2000usa@yahoo.com> Reviewed-by: David G. Johnston <david.g.johnston@gmail.com> Discussion: https://postgr.es/m/175385113607.786.16774570234342968908@wrigleys.postgresql.org Branch ------ REL_18_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/7b1053a577494871acb5e91c63abb2bade5b8dea Modified Files -------------- doc/src/sgml/ref/alter_table.sgml | 7 ++++--- 1 file changed, 4 insertions(+), 3 deletions(-) ^ permalink raw reply [nested|flat] 7+ messages in thread
* pgsql: doc: mention unusability of dropped CHECK to verify NOT NULL @ 2025-08-04 11:29 Álvaro Herrera <alvherre@kurilemu.de> 0 siblings, 0 replies; 7+ messages in thread From: Álvaro Herrera @ 2025-08-04 11:29 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org doc: mention unusability of dropped CHECK to verify NOT NULL It's possible to use a CHECK (col IS NOT NULL) constraint to skip scanning a table for nulls when adding a NOT NULL constraint on the same column. However, if the CHECK constraint is dropped on the same command that the NOT NULL is added, this fails, i.e., makes the NOT NULL addition slow. The best we can do about it at this stage is to document this so that users aren't taken by surprise. (In Postgres 18 you can directly add the NOT NULL constraint as NOT VALID instead, so there's no longer much use for the CHECK constraint, therefore no point in building mechanism to support the case better.) Reported-by: Andrew <psy2000usa@yahoo.com> Reviewed-by: David G. Johnston <david.g.johnston@gmail.com> Discussion: https://postgr.es/m/175385113607.786.16774570234342968908@wrigleys.postgresql.org Branch ------ REL_17_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/a9b4b0665d4599f4cbc441032a01fcfdab741613 Modified Files -------------- doc/src/sgml/ref/alter_table.sgml | 7 ++++--- 1 file changed, 4 insertions(+), 3 deletions(-) ^ permalink raw reply [nested|flat] 7+ messages in thread
* pgsql: doc: mention unusability of dropped CHECK to verify NOT NULL @ 2025-08-04 11:29 Álvaro Herrera <alvherre@kurilemu.de> 0 siblings, 0 replies; 7+ messages in thread From: Álvaro Herrera @ 2025-08-04 11:29 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org doc: mention unusability of dropped CHECK to verify NOT NULL It's possible to use a CHECK (col IS NOT NULL) constraint to skip scanning a table for nulls when adding a NOT NULL constraint on the same column. However, if the CHECK constraint is dropped on the same command that the NOT NULL is added, this fails, i.e., makes the NOT NULL addition slow. The best we can do about it at this stage is to document this so that users aren't taken by surprise. (In Postgres 18 you can directly add the NOT NULL constraint as NOT VALID instead, so there's no longer much use for the CHECK constraint, therefore no point in building mechanism to support the case better.) Reported-by: Andrew <psy2000usa@yahoo.com> Reviewed-by: David G. Johnston <david.g.johnston@gmail.com> Discussion: https://postgr.es/m/175385113607.786.16774570234342968908@wrigleys.postgresql.org Branch ------ REL_16_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/479ebce12d320d23f960428b5221870c3633f1bd Modified Files -------------- doc/src/sgml/ref/alter_table.sgml | 7 ++++--- 1 file changed, 4 insertions(+), 3 deletions(-) ^ permalink raw reply [nested|flat] 7+ messages in thread
* pgsql: doc: mention unusability of dropped CHECK to verify NOT NULL @ 2025-08-04 11:29 Álvaro Herrera <alvherre@kurilemu.de> 0 siblings, 0 replies; 7+ messages in thread From: Álvaro Herrera @ 2025-08-04 11:29 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org doc: mention unusability of dropped CHECK to verify NOT NULL It's possible to use a CHECK (col IS NOT NULL) constraint to skip scanning a table for nulls when adding a NOT NULL constraint on the same column. However, if the CHECK constraint is dropped on the same command that the NOT NULL is added, this fails, i.e., makes the NOT NULL addition slow. The best we can do about it at this stage is to document this so that users aren't taken by surprise. (In Postgres 18 you can directly add the NOT NULL constraint as NOT VALID instead, so there's no longer much use for the CHECK constraint, therefore no point in building mechanism to support the case better.) Reported-by: Andrew <psy2000usa@yahoo.com> Reviewed-by: David G. Johnston <david.g.johnston@gmail.com> Discussion: https://postgr.es/m/175385113607.786.16774570234342968908@wrigleys.postgresql.org Branch ------ REL_15_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/2f600115a5a95e94f4085a5114ace0116314c4a4 Modified Files -------------- doc/src/sgml/ref/alter_table.sgml | 7 ++++--- 1 file changed, 4 insertions(+), 3 deletions(-) ^ permalink raw reply [nested|flat] 7+ messages in thread
* pgsql: doc: mention unusability of dropped CHECK to verify NOT NULL @ 2025-08-04 11:29 Álvaro Herrera <alvherre@kurilemu.de> 0 siblings, 0 replies; 7+ messages in thread From: Álvaro Herrera @ 2025-08-04 11:29 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org doc: mention unusability of dropped CHECK to verify NOT NULL It's possible to use a CHECK (col IS NOT NULL) constraint to skip scanning a table for nulls when adding a NOT NULL constraint on the same column. However, if the CHECK constraint is dropped on the same command that the NOT NULL is added, this fails, i.e., makes the NOT NULL addition slow. The best we can do about it at this stage is to document this so that users aren't taken by surprise. (In Postgres 18 you can directly add the NOT NULL constraint as NOT VALID instead, so there's no longer much use for the CHECK constraint, therefore no point in building mechanism to support the case better.) Reported-by: Andrew <psy2000usa@yahoo.com> Reviewed-by: David G. Johnston <david.g.johnston@gmail.com> Discussion: https://postgr.es/m/175385113607.786.16774570234342968908@wrigleys.postgresql.org Branch ------ REL_14_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/2ea8a37f27847ddc0828a84ab9ebea72df1eaf91 Modified Files -------------- doc/src/sgml/ref/alter_table.sgml | 7 ++++--- 1 file changed, 4 insertions(+), 3 deletions(-) ^ permalink raw reply [nested|flat] 7+ messages in thread
* pgsql: doc: mention unusability of dropped CHECK to verify NOT NULL @ 2025-08-04 11:29 Álvaro Herrera <alvherre@kurilemu.de> 0 siblings, 0 replies; 7+ messages in thread From: Álvaro Herrera @ 2025-08-04 11:29 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org doc: mention unusability of dropped CHECK to verify NOT NULL It's possible to use a CHECK (col IS NOT NULL) constraint to skip scanning a table for nulls when adding a NOT NULL constraint on the same column. However, if the CHECK constraint is dropped on the same command that the NOT NULL is added, this fails, i.e., makes the NOT NULL addition slow. The best we can do about it at this stage is to document this so that users aren't taken by surprise. (In Postgres 18 you can directly add the NOT NULL constraint as NOT VALID instead, so there's no longer much use for the CHECK constraint, therefore no point in building mechanism to support the case better.) Reported-by: Andrew <psy2000usa@yahoo.com> Reviewed-by: David G. Johnston <david.g.johnston@gmail.com> Discussion: https://postgr.es/m/175385113607.786.16774570234342968908@wrigleys.postgresql.org Branch ------ REL_13_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/d0654e53f1fc66dd7cd2f66d479f6c643d5427b4 Modified Files -------------- doc/src/sgml/ref/alter_table.sgml | 7 ++++--- 1 file changed, 4 insertions(+), 3 deletions(-) ^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2025-08-04 11:29 UTC | newest] Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2025-08-04 11:29 pgsql: doc: mention unusability of dropped CHECK to verify NOT NULL Álvaro Herrera <alvherre@kurilemu.de> 2025-08-04 11:29 pgsql: doc: mention unusability of dropped CHECK to verify NOT NULL Álvaro Herrera <alvherre@kurilemu.de> 2025-08-04 11:29 pgsql: doc: mention unusability of dropped CHECK to verify NOT NULL Álvaro Herrera <alvherre@kurilemu.de> 2025-08-04 11:29 pgsql: doc: mention unusability of dropped CHECK to verify NOT NULL Álvaro Herrera <alvherre@kurilemu.de> 2025-08-04 11:29 pgsql: doc: mention unusability of dropped CHECK to verify NOT NULL Álvaro Herrera <alvherre@kurilemu.de> 2025-08-04 11:29 pgsql: doc: mention unusability of dropped CHECK to verify NOT NULL Álvaro Herrera <alvherre@kurilemu.de> 2025-08-04 11:29 pgsql: doc: mention unusability of dropped CHECK to verify NOT NULL Álvaro Herrera <alvherre@kurilemu.de>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox