pgsql: Allow NOT NULL constraints to be added as NOT VALID - Mailing list pgsql-committers

Allow NOT NULL constraints to be added as NOT VALID

This allows them to be added without scanning the table, and validating
them afterwards without holding access exclusive lock on the table after
any violating rows have been deleted or fixed.

Doing ALTER TABLE ... SET NOT NULL for a column that has an invalid
not-null constraint validates that constraint.  ALTER TABLE .. VALIDATE
CONSTRAINT is also supported.  There are various checks on whether an
invalid constraint is allowed in a child table when the parent table has
a valid constraint; this should match what we do for enforced/not
enforced constraints.

pg_attribute.attnotnull is now only an indicator for whether a not-null
constraint exists for the column; whether it's valid or invalid must be
queried in pg_constraint.  Applications can continue to query
pg_attribute.attnotnull as before, but now it's possible that NULL rows
are present in the column even when that's set to true.

For backend internal purposes, we cache the nullability status in
CompactAttribute->attnullability that each tuple descriptor carries
(replacing CompactAttribute.attnotnull, which was a mirror of
Form_pg_attribute.attnotnull).  During the initial tuple descriptor
creation, based on the pg_attribute scan, we set this to UNRESTRICTED if
pg_attribute.attnotnull is false, or to UNKNOWN if it's true; then we
update the latter to VALID or INVALID depending on the pg_constraint
scan.  This flag is also copied when tupledescs are copied.

Comparing tuple descs for equality must also compare the
CompactAttribute.attnullability flag and return false in case of a
mismatch.

pg_dump deals with these constraints by storing the OIDs of invalid
not-null constraints in a separate array, and running a query to obtain
their properties.  The regular table creation SQL omits them entirely.
They are then dealt with in the same way as "separate" CHECK
constraints, and dumped after the data has been loaded.  Because no
additional pg_dump infrastructure was required, we don't bump its
version number.

I decided not to bump catversion either, because the old catalog state
works perfectly in the new world.  (Trying to run with new catalog state
and the old server version would likely run into issues, however.)

System catalogs do not support invalid not-null constraints (because
commit 14e87ffa5c54 didn't allow them to have pg_constraint rows
anyway.)

Author: Rushabh Lathia <rushabh.lathia@gmail.com>
Author: Jian He <jian.universality@gmail.com>
Reviewed-by: Álvaro Herrera <alvherre@alvh.no-ip.org>
Tested-by: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
Discussion: https://postgr.es/m/CAGPqQf0KitkNack4F5CFkFi-9Dqvp29Ro=EpcWt=4_hs-Rt+bQ@mail.gmail.com

Branch
------
master

Details
-------
https://git.postgresql.org/pg/commitdiff/a379061a22a8fdf421e1a457cc6af8503def6252

Modified Files
--------------
doc/src/sgml/catalogs.sgml                |   2 +-
doc/src/sgml/ref/alter_table.sgml         |   8 +-
src/backend/access/common/tupdesc.c       |  38 ++++-
src/backend/catalog/heap.c                |   9 +-
src/backend/catalog/pg_constraint.c       |  46 +++--
src/backend/commands/tablecmds.c          | 271 +++++++++++++++++++++++++-----
src/backend/jit/llvm/llvmjit_deform.c     |  10 +-
src/backend/optimizer/util/plancat.c      |  16 +-
src/backend/parser/gram.y                 |   5 +-
src/backend/utils/cache/relcache.c        |  73 +++++++-
src/bin/pg_dump/pg_dump.c                 | 190 +++++++++++++++++++--
src/bin/pg_dump/pg_dump.h                 |   2 +
src/bin/pg_dump/t/002_pg_dump.pl          |  21 ++-
src/bin/psql/describe.c                   |   9 +-
src/include/access/tupdesc.h              |  10 +-
src/include/catalog/pg_attribute.h        |   4 +-
src/include/catalog/pg_constraint.h       |   2 +-
src/test/regress/expected/alter_table.out |  69 ++++++++
src/test/regress/expected/constraints.out | 252 +++++++++++++++++++++++++++
src/test/regress/sql/alter_table.sql      |  14 ++
src/test/regress/sql/constraints.sql      | 169 +++++++++++++++++++
21 files changed, 1109 insertions(+), 111 deletions(-)