Catalog NOT NULL constraints
We now create pg_constaint rows for NOT NULL constraints with
contype='n'.
We propagate these constraints during operations such as adding
inheritance relationships, creating and attaching partitions, creating
tables LIKE other tables. We mostly follow the well-known rules of
conislocal and coninhcount that we have for CHECK constraints, with some
adaptations; for example, as opposed to CHECK constraints, we don't
match NOT NULL ones by name when descending a hierarchy to alter it;
instead we match by column number. This means we don't require the
constraint names to be identical across a hierarchy.
For now, we omit them from system catalogs. Maybe this is worth
reconsidering. We don't support NOT VALID nor DEFERRABLE clauses
either; these can be added as separate features later (this patch is
already large and complicated enough.)
This has been very long in the making. The first patch was written by
Bernd Helmle in 2010 to add a new pg_constraint.contype value ('n'),
which I (Álvaro) then hijacked in 2011 and 2012, until that one was
killed by the realization that we ought to use contype='c' instead:
manufactured CHECK constraints. However, later SQL standard
development, as well as nonobvious emergent properties of that design
(mostly, failure to distinguish them from "normal" CHECK constraints as
well as the performance implication of having to test the CHECK
expression) led us to reconsider this choice, so now the current
implementation uses contype='n' again.
In 2016 Vitaly Burovoy also worked on this feature[1] but found no
consensus for his proposed approach, which was claimed to be closer to
the letter of the standard, requiring additional pg_attribute columns to
track the OID of the NOT NULL constraint for that column.
[1] https://postgr.es/m/CAKOSWNkN6HSyatuys8xZxzRCR-KL1OkHS5-b9qd9bf1Rad3PLA@mail.gmail.com
Author: Álvaro Herrera <alvherre@alvh.no-ip.org>
Author: Bernd Helmle <mailings@oopsware.de>
Reviewed-by: Justin Pryzby <pryzby@telsasoft.com>
Reviewed-by: Peter Eisentraut <peter.eisentraut@enterprisedb.com>
Discussion: https://postgr.es/m/CACA0E642A0267EDA387AF2B%40%5B172.26.14.62%5D
Discussion: https://postgr.es/m/AANLkTinLXMOEMz+0J29tf1POokKi4XDkWJ6-DDR9BKgU@mail.gmail.com
Discussion: https://postgr.es/m/20110707213401.GA27098@alvh.no-ip.org
Discussion: https://postgr.es/m/1343682669-sup-2532@alvh.no-ip.org
Discussion: https://postgr.es/m/CAKOSWNkN6HSyatuys8xZxzRCR-KL1OkHS5-b9qd9bf1Rad3PLA@mail.gmail.com
Discussion: https://postgr.es/m/20220817181249.q7qvj3okywctra3c@alvherre.pgsql
Branch
------
master
Details
-------
https://git.postgresql.org/pg/commitdiff/e056c557aef4006c3dfbf8a4b94b7ae88eb9fd67
Modified Files
--------------
doc/src/sgml/catalogs.sgml | 1 +
doc/src/sgml/ref/alter_table.sgml | 14 +-
doc/src/sgml/ref/create_table.sgml | 8 +-
src/backend/catalog/heap.c | 491 ++++++--
src/backend/catalog/pg_constraint.c | 97 ++
src/backend/commands/tablecmds.c | 1326 +++++++++++++++-----
src/backend/nodes/outfuncs.c | 4 +
src/backend/nodes/readfuncs.c | 8 +-
src/backend/optimizer/util/plancat.c | 2 +
src/backend/parser/gram.y | 13 +
src/backend/parser/parse_utilcmd.c | 206 ++-
src/backend/utils/adt/ruleutils.c | 14 +
src/bin/pg_dump/common.c | 15 +-
src/bin/pg_dump/pg_backup_archiver.c | 2 +
src/bin/pg_dump/pg_dump.c | 209 ++-
src/bin/pg_dump/pg_dump.h | 2 +-
src/bin/pg_dump/t/002_pg_dump.pl | 6 +-
src/include/catalog/catversion.h | 2 +-
src/include/catalog/heap.h | 7 +-
src/include/catalog/pg_constraint.h | 11 +-
src/include/commands/tablecmds.h | 2 +
src/include/nodes/parsenodes.h | 14 +-
.../test_ddl_deparse/expected/alter_table.out | 18 +-
.../test_ddl_deparse/expected/create_table.out | 25 +-
.../modules/test_ddl_deparse/test_ddl_deparse.c | 4 +
src/test/regress/expected/alter_table.out | 50 +-
src/test/regress/expected/cluster.out | 7 +-
src/test/regress/expected/constraints.out | 114 ++
src/test/regress/expected/create_table.out | 27 +-
src/test/regress/expected/event_trigger.out | 2 +
src/test/regress/expected/foreign_data.out | 11 +-
src/test/regress/expected/foreign_key.out | 16 +-
src/test/regress/expected/indexing.out | 41 +-
src/test/regress/expected/inherit.out | 405 ++++++
src/test/regress/expected/replica_identity.out | 13 +
src/test/regress/parallel_schedule | 3 +-
src/test/regress/sql/alter_table.sql | 26 +-
src/test/regress/sql/constraints.sql | 43 +
src/test/regress/sql/create_table.sql | 6 +-
src/test/regress/sql/indexing.sql | 8 +-
src/test/regress/sql/inherit.sql | 208 +++
src/test/regress/sql/replica_identity.sql | 12 +
42 files changed, 2868 insertions(+), 625 deletions(-)