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

3 files changed, 35 insertions, 12 deletions

diff --git a/src/test/modules/test_ddl_deparse/expected/alter_table.out b/src/test/modules/test_ddl_deparse/expected/alter_table.out
index 87a1ab7aabc..4d8e3abfed9 100644
--- a/src/test/modules/test_ddl_deparse/expected/alter_table.out
+++ b/src/test/modules/test_ddl_deparse/expected/alter_table.out
@@ -28,6 +28,7 @@ ALTER TABLE parent ADD COLUMN b serial;
 NOTICE:  DDL test: type simple, tag CREATE SEQUENCE
 NOTICE:  DDL test: type alter table, tag ALTER TABLE
 NOTICE:    subcommand: type ADD COLUMN (and recurse) desc column b of table parent
+NOTICE:    subcommand: type ADD CONSTRAINT (and recurse) desc constraint parent_b_not_null on table parent
 NOTICE:  DDL test: type simple, tag ALTER SEQUENCE
 ALTER TABLE parent RENAME COLUMN b TO c;
 NOTICE:  DDL test: type simple, tag ALTER TABLE
@@ -57,24 +58,18 @@ NOTICE:    subcommand: type DETACH PARTITION desc table part2
 DROP TABLE part2;
 ALTER TABLE part ADD PRIMARY KEY (a);
 NOTICE:  DDL test: type alter table, tag ALTER TABLE
-NOTICE:    subcommand: type SET NOT NULL desc column a of table part
-NOTICE:    subcommand: type SET NOT NULL desc column a of table part1
+NOTICE:    subcommand: type SET ATTNOTNULL desc <NULL>
+NOTICE:    subcommand: type SET ATTNOTNULL desc <NULL>
 NOTICE:    subcommand: type ADD INDEX desc index part_pkey
 ALTER TABLE parent ALTER COLUMN a SET NOT NULL;
 NOTICE:  DDL test: type alter table, tag ALTER TABLE
-NOTICE:    subcommand: type SET NOT NULL desc column a of table parent
-NOTICE:    subcommand: type SET NOT NULL desc column a of table child
-NOTICE:    subcommand: type SET NOT NULL desc column a of table grandchild
+NOTICE:    subcommand: type SET NOT NULL (and recurse) desc constraint parent_a_not_null on table parent
 ALTER TABLE parent ALTER COLUMN a DROP NOT NULL;
 NOTICE:  DDL test: type alter table, tag ALTER TABLE
-NOTICE:    subcommand: type DROP NOT NULL desc column a of table parent
-NOTICE:    subcommand: type DROP NOT NULL desc column a of table child
-NOTICE:    subcommand: type DROP NOT NULL desc column a of table grandchild
+NOTICE:    subcommand: type DROP NOT NULL (and recurse) desc column a of table parent
 ALTER TABLE parent ALTER COLUMN a SET NOT NULL;
 NOTICE:  DDL test: type alter table, tag ALTER TABLE
-NOTICE:    subcommand: type SET NOT NULL desc column a of table parent
-NOTICE:    subcommand: type SET NOT NULL desc column a of table child
-NOTICE:    subcommand: type SET NOT NULL desc column a of table grandchild
+NOTICE:    subcommand: type SET NOT NULL (and recurse) desc constraint parent_a_not_null on table parent
 ALTER TABLE parent ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY;
 NOTICE:  DDL test: type simple, tag CREATE SEQUENCE
 NOTICE:  DDL test: type simple, tag ALTER SEQUENCE
@@ -116,6 +111,7 @@ NOTICE:  DDL test: type alter table, tag ALTER TABLE
 NOTICE:    subcommand: type ALTER COLUMN SET TYPE desc column c of table parent
 NOTICE:    subcommand: type ALTER COLUMN SET TYPE desc column c of table child
 NOTICE:    subcommand: type ALTER COLUMN SET TYPE desc column c of table grandchild
+NOTICE:    subcommand: type (re) ADD CONSTRAINT desc constraint parent_b_not_null on table parent
 NOTICE:    subcommand: type (re) ADD STATS desc statistics object parent_stat
 ALTER TABLE parent ALTER COLUMN c SET DEFAULT 0;
 NOTICE:  DDL test: type alter table, tag ALTER TABLE
diff --git a/src/test/modules/test_ddl_deparse/expected/create_table.out b/src/test/modules/test_ddl_deparse/expected/create_table.out
index 2178ce83e9d..dc9175bf77d 100644
--- a/src/test/modules/test_ddl_deparse/expected/create_table.out
+++ b/src/test/modules/test_ddl_deparse/expected/create_table.out
@@ -54,6 +54,8 @@ NOTICE:  DDL test: type simple, tag CREATE SEQUENCE
 NOTICE:  DDL test: type simple, tag CREATE SEQUENCE
 NOTICE:  DDL test: type simple, tag CREATE SEQUENCE
 NOTICE:  DDL test: type simple, tag CREATE TABLE
+NOTICE:  DDL test: type alter table, tag ALTER TABLE
+NOTICE:    subcommand: type SET ATTNOTNULL desc <NULL>
 NOTICE:  DDL test: type simple, tag CREATE INDEX
 NOTICE:  DDL test: type simple, tag CREATE INDEX
 NOTICE:  DDL test: type simple, tag ALTER SEQUENCE
@@ -74,6 +76,8 @@ CREATE TABLE IF NOT EXISTS fkey_table (
     EXCLUDE USING btree (check_col_2 WITH =)
 );
 NOTICE:  DDL test: type simple, tag CREATE TABLE
+NOTICE:  DDL test: type alter table, tag ALTER TABLE
+NOTICE:    subcommand: type SET ATTNOTNULL desc <NULL>
 NOTICE:  DDL test: type simple, tag CREATE INDEX
 NOTICE:  DDL test: type simple, tag CREATE INDEX
 NOTICE:  DDL test: type alter table, tag ALTER TABLE
@@ -86,7 +90,7 @@ CREATE TABLE employees OF employee_type (
 );
 NOTICE:  DDL test: type simple, tag CREATE TABLE
 NOTICE:  DDL test: type alter table, tag ALTER TABLE
-NOTICE:    subcommand: type SET NOT NULL desc column name of table employees
+NOTICE:    subcommand: type SET ATTNOTNULL desc <NULL>
 NOTICE:  DDL test: type simple, tag CREATE INDEX
 -- Inheritance
 CREATE TABLE person (
@@ -96,6 +100,8 @@ CREATE TABLE person (
 	location 	point
 );
 NOTICE:  DDL test: type simple, tag CREATE TABLE
+NOTICE:  DDL test: type alter table, tag ALTER TABLE
+NOTICE:    subcommand: type SET ATTNOTNULL desc <NULL>
 NOTICE:  DDL test: type simple, tag CREATE INDEX
 CREATE TABLE emp (
 	salary 		int4,
@@ -128,6 +134,10 @@ CREATE TABLE like_datatype_table (
   EXCLUDING ALL
 );
 NOTICE:  DDL test: type simple, tag CREATE TABLE
+NOTICE:  DDL test: type alter table, tag ALTER TABLE
+NOTICE:    subcommand: type ADD CONSTRAINT (and recurse) desc constraint datatype_table_id_big_not_null on table like_datatype_table
+NOTICE:    subcommand: type ADD CONSTRAINT (and recurse) desc constraint datatype_table_id_not_null on table like_datatype_table
+NOTICE:    subcommand: type ADD CONSTRAINT (and recurse) desc constraint datatype_table_is_small_not_null on table like_datatype_table
 CREATE TABLE like_fkey_table (
   LIKE fkey_table
   INCLUDING DEFAULTS
@@ -137,6 +147,11 @@ CREATE TABLE like_fkey_table (
 NOTICE:  DDL test: type simple, tag CREATE TABLE
 NOTICE:  DDL test: type alter table, tag ALTER TABLE
 NOTICE:    subcommand: type ALTER COLUMN SET DEFAULT (precooked) desc column id of table like_fkey_table
+NOTICE:    subcommand: type ADD CONSTRAINT (and recurse) desc constraint fkey_table_big_id_not_null on table like_fkey_table
+NOTICE:    subcommand: type ADD CONSTRAINT (and recurse) desc constraint fkey_table_check_col_1_not_null on table like_fkey_table
+NOTICE:    subcommand: type ADD CONSTRAINT (and recurse) desc constraint fkey_table_check_col_2_not_null on table like_fkey_table
+NOTICE:    subcommand: type ADD CONSTRAINT (and recurse) desc constraint fkey_table_datatype_id_not_null on table like_fkey_table
+NOTICE:    subcommand: type ADD CONSTRAINT (and recurse) desc constraint fkey_table_id_not_null on table like_fkey_table
 NOTICE:  DDL test: type simple, tag CREATE INDEX
 NOTICE:  DDL test: type simple, tag CREATE INDEX
 -- Volatile table types
@@ -144,21 +159,29 @@ CREATE UNLOGGED TABLE unlogged_table (
     id INT PRIMARY KEY
 );
 NOTICE:  DDL test: type simple, tag CREATE TABLE
+NOTICE:  DDL test: type alter table, tag ALTER TABLE
+NOTICE:    subcommand: type SET ATTNOTNULL desc <NULL>
 NOTICE:  DDL test: type simple, tag CREATE INDEX
 CREATE TEMP TABLE temp_table (
     id INT PRIMARY KEY
 );
 NOTICE:  DDL test: type simple, tag CREATE TABLE
+NOTICE:  DDL test: type alter table, tag ALTER TABLE
+NOTICE:    subcommand: type SET ATTNOTNULL desc <NULL>
 NOTICE:  DDL test: type simple, tag CREATE INDEX
 CREATE TEMP TABLE temp_table_commit_delete (
     id INT PRIMARY KEY
 )
 ON COMMIT DELETE ROWS;
 NOTICE:  DDL test: type simple, tag CREATE TABLE
+NOTICE:  DDL test: type alter table, tag ALTER TABLE
+NOTICE:    subcommand: type SET ATTNOTNULL desc <NULL>
 NOTICE:  DDL test: type simple, tag CREATE INDEX
 CREATE TEMP TABLE temp_table_commit_drop (
     id INT PRIMARY KEY
 )
 ON COMMIT DROP;
 NOTICE:  DDL test: type simple, tag CREATE TABLE
+NOTICE:  DDL test: type alter table, tag ALTER TABLE
+NOTICE:    subcommand: type SET ATTNOTNULL desc <NULL>
 NOTICE:  DDL test: type simple, tag CREATE INDEX
diff --git a/src/test/modules/test_ddl_deparse/test_ddl_deparse.c b/src/test/modules/test_ddl_deparse/test_ddl_deparse.c
index b7c6f98577c..88977bf2c74 100644
--- a/src/test/modules/test_ddl_deparse/test_ddl_deparse.c
+++ b/src/test/modules/test_ddl_deparse/test_ddl_deparse.c
@@ -129,6 +129,9 @@ get_altertable_subcmdinfo(PG_FUNCTION_ARGS)
 			case AT_SetNotNull:
 				strtype = "SET NOT NULL";
 				break;
+			case AT_SetAttNotNull:
+				strtype = "SET ATTNOTNULL";
+				break;
 			case AT_DropExpression:
 				strtype = "DROP EXPRESSION";
 				break;
@@ -318,6 +321,7 @@ get_altertable_subcmdinfo(PG_FUNCTION_ARGS)
 		if (OidIsValid(sub->address.objectId))
 		{
 			char	   *objdesc;
+
 			objdesc = getObjectDescription((const ObjectAddress *) &sub->address, false);
 			values[1] = CStringGetTextDatum(objdesc);
 		}