Revert structural changes to not-null constraints

There are some problems with the new way to handle these constraints
that were detected at the last minute, and require fixes that appear too
invasive to be doing this late in the cycle.  Revert this (again) for
now, we'll try again with these problems fixed.

The following commits are reverted:

    b0e96f311985  Catalog not-null constraints
    9b581c534186  Disallow changing NO INHERIT status of a not-null constraint
    d0ec2ddbe088  Fix not-null constraint test
    ac22a9545ca9  Move privilege check to the right place
    b0f7dd915bca  Check stack depth in new recursive functions
    3af721794272  Update information_schema definition for not-null constraints
    c3709100be73  Fix propagating attnotnull in multiple inheritance
    d9f686a72ee9  Fix restore of not-null constraints with inheritance
    d72d32f52d26  Don't try to assign smart names to constraints
    0cd711271d42  Better handle indirect constraint drops
    13daa33fa5a6  Disallow NO INHERIT not-null constraints on partitioned tables
    d45597f72fe5  Disallow direct change of NO INHERIT of not-null constraints
    21ac38f498b3  Fix inconsistencies in error messages

Discussion: https://postgr.es/m/202405110940.joxlqcx4dogd@alvherre.pgsql

4 files changed, 29 insertions, 60 deletions

diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index b530c030f01..56deb4ac5fd 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -1270,8 +1270,7 @@
        <structfield>attnotnull</structfield> <type>bool</type>
       </para>
       <para>
-       This column is marked not-null, either by a not-null constraint
-       or a primary key.
+       This represents a not-null constraint.
       </para></entry>
      </row>
 
@@ -2502,10 +2501,14 @@ SCRAM-SHA-256$<replaceable>&lt;iteration count&gt;</replaceable>:<replaceable>&l
   </indexterm>
 
   <para>
-   The catalog <structname>pg_constraint</structname> stores check, not-null,
-   primary key, unique, foreign key, and exclusion constraints on tables.
+   The catalog <structname>pg_constraint</structname> stores check, primary
+   key, unique, foreign key, and exclusion constraints on tables, as well as
+   not-null constraints on domains.
    (Column constraints are not treated specially.  Every column constraint is
    equivalent to some table constraint.)
+   Not-null constraints on relations are represented in the
+   <link linkend="catalog-pg-attribute"><structname>pg_attribute</structname></link>
+   catalog, not here.
   </para>
 
   <para>
@@ -2567,7 +2570,7 @@ SCRAM-SHA-256$<replaceable>&lt;iteration count&gt;</replaceable>:<replaceable>&l
       <para>
        <literal>c</literal> = check constraint,
        <literal>f</literal> = foreign key constraint,
-       <literal>n</literal> = not-null constraint,
+       <literal>f</literal> = not-null constraint (domains only),
        <literal>p</literal> = primary key constraint,
        <literal>u</literal> = unique constraint,
        <literal>t</literal> = constraint trigger,
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 026bfff70f3..6aab79e901c 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -763,38 +763,17 @@ CREATE TABLE products (
     price numeric
 );
 </programlisting>
-    An explicit constraint name can also be specified, for example:
-<programlisting>
-CREATE TABLE products (
-    product_no integer NOT NULL,
-    name text <emphasis>CONSTRAINT products_name_not_null</emphasis> NOT NULL,
-    price numeric
-);
-</programlisting>
-   </para>
-
-   <para>
-    A not-null constraint is usually written as a column constraint.  The
-    syntax for writing it as a table constraint is
-<programlisting>
-CREATE TABLE products (
-    product_no integer,
-    name text,
-    price numeric,
-    <emphasis>NOT NULL product_no</emphasis>,
-    <emphasis>NOT NULL name</emphasis>
-);
-</programlisting>
-    But this syntax is not standard and mainly intended for use by
-    <application>pg_dump</application>.
    </para>
 
    <para>
-    A not-null constraint is functionally equivalent to creating a check
+    A not-null constraint is always written as a column constraint.  A
+    not-null constraint is functionally equivalent to creating a check
     constraint <literal>CHECK (<replaceable>column_name</replaceable>
     IS NOT NULL)</literal>, but in
     <productname>PostgreSQL</productname> creating an explicit
-    not-null constraint is more efficient.
+    not-null constraint is more efficient.  The drawback is that you
+    cannot give explicit names to not-null constraints created this
+    way.
    </para>
 
    <para>
@@ -812,10 +791,6 @@ CREATE TABLE products (
    </para>
 
    <para>
-    However, a column can have at most one explicit not-null constraint.
-   </para>
-
-   <para>
     The <literal>NOT NULL</literal> constraint has an inverse: the
     <literal>NULL</literal> constraint.  This does not mean that the
     column must be null, which would surely be useless.  Instead, this
@@ -1008,7 +983,7 @@ CREATE TABLE example (
 
    <para>
     A table can have at most one primary key.  (There can be any number
-    of unique constraints, which combined with not-null constraints are functionally almost the
+    of unique and not-null constraints, which are functionally almost the
     same thing, but only one can be identified as the primary key.)
     Relational database theory
     dictates that every table must have a primary key.  This rule is
@@ -1668,16 +1643,11 @@ ALTER TABLE products ADD CHECK (name &lt;&gt; '');
 ALTER TABLE products ADD CONSTRAINT some_name UNIQUE (product_no);
 ALTER TABLE products ADD FOREIGN KEY (product_group_id) REFERENCES product_groups;
 </programlisting>
-   </para>
-
-   <para>
-    To add a not-null constraint, which is normally not written as a table
-    constraint, this special syntax is available:
+    To add a not-null constraint, which cannot be written as a table
+    constraint, use this syntax:
 <programlisting>
 ALTER TABLE products ALTER COLUMN product_no SET NOT NULL;
 </programlisting>
-    This command silently does nothing if the column already has a
-    not-null constraint.
    </para>
 
    <para>
@@ -1718,15 +1688,12 @@ ALTER TABLE products DROP CONSTRAINT some_name;
    </para>
 
    <para>
-    Simplified syntax is available to drop a not-null constraint:
+    This works the same for all constraint types except not-null
+    constraints. To drop a not-null constraint use:
 <programlisting>
 ALTER TABLE products ALTER COLUMN product_no DROP NOT NULL;
 </programlisting>
-    This mirrors the <literal>SET NOT NULL</literal> syntax for adding a
-    not-null constraint.  This command will silently do nothing if the column
-    does not have a not-null constraint.  (Recall that a column can have at
-    most one not-null constraint, so it is never ambiguous which constraint
-    this command acts on.)
+    (Recall that not-null constraints do not have names.)
    </para>
   </sect2>
 
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 0bf11f6cb6d..5d352abf991 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -105,7 +105,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
 <phrase>and <replaceable class="parameter">column_constraint</replaceable> is:</phrase>
 
 [ CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> ]
-{ NOT NULL [ NO INHERIT ] |
+{ NOT NULL |
   NULL |
   CHECK ( <replaceable class="parameter">expression</replaceable> ) [ NO INHERIT ] |
   DEFAULT <replaceable>default_expr</replaceable> |
@@ -121,7 +121,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
 
 [ CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> ]
 { CHECK ( <replaceable class="parameter">expression</replaceable> ) [ NO INHERIT ] |
-  NOT NULL <replaceable class="parameter">column_name</replaceable> [ NO INHERIT ] |
   UNIQUE [ NULLS [ NOT ] DISTINCT ] ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> |
   PRIMARY KEY ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> |
   EXCLUDE [ USING <replaceable class="parameter">index_method</replaceable> ] ( <replaceable class="parameter">exclude_element</replaceable> WITH <replaceable class="parameter">operator</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> [ WHERE ( <replaceable class="parameter">predicate</replaceable> ) ] |
@@ -1942,17 +1941,11 @@ ALTER TABLE sales_list MERGE PARTITIONS (sales_west, sales_east, sales_central)
   <title>Compatibility</title>
 
   <para>
-   The forms <literal>ADD [COLUMN]</literal>,
+   The forms <literal>ADD</literal> (without <literal>USING INDEX</literal>),
    <literal>DROP [COLUMN]</literal>, <literal>DROP IDENTITY</literal>, <literal>RESTART</literal>,
    <literal>SET DEFAULT</literal>, <literal>SET DATA TYPE</literal> (without <literal>USING</literal>),
    <literal>SET GENERATED</literal>, and <literal>SET <replaceable>sequence_option</replaceable></literal>
-   conform with the SQL standard.
-   The form <literal>ADD <replaceable>table_constraint</replaceable></literal>
-   conforms with the SQL standard when the <literal>USING INDEX</literal> and
-   <literal>NOT VALID</literal> clauses are omitted and the constraint type is
-   one of <literal>CHECK</literal>, <literal>UNIQUE</literal>, <literal>PRIMARY KEY</literal>,
-   or <literal>REFERENCES</literal>.
-   The other forms are
+   conform with the SQL standard.  The other forms are
    <productname>PostgreSQL</productname> extensions of the SQL standard.
    Also, the ability to specify more than one manipulation in a single
    <command>ALTER TABLE</command> command is an extension.
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index 75f06bc49cc..a5bf80fb27e 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -77,7 +77,6 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
 
 [ CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> ]
 { CHECK ( <replaceable class="parameter">expression</replaceable> ) [ NO INHERIT ] |
-  NOT NULL <replaceable class="parameter">column_name</replaceable> [ NO INHERIT ] |
   UNIQUE [ NULLS [ NOT ] DISTINCT ] ( <replaceable class="parameter">column_name</replaceable> [, ... ] [, <replaceable class="parameter">column_name</replaceable> WITHOUT OVERLAPS ] ) <replaceable class="parameter">index_parameters</replaceable> |
   PRIMARY KEY ( <replaceable class="parameter">column_name</replaceable> [, ... ] [, <replaceable class="parameter">column_name</replaceable> WITHOUT OVERLAPS ] ) <replaceable class="parameter">index_parameters</replaceable> |
   EXCLUDE [ USING <replaceable class="parameter">index_method</replaceable> ] ( <replaceable class="parameter">exclude_element</replaceable> WITH <replaceable class="parameter">operator</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> [ WHERE ( <replaceable class="parameter">predicate</replaceable> ) ] |
@@ -2392,6 +2391,13 @@ CREATE TABLE cities_partdef
     constraint, and index names must be unique across all relations within
     the same schema.
    </para>
+
+   <para>
+    Currently, <productname>PostgreSQL</productname> does not record names
+    for not-null constraints at all, so they are not
+    subject to the uniqueness restriction.  This might change in a future
+    release.
+   </para>
   </refsect2>
 
   <refsect2>