Skip to content

Commit e056c55

Browse files
alvherrealvherre
committed
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
1 parent ff245a3 commit e056c55

Some content is hidden

Large Commits have some content hidden by default. Use the searchbox below for content that may be hidden.

42 files changed

+2868
-625
lines changed

‎doc/src/sgml/catalogs.sgml

+1
Original file line numberDiff line numberDiff line change
@@ -2552,6 +2552,7 @@ SCRAM-SHA-256$<replaceable>&lt;iteration count&gt;</replaceable>:<replaceable>&l
25522552
<para>
25532553
<literal>c</literal> = check constraint,
25542554
<literal>f</literal> = foreign key constraint,
2555+
<literal>n</literal> = not null constraint,
25552556
<literal>p</literal> = primary key constraint,
25562557
<literal>u</literal> = unique constraint,
25572558
<literal>t</literal> = constraint trigger,

‎doc/src/sgml/ref/alter_table.sgml

+11-3
Original file line numberDiff line numberDiff line change
@@ -117,7 +117,9 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
117117
PRIMARY KEY ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> |
118118
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> ) ] |
119119
FOREIGN KEY ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) REFERENCES <replaceable class="parameter">reftable</replaceable> [ ( <replaceable class="parameter">refcolumn</replaceable> [, ... ] ) ]
120-
[ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE <replaceable class="parameter">referential_action</replaceable> ] [ ON UPDATE <replaceable class="parameter">referential_action</replaceable> ] }
120+
[ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE <replaceable class="parameter">referential_action</replaceable> ] [ ON UPDATE <replaceable class="parameter">referential_action</replaceable> ] |
121+
NOT NULL <replaceable class="parameter">column_name</replaceable> [ NO INHERIT ]
122+
}
121123
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
122124

123125
<phrase>and <replaceable class="parameter">table_constraint_using_index</replaceable> is:</phrase>
@@ -1763,11 +1765,17 @@ ALTER TABLE measurement
17631765
<title>Compatibility</title>
17641766

17651767
<para>
1766-
The forms <literal>ADD</literal> (without <literal>USING INDEX</literal>),
1768+
The forms <literal>ADD COLUMN</literal>
17671769
<literal>DROP [COLUMN]</literal>, <literal>DROP IDENTITY</literal>, <literal>RESTART</literal>,
17681770
<literal>SET DEFAULT</literal>, <literal>SET DATA TYPE</literal> (without <literal>USING</literal>),
17691771
<literal>SET GENERATED</literal>, and <literal>SET <replaceable>sequence_option</replaceable></literal>
1770-
conform with the SQL standard. The other forms are
1772+
conform with the SQL standard.
1773+
The form <literal>ADD <replaceable>table_constraint</replaceable></literal>
1774+
conforms with the SQL standard when the <literal>USING INDEX</literal> and
1775+
<literal>NOT VALID</literal> clauses are omitted and the constraint type is
1776+
one of <literal>UNIQUE</literal>, <literal>PRIMARY KEY</literal>
1777+
or <literal>REFERENCES</literal>.
1778+
The other forms are
17711779
<productname>PostgreSQL</productname> extensions of the SQL standard.
17721780
Also, the ability to specify more than one manipulation in a single
17731781
<command>ALTER TABLE</command> command is an extension.

‎doc/src/sgml/ref/create_table.sgml

+1-7
Original file line numberDiff line numberDiff line change
@@ -77,6 +77,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
7777

7878
[ CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> ]
7979
{ CHECK ( <replaceable class="parameter">expression</replaceable> ) [ NO INHERIT ] |
80+
NOT NULL <replaceable class="parameter">column_name</replaceable> |
8081
UNIQUE [ NULLS [ NOT ] DISTINCT ] ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> |
8182
PRIMARY KEY ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> |
8283
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> ) ] |
@@ -2314,13 +2315,6 @@ CREATE TABLE cities_partdef
23142315
constraint, and index names must be unique across all relations within
23152316
the same schema.
23162317
</para>
2317-
2318-
<para>
2319-
Currently, <productname>PostgreSQL</productname> does not record names
2320-
for <literal>NOT NULL</literal> constraints at all, so they are not
2321-
subject to the uniqueness restriction. This might change in a future
2322-
release.
2323-
</para>
23242318
</refsect2>
23252319

23262320
<refsect2>

0 commit comments

Comments
 (0)