Skip to content

Commit b0e96f3

Browse files
alvherrealvherre
committed
Catalog not-null constraints
We now create contype='n' pg_constraint rows for not-null constraints. We propagate these constraints to other tables during operations such as adding inheritance relationships, creating and attaching partitions and creating tables LIKE other tables. We also spawn not-null constraints for inheritance child tables when their parents have primary keys. These related constraints 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 matching by column name that they apply to. This means we don't require the constraint names to be identical across a hierarchy. For now, we omit them for 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.) psql shows these constraints in \d+. pg_dump requires some ad-hoc hacks, particularly when dumping a primary key. We now create one "throwaway" not-null constraint for each column in the PK together with the CREATE TABLE command, and once the PK is created, all those throwaway constraints are removed. This avoids having to check each tuple for nullness when the dump restores the primary key creation. pg_upgrading from an older release requires a somewhat brittle procedure to create a constraint state that matches what would be created if the database were being created fresh in Postgres 17. I have tested all the scenarios I could think of, and it works correctly as far as I can tell, but I could have neglected weird cases. This patch 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. During Postgres 16 this had already been introduced by commit e056c55, but there were some problems mainly with the pg_upgrade procedure that couldn't be fixed in reasonable time, so it was reverted. 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 an additional pg_attribute column 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> Reviewed-by: Dean Rasheed <dean.a.rasheed@gmail.com>
1 parent 9c13b68 commit b0e96f3

Some content is hidden

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

50 files changed

+3928
-764
lines changed

‎contrib/sepgsql/expected/alter.out

-3
Original file line numberDiff line numberDiff line change
@@ -164,7 +164,6 @@ LOG: SELinux: allowed { setattr } scontext=unconfined_u:unconfined_r:sepgsql_re
164164
LOG: SELinux: allowed { setattr } scontext=unconfined_u:unconfined_r:sepgsql_regtest_superuser_t:s0 tcontext=unconfined_u:object_r:sepgsql_table_t:s0 tclass=db_column name="regtest_schema.regtest_table_2.b" permissive=0
165165
ALTER TABLE regtest_table ALTER b DROP NOT NULL;
166166
LOG: SELinux: allowed { setattr } scontext=unconfined_u:unconfined_r:sepgsql_regtest_superuser_t:s0 tcontext=unconfined_u:object_r:sepgsql_table_t:s0 tclass=db_column name="regtest_schema_2.regtest_table.b" permissive=0
167-
LOG: SELinux: allowed { setattr } scontext=unconfined_u:unconfined_r:sepgsql_regtest_superuser_t:s0 tcontext=unconfined_u:object_r:sepgsql_table_t:s0 tclass=db_column name="regtest_schema.regtest_table_2.b" permissive=0
168167
ALTER TABLE regtest_table ALTER b SET STATISTICS -1;
169168
LOG: SELinux: allowed { setattr } scontext=unconfined_u:unconfined_r:sepgsql_regtest_superuser_t:s0 tcontext=unconfined_u:object_r:sepgsql_table_t:s0 tclass=db_column name="regtest_schema_2.regtest_table.b" permissive=0
170169
LOG: SELinux: allowed { setattr } scontext=unconfined_u:unconfined_r:sepgsql_regtest_superuser_t:s0 tcontext=unconfined_u:object_r:sepgsql_table_t:s0 tclass=db_column name="regtest_schema.regtest_table_2.b" permissive=0
@@ -249,8 +248,6 @@ LOG: SELinux: allowed { setattr } scontext=unconfined_u:unconfined_r:sepgsql_re
249248
LOG: SELinux: allowed { setattr } scontext=unconfined_u:unconfined_r:sepgsql_regtest_superuser_t:s0 tcontext=unconfined_u:object_r:sepgsql_table_t:s0 tclass=db_column name="regtest_schema.regtest_ptable_1_tens.p" permissive=0
250249
ALTER TABLE regtest_ptable ALTER p DROP NOT NULL;
251250
LOG: SELinux: allowed { setattr } scontext=unconfined_u:unconfined_r:sepgsql_regtest_superuser_t:s0 tcontext=unconfined_u:object_r:sepgsql_table_t:s0 tclass=db_column name="regtest_schema_2.regtest_ptable.p" permissive=0
252-
LOG: SELinux: allowed { setattr } scontext=unconfined_u:unconfined_r:sepgsql_regtest_superuser_t:s0 tcontext=unconfined_u:object_r:sepgsql_table_t:s0 tclass=db_column name="regtest_schema_2.regtest_table_part.p" permissive=0
253-
LOG: SELinux: allowed { setattr } scontext=unconfined_u:unconfined_r:sepgsql_regtest_superuser_t:s0 tcontext=unconfined_u:object_r:sepgsql_table_t:s0 tclass=db_column name="regtest_schema.regtest_ptable_1_tens.p" permissive=0
254251
ALTER TABLE regtest_ptable ALTER p SET STATISTICS -1;
255252
LOG: SELinux: allowed { setattr } scontext=unconfined_u:unconfined_r:sepgsql_regtest_superuser_t:s0 tcontext=unconfined_u:object_r:sepgsql_table_t:s0 tclass=db_column name="regtest_schema_2.regtest_ptable.p" permissive=0
256253
LOG: SELinux: allowed { setattr } scontext=unconfined_u:unconfined_r:sepgsql_regtest_superuser_t:s0 tcontext=unconfined_u:object_r:sepgsql_table_t:s0 tclass=db_column name="regtest_schema_2.regtest_table_part.p" permissive=0

‎contrib/sepgsql/expected/ddl.out

+2
Original file line numberDiff line numberDiff line change
@@ -49,6 +49,7 @@ LOG: SELinux: allowed { search } scontext=unconfined_u:unconfined_r:sepgsql_reg
4949
LOG: SELinux: allowed { search } scontext=unconfined_u:unconfined_r:sepgsql_regtest_superuser_t:s0 tcontext=unconfined_u:object_r:sepgsql_schema_t:s0 tclass=db_schema name="regtest_schema" permissive=0
5050
LOG: SELinux: allowed { search } scontext=unconfined_u:unconfined_r:sepgsql_regtest_superuser_t:s0 tcontext=system_u:object_r:sepgsql_schema_t:s0 tclass=db_schema name="pg_catalog" permissive=0
5151
LOG: SELinux: allowed { search } scontext=unconfined_u:unconfined_r:sepgsql_regtest_superuser_t:s0 tcontext=unconfined_u:object_r:sepgsql_schema_t:s0 tclass=db_schema name="regtest_schema" permissive=0
52+
LOG: SELinux: allowed { search } scontext=unconfined_u:unconfined_r:sepgsql_regtest_superuser_t:s0 tcontext=unconfined_u:object_r:sepgsql_schema_t:s0 tclass=db_schema name="regtest_schema" permissive=0
5253
LOG: SELinux: allowed { add_name } scontext=unconfined_u:unconfined_r:sepgsql_regtest_superuser_t:s0 tcontext=unconfined_u:object_r:sepgsql_schema_t:s0 tclass=db_schema name="regtest_schema" permissive=0
5354
LOG: SELinux: allowed { setattr } scontext=unconfined_u:unconfined_r:sepgsql_regtest_superuser_t:s0 tcontext=unconfined_u:object_r:sepgsql_table_t:s0 tclass=db_table name="regtest_schema.regtest_table" permissive=0
5455
LOG: SELinux: allowed { search } scontext=unconfined_u:unconfined_r:sepgsql_regtest_superuser_t:s0 tcontext=unconfined_u:object_r:sepgsql_schema_t:s0 tclass=db_schema name="regtest_schema" permissive=0
@@ -269,6 +270,7 @@ LOG: SELinux: allowed { create } scontext=unconfined_u:unconfined_r:sepgsql_reg
269270
LOG: SELinux: allowed { create } scontext=unconfined_u:unconfined_r:sepgsql_regtest_superuser_t:s0 tcontext=unconfined_u:object_r:sepgsql_table_t:s0 tclass=db_column name="regtest_schema.regtest_table_4.y" permissive=0
270271
LOG: SELinux: allowed { create } scontext=unconfined_u:unconfined_r:sepgsql_regtest_superuser_t:s0 tcontext=unconfined_u:object_r:sepgsql_table_t:s0 tclass=db_column name="regtest_schema.regtest_table_4.z" permissive=0
271272
LOG: SELinux: allowed { search } scontext=unconfined_u:unconfined_r:sepgsql_regtest_superuser_t:s0 tcontext=unconfined_u:object_r:sepgsql_schema_t:s0 tclass=db_schema name="regtest_schema" permissive=0
273+
LOG: SELinux: allowed { search } scontext=unconfined_u:unconfined_r:sepgsql_regtest_superuser_t:s0 tcontext=unconfined_u:object_r:sepgsql_schema_t:s0 tclass=db_schema name="regtest_schema" permissive=0
272274
LOG: SELinux: allowed { add_name } scontext=unconfined_u:unconfined_r:sepgsql_regtest_superuser_t:s0 tcontext=unconfined_u:object_r:sepgsql_schema_t:s0 tclass=db_schema name="regtest_schema" permissive=0
273275
LOG: SELinux: allowed { setattr } scontext=unconfined_u:unconfined_r:sepgsql_regtest_superuser_t:s0 tcontext=unconfined_u:object_r:sepgsql_table_t:s0 tclass=db_table name="regtest_schema.regtest_table_4" permissive=0
274276
CREATE INDEX regtest_index_tbl4_y ON regtest_table_4(y);

‎contrib/test_decoding/expected/ddl.out

+12
Original file line numberDiff line numberDiff line change
@@ -492,6 +492,9 @@ WITH (user_catalog_table = true)
492492
options | text[] | | | | extended | |
493493
Indexes:
494494
"replication_metadata_pkey" PRIMARY KEY, btree (id)
495+
Not-null constraints:
496+
"replication_metadata_id_not_null" NOT NULL "id"
497+
"replication_metadata_relation_not_null" NOT NULL "relation"
495498
Options: user_catalog_table=true
496499

497500
INSERT INTO replication_metadata(relation, options)
@@ -506,6 +509,9 @@ ALTER TABLE replication_metadata RESET (user_catalog_table);
506509
options | text[] | | | | extended | |
507510
Indexes:
508511
"replication_metadata_pkey" PRIMARY KEY, btree (id)
512+
Not-null constraints:
513+
"replication_metadata_id_not_null" NOT NULL "id"
514+
"replication_metadata_relation_not_null" NOT NULL "relation"
509515

510516
INSERT INTO replication_metadata(relation, options)
511517
VALUES ('bar', ARRAY['a', 'b']);
@@ -519,6 +525,9 @@ ALTER TABLE replication_metadata SET (user_catalog_table = true);
519525
options | text[] | | | | extended | |
520526
Indexes:
521527
"replication_metadata_pkey" PRIMARY KEY, btree (id)
528+
Not-null constraints:
529+
"replication_metadata_id_not_null" NOT NULL "id"
530+
"replication_metadata_relation_not_null" NOT NULL "relation"
522531
Options: user_catalog_table=true
523532

524533
INSERT INTO replication_metadata(relation, options)
@@ -538,6 +547,9 @@ ALTER TABLE replication_metadata SET (user_catalog_table = false);
538547
rewritemeornot | integer | | | | plain | |
539548
Indexes:
540549
"replication_metadata_pkey" PRIMARY KEY, btree (id)
550+
Not-null constraints:
551+
"replication_metadata_id_not_null" NOT NULL "id"
552+
"replication_metadata_relation_not_null" NOT NULL "relation"
541553
Options: user_catalog_table=false
542554

543555
INSERT INTO replication_metadata(relation, options)

‎doc/src/sgml/catalogs.sgml

+5-6
Original file line numberDiff line numberDiff line change
@@ -1270,7 +1270,8 @@
12701270
<structfield>attnotnull</structfield> <type>bool</type>
12711271
</para>
12721272
<para>
1273-
This represents a not-null constraint.
1273+
This column is marked not-null, either by a not-null constraint
1274+
or a primary key.
12741275
</para></entry>
12751276
</row>
12761277

@@ -2484,13 +2485,10 @@ SCRAM-SHA-256$<replaceable>&lt;iteration count&gt;</replaceable>:<replaceable>&l
24842485
</indexterm>
24852486

24862487
<para>
2487-
The catalog <structname>pg_constraint</structname> stores check, primary
2488-
key, unique, foreign key, and exclusion constraints on tables.
2488+
The catalog <structname>pg_constraint</structname> stores check, not-null,
2489+
primary key, unique, foreign key, and exclusion constraints on tables.
24892490
(Column constraints are not treated specially. Every column constraint is
24902491
equivalent to some table constraint.)
2491-
Not-null constraints are represented in the
2492-
<link linkend="catalog-pg-attribute"><structname>pg_attribute</structname></link>
2493-
catalog, not here.
24942492
</para>
24952493

24962494
<para>
@@ -2552,6 +2550,7 @@ SCRAM-SHA-256$<replaceable>&lt;iteration count&gt;</replaceable>:<replaceable>&l
25522550
<para>
25532551
<literal>c</literal> = check constraint,
25542552
<literal>f</literal> = foreign key constraint,
2553+
<literal>n</literal> = not-null constraint,
25552554
<literal>p</literal> = primary key constraint,
25562555
<literal>u</literal> = unique constraint,
25572556
<literal>t</literal> = constraint trigger,

‎doc/src/sgml/ddl.sgml

+44-11
Original file line numberDiff line numberDiff line change
@@ -651,17 +651,38 @@ CREATE TABLE products (
651651
price numeric
652652
);
653653
</programlisting>
654+
An explicit constraint name can also be specified, for example:
655+
<programlisting>
656+
CREATE TABLE products (
657+
product_no integer NOT NULL,
658+
name text <emphasis>CONSTRAINT products_name_not_null</emphasis> NOT NULL,
659+
price numeric
660+
);
661+
</programlisting>
662+
</para>
663+
664+
<para>
665+
A not-null constraint is usually written as a column constraint. The
666+
syntax for writing it as a table constraint is
667+
<programlisting>
668+
CREATE TABLE products (
669+
product_no integer,
670+
name text,
671+
price numeric,
672+
<emphasis>NOT NULL product_no</emphasis>,
673+
<emphasis>NOT NULL name</emphasis>
674+
);
675+
</programlisting>
676+
But this syntax is not standard and mainly intended for use by
677+
<application>pg_dump</application>.
654678
</para>
655679

656680
<para>
657-
A not-null constraint is always written as a column constraint. A
658-
not-null constraint is functionally equivalent to creating a check
681+
A not-null constraint is functionally equivalent to creating a check
659682
constraint <literal>CHECK (<replaceable>column_name</replaceable>
660683
IS NOT NULL)</literal>, but in
661684
<productname>PostgreSQL</productname> creating an explicit
662-
not-null constraint is more efficient. The drawback is that you
663-
cannot give explicit names to not-null constraints created this
664-
way.
685+
not-null constraint is more efficient.
665686
</para>
666687

667688
<para>
@@ -678,6 +699,10 @@ CREATE TABLE products (
678699
order the constraints are checked.
679700
</para>
680701

702+
<para>
703+
However, a column can have at most one explicit not-null constraint.
704+
</para>
705+
681706
<para>
682707
The <literal>NOT NULL</literal> constraint has an inverse: the
683708
<literal>NULL</literal> constraint. This does not mean that the
@@ -871,7 +896,7 @@ CREATE TABLE example (
871896

872897
<para>
873898
A table can have at most one primary key. (There can be any number
874-
of unique and not-null constraints, which are functionally almost the
899+
of unique constraints, which combined with not-null constraints are functionally almost the
875900
same thing, but only one can be identified as the primary key.)
876901
Relational database theory
877902
dictates that every table must have a primary key. This rule is
@@ -1531,11 +1556,16 @@ ALTER TABLE products ADD CHECK (name &lt;&gt; '');
15311556
ALTER TABLE products ADD CONSTRAINT some_name UNIQUE (product_no);
15321557
ALTER TABLE products ADD FOREIGN KEY (product_group_id) REFERENCES product_groups;
15331558
</programlisting>
1534-
To add a not-null constraint, which cannot be written as a table
1535-
constraint, use this syntax:
1559+
</para>
1560+
1561+
<para>
1562+
To add a not-null constraint, which is normally not written as a table
1563+
constraint, this special syntax is available:
15361564
<programlisting>
15371565
ALTER TABLE products ALTER COLUMN product_no SET NOT NULL;
15381566
</programlisting>
1567+
This command silently does nothing if the column already has a
1568+
not-null constraint.
15391569
</para>
15401570

15411571
<para>
@@ -1576,12 +1606,15 @@ ALTER TABLE products DROP CONSTRAINT some_name;
15761606
</para>
15771607

15781608
<para>
1579-
This works the same for all constraint types except not-null
1580-
constraints. To drop a not null constraint use:
1609+
Simplified syntax is available to drop a not-null constraint:
15811610
<programlisting>
15821611
ALTER TABLE products ALTER COLUMN product_no DROP NOT NULL;
15831612
</programlisting>
1584-
(Recall that not-null constraints do not have names.)
1613+
This mirrors the <literal>SET NOT NULL</literal> syntax for adding a
1614+
not-null constraint. This command will silently do nothing if the column
1615+
does not have a not-null constraint. (Recall that a column can have at
1616+
most one not-null constraint, so it is never ambiguous which constraint
1617+
this command acts on.)
15851618
</para>
15861619
</sect2>
15871620

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

+9-2
Original file line numberDiff line numberDiff line change
@@ -113,6 +113,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
113113

114114
[ CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> ]
115115
{ CHECK ( <replaceable class="parameter">expression</replaceable> ) [ NO INHERIT ] |
116+
NOT NULL <replaceable class="parameter">column_name</replaceable> [ NO INHERIT ] |
116117
UNIQUE [ NULLS [ NOT ] DISTINCT ] ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> |
117118
PRIMARY KEY ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> |
118119
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> ) ] |
@@ -1763,11 +1764,17 @@ ALTER TABLE measurement
17631764
<title>Compatibility</title>
17641765

17651766
<para>
1766-
The forms <literal>ADD</literal> (without <literal>USING INDEX</literal>),
1767+
The forms <literal>ADD [COLUMN]</literal>,
17671768
<literal>DROP [COLUMN]</literal>, <literal>DROP IDENTITY</literal>, <literal>RESTART</literal>,
17681769
<literal>SET DEFAULT</literal>, <literal>SET DATA TYPE</literal> (without <literal>USING</literal>),
17691770
<literal>SET GENERATED</literal>, and <literal>SET <replaceable>sequence_option</replaceable></literal>
1770-
conform with the SQL standard. The other forms are
1771+
conform with the SQL standard.
1772+
The form <literal>ADD <replaceable>table_constraint</replaceable></literal>
1773+
conforms with the SQL standard when the <literal>USING INDEX</literal> and
1774+
<literal>NOT VALID</literal> clauses are omitted and the constraint type is
1775+
one of <literal>CHECK</literal>, <literal>UNIQUE</literal>, <literal>PRIMARY KEY</literal>,
1776+
or <literal>REFERENCES</literal>.
1777+
The other forms are
17711778
<productname>PostgreSQL</productname> extensions of the SQL standard.
17721779
Also, the ability to specify more than one manipulation in a single
17731780
<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> [ NO INHERIT ] |
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)