Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitb0e96f3

Browse files
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 asadding inheritance relationships, creating and attaching partitions andcreating tables LIKE other tables. We also spawn not-null constraintsfor inheritance child tables when their parents have primary keys.These related constraints mostly follow the well-known rules ofconislocal and coninhcount that we have for CHECK constraints, with someadaptations: for example, as opposed to CHECK constraints, we don'tmatch 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'trequire the constraint names to be identical across a hierarchy.For now, we omit them for system catalogs. Maybe this is worthreconsidering. We don't support NOT VALID nor DEFERRABLE clauseseither; these can be added as separate features later (this patch isalready large and complicated enough.)psql shows these constraints in \d+.pg_dump requires some ad-hoc hacks, particularly when dumping a primarykey. We now create one "throwaway" not-null constraint for each columnin the PK together with the CREATE TABLE command, and once the PK iscreated, all those throwaway constraints are removed. This avoidshaving to check each tuple for nullness when the dump restores theprimary key creation.pg_upgrading from an older release requires a somewhat brittle procedureto create a constraint state that matches what would be created if thedatabase were being created fresh in Postgres 17. I have tested all thescenarios 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 waswritten 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 onewas killed by the realization that we ought to use contype='c' instead:manufactured CHECK constraints. However, later SQL standarddevelopment, as well as nonobvious emergent properties of that design(mostly, failure to distinguish them from "normal" CHECK constraints aswell as the performance implication of having to test the CHECKexpression) led us to reconsider this choice, so now the currentimplementation uses contype='n' again. During Postgres 16 this hadalready been introduced by commite056c55, but there were someproblems mainly with the pg_upgrade procedure that couldn't be fixed inreasonable time, so it was reverted.In 2016 Vitaly Burovoy also worked on this feature[1] but found noconsensus for his proposed approach, which was claimed to be closer tothe letter of the standard, requiring an additional pg_attribute columnto track the OID of the not-null constraint for that column.[1]https://postgr.es/m/CAKOSWNkN6HSyatuys8xZxzRCR-KL1OkHS5-b9qd9bf1Rad3PLA@mail.gmail.comAuthor: Á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 parent9c13b68 commitb0e96f3

File tree

50 files changed

+3928
-764
lines changed

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

Lines changed: 0 additions & 3 deletions
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

Lines changed: 2 additions & 0 deletions
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

Lines changed: 12 additions & 0 deletions
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

Lines changed: 5 additions & 6 deletions
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+
primarykey, 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

Lines changed: 44 additions & 11 deletions
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 uniqueandnot-null constraints, which are functionally almost the
899+
of uniqueconstraints, which combined withnot-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

Lines changed: 9 additions & 2 deletions
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

Lines changed: 1 addition & 7 deletions
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)
[8]ページ先頭
©2009-2025 Movatter.jp