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

Commit8977952

Browse files
committed
Enable CHECK constraints to be declared NOT VALID
This means that they can initially be added to a large existing tablewithout checking its initial contents, but new tuples must comply tothem; a separate pass invoked by ALTER TABLE / VALIDATE can verifyexisting data and ensure it complies with the constraint, at which pointit is marked validated and becomes a normal part of the table ecosystem.An non-validated CHECK constraint is ignored in the planner forconstraint_exclusion purposes; when validated, cached plans arerecomputed so that partitioning starts working right away.This patch also enables domains to have unvalidated CHECK constraintsattached to them as well by way of ALTER DOMAIN / ADD CONSTRAINT / NOTVALID, which can later be validated with ALTER DOMAIN / VALIDATECONSTRAINT.Thanks to Thom Brown, Dean Rasheed and Jaime Casanova for the variousreviews, and Robert Hass for documentation wording improvementsuggestions.This patch was sponsored by Enova Financial.
1 parentb36927f commit8977952

File tree

19 files changed

+581
-70
lines changed

19 files changed

+581
-70
lines changed

‎doc/src/sgml/catalogs.sgml

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1899,7 +1899,7 @@
18991899
<entry><type>bool</type></entry>
19001900
<entry></entry>
19011901
<entry>Has the constraint been validated?
1902-
Currently, can only be false for foreign keys</entry>
1902+
Currently, can only be false for foreign keys and CHECK constraints</entry>
19031903
</row>
19041904

19051905
<row>

‎doc/src/sgml/ref/alter_domain.sgml

Lines changed: 38 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -28,9 +28,11 @@ ALTER DOMAIN <replaceable class="PARAMETER">name</replaceable>
2828
ALTER DOMAIN <replaceable class="PARAMETER">name</replaceable>
2929
{ SET | DROP } NOT NULL
3030
ALTER DOMAIN <replaceable class="PARAMETER">name</replaceable>
31-
ADD <replaceable class="PARAMETER">domain_constraint</replaceable>
31+
ADD <replaceable class="PARAMETER">domain_constraint</replaceable> [ NOT VALID ]
3232
ALTER DOMAIN <replaceable class="PARAMETER">name</replaceable>
3333
DROP CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable> [ RESTRICT | CASCADE ]
34+
ALTER DOMAIN <replaceable class="PARAMETER">name</replaceable>
35+
VALIDATE CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable>
3436
ALTER DOMAIN <replaceable class="PARAMETER">name</replaceable>
3537
OWNER TO <replaceable class="PARAMETER">new_owner</replaceable>
3638
ALTER DOMAIN <replaceable class="PARAMETER">name</replaceable>
@@ -70,13 +72,19 @@ ALTER DOMAIN <replaceable class="PARAMETER">name</replaceable>
7072
</varlistentry>
7173

7274
<varlistentry>
73-
<term>ADD <replaceable class="PARAMETER">domain_constraint</replaceable></term>
75+
<term>ADD <replaceable class="PARAMETER">domain_constraint</replaceable> [ NOT VALID ]</term>
7476
<listitem>
7577
<para>
7678
This form adds a new constraint to a domain using the same syntax as
7779
<xref linkend="SQL-CREATEDOMAIN">.
78-
This will only succeed if all columns using the domain satisfy the
79-
new constraint.
80+
When a new constraint is added to a domain, all columns using that
81+
domain will be checked against the newly added constraint. These
82+
checks can be suppressed by adding the new constraint using the
83+
<literal>NOT VALID</literal> option; the constraint can later be made
84+
valid using <command>ALTER DOMAIN ... VALIDATE CONSTRAINT</command>.
85+
Newly inserted or updated rows are always checked against all
86+
constraints, even those marked <literal>NOT VALID</literal>.
87+
<literal>NOT VALID</> is only accepted for <literal>CHECK</> constraints.
8088
</para>
8189
</listitem>
8290
</varlistentry>
@@ -90,6 +98,17 @@ ALTER DOMAIN <replaceable class="PARAMETER">name</replaceable>
9098
</listitem>
9199
</varlistentry>
92100

101+
<varlistentry>
102+
<term>VALIDATE CONSTRAINT</term>
103+
<listitem>
104+
<para>
105+
This form validates a constraint previously added as
106+
<literal>NOT VALID</>, that is, verify that all data in columns using the
107+
domain satisfy the specified constraint.
108+
</para>
109+
</listitem>
110+
</varlistentry>
111+
93112
<varlistentry>
94113
<term>OWNER</term>
95114
<listitem>
@@ -155,6 +174,16 @@ ALTER DOMAIN <replaceable class="PARAMETER">name</replaceable>
155174
</listitem>
156175
</varlistentry>
157176

177+
<varlistentry>
178+
<term><replaceable class="PARAMETER">NOT VALID</replaceable></term>
179+
<listitem>
180+
<para>
181+
Do not verify existing column data for constraint validity.
182+
</para>
183+
</listitem>
184+
</varlistentry>
185+
186+
158187
<varlistentry>
159188
<term><literal>CASCADE</literal></term>
160189
<listitem>
@@ -250,9 +279,11 @@ ALTER DOMAIN zipcode SET SCHEMA customers;
250279

251280
<para>
252281
<command>ALTER DOMAIN</command> conforms to the <acronym>SQL</acronym>
253-
standard,
254-
except for the <literal>OWNER</> and <literal>SET SCHEMA</> variants,
255-
which are <productname>PostgreSQL</productname> extensions.
282+
standard, except for the <literal>OWNER</>, <literal>SET SCHEMA</> and
283+
<literal>VALIDATE CONSTRAINT</> variants, which are
284+
<productname>PostgreSQL</productname> extensions. The <literal>NOT VALID</>
285+
clause of the <literal>ADD CONSTRAINT</> variant is also a
286+
<productname>PostgreSQL</productname> extension.
256287
</para>
257288
</refsect1>
258289

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

Lines changed: 7 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -240,12 +240,14 @@ ALTER TABLE <replaceable class="PARAMETER">name</replaceable>
240240
This form adds a new constraint to a table using the same syntax as
241241
<xref linkend="SQL-CREATETABLE">, plus the option <literal>NOT
242242
VALID</literal>, which is currently only allowed for foreign key
243-
constraints.
243+
and CHECKconstraints.
244244
If the constraint is marked <literal>NOT VALID</literal>, the
245245
potentially-lengthy initial check to verify that all rows in the table
246246
satisfy the constraint is skipped. The constraint will still be
247247
enforced against subsequent inserts or updates (that is, they'll fail
248-
unless there is a matching row in the referenced table). But the
248+
unless there is a matching row in the referenced table, in the case
249+
of foreign keys; and they'll fail unless the new row matches the
250+
specified check constraints). But the
249251
database will not assume that the constraint holds for all rows in
250252
the table, until it is validated by using the <literal>VALIDATE
251253
CONSTRAINT</literal> option.
@@ -308,10 +310,10 @@ ALTER TABLE <replaceable class="PARAMETER">name</replaceable>
308310
<term><literal>VALIDATE CONSTRAINT</literal></term>
309311
<listitem>
310312
<para>
311-
This form validates a foreign key constraint that was previously created
313+
This form validates a foreign keyor checkconstraint that was previously created
312314
as <literal>NOT VALID</literal>, by scanning the table to ensure there
313-
are nounmatchedrows. Nothing happens ifthe constraint is
314-
already marked valid.
315+
are no rows for whichthe constraint is not satisfied.
316+
Nothing happens if the constraint isalready marked valid.
315317
The value of separating validation from initial creation of the
316318
constraint is that validation requires a lesser lock on the table
317319
than constraint creation does.

‎src/backend/access/common/tupdesc.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -200,6 +200,7 @@ CreateTupleDescCopyConstr(TupleDesc tupdesc)
200200
cpy->check[i].ccname=pstrdup(constr->check[i].ccname);
201201
if (constr->check[i].ccbin)
202202
cpy->check[i].ccbin=pstrdup(constr->check[i].ccbin);
203+
cpy->check[i].ccvalid=constr->check[i].ccvalid;
203204
}
204205
}
205206

‎src/backend/catalog/heap.c

Lines changed: 8 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -98,7 +98,7 @@ static Oid AddNewRelationType(const char *typeName,
9898
Oidnew_array_type);
9999
staticvoidRelationRemoveInheritance(Oidrelid);
100100
staticvoidStoreRelCheck(Relationrel,char*ccname,Node*expr,
101-
boolis_local,intinhcount);
101+
boolis_validated,boolis_local,intinhcount);
102102
staticvoidStoreConstraints(Relationrel,List*cooked_constraints);
103103
staticboolMergeWithExistingConstraint(Relationrel,char*ccname,Node*expr,
104104
boolallow_merge,boolis_local);
@@ -1846,7 +1846,7 @@ StoreAttrDefault(Relation rel, AttrNumber attnum, Node *expr)
18461846
*/
18471847
staticvoid
18481848
StoreRelCheck(Relationrel,char*ccname,Node*expr,
1849-
boolis_local,intinhcount)
1849+
boolis_validated,boolis_local,intinhcount)
18501850
{
18511851
char*ccbin;
18521852
char*ccsrc;
@@ -1907,7 +1907,7 @@ StoreRelCheck(Relation rel, char *ccname, Node *expr,
19071907
CONSTRAINT_CHECK,/* Constraint Type */
19081908
false,/* Is Deferrable */
19091909
false,/* Is Deferred */
1910-
true,/* Is Validated */
1910+
is_validated,
19111911
RelationGetRelid(rel),/* relation */
19121912
attNos,/* attrs in the constraint */
19131913
keycount,/* # attrs in the constraint */
@@ -1967,7 +1967,7 @@ StoreConstraints(Relation rel, List *cooked_constraints)
19671967
StoreAttrDefault(rel,con->attnum,con->expr);
19681968
break;
19691969
caseCONSTR_CHECK:
1970-
StoreRelCheck(rel,con->name,con->expr,
1970+
StoreRelCheck(rel,con->name,con->expr, !con->skip_validation,
19711971
con->is_local,con->inhcount);
19721972
numchecks++;
19731973
break;
@@ -2081,6 +2081,7 @@ AddRelationNewConstraints(Relation rel,
20812081
cooked->name=NULL;
20822082
cooked->attnum=colDef->attnum;
20832083
cooked->expr=expr;
2084+
cooked->skip_validation= false;
20842085
cooked->is_local=is_local;
20852086
cooked->inhcount=is_local ?0 :1;
20862087
cookedConstraints=lappend(cookedConstraints,cooked);
@@ -2194,7 +2195,8 @@ AddRelationNewConstraints(Relation rel,
21942195
/*
21952196
* OK, store it.
21962197
*/
2197-
StoreRelCheck(rel,ccname,expr,is_local,is_local ?0 :1);
2198+
StoreRelCheck(rel,ccname,expr, !cdef->skip_validation,is_local,
2199+
is_local ?0 :1);
21982200

21992201
numchecks++;
22002202

@@ -2203,6 +2205,7 @@ AddRelationNewConstraints(Relation rel,
22032205
cooked->name=ccname;
22042206
cooked->attnum=0;
22052207
cooked->expr=expr;
2208+
cooked->skip_validation=cdef->skip_validation;
22062209
cooked->is_local=is_local;
22072210
cooked->inhcount=is_local ?0 :1;
22082211
cookedConstraints=lappend(cookedConstraints,cooked);

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp